Write SQL command for following
i)Create user
ii) Grant privileges to user.
Iii) Remove privileges from user.
1 Answer
Of course. Here are the SQL commands for creating a user, granting privileges, and removing privileges, along with explanations and examples.
These commands fall under DCL (Data Control Language) and are typically executed by a Database Administrator (DBA) or a user with sufficient administrative permissions.
i) Create User
The CREATE USER command is used to create a new user account in the database. A newly created user typically has no privileges, not even the ability to log in, until they are granted.
Syntax
`sql
CREATE USER username IDENTIFIED BY password;
`
username: The name you want to give the new user.
password: The password the user will use to log in.
Example
Let's create a new user named new_app_user.
`sql
CREATE USER new_app_user IDENTIFIED BY 'a_very_secure_password';
`
Explanation: This command creates a new user account named new_app_user with the password a_very_secure_password. At this point, new_app_user exists but cannot connect to the database.
ii) Grant Privileges to User
The GRANT command is used to give specific permissions (privileges) to a user or a role. This is how you control what a user is allowed to do.
There are two main types of privileges:
System Privileges: Permissions to perform database-wide actions (e.g., connect to the database, create tables).
Object Privileges: Permissions to perform actions on specific database objects (e.g., select from a table, execute a procedure).
Syntax
For System Privileges:
`sql
GRANT privilege_name [, ...] TO username;
`
* privilege_name: The system privilege, such as CONNECT or CREATE TABLE.
For Object Privileges:
`sql
GRANT privilege_name [, ...] ON object_name TO username;
`
privilege_name: The object privilege, such as SELECT, INSERT, UPDATE, DELETE.
object_name: The name of the table, view, or other object.
Example
Let's grant our new_app_user the ability to log in and then allow them to read and add data to a specific table called products.
1. Granting Login Permission (System Privilege):
The most basic privilege a user needs is CONNECT.
`sql
GRANT CONNECT TO new_app_user;
`
Explanation: This command gives new_app_user the ability to connect to the database.
2. Granting Table Permissions (Object Privileges):
Now, let's allow the user to work with the products table.
`sql
GRANT SELECT, INSERT ON products TO new_app_user;
`
Explanation: This command gives new_app_user two specific permissions on the products table:
SELECT: The ability to read data from the table.
INSERT: The ability to add new rows to the table.
The user still cannot UPDATE or DELETE data from this table.
iii) Remove Privileges from User
The REVOKE command is the opposite of GRANT. It is used to take away privileges from a user or a role.
Syntax
For System Privileges:
`sql
REVOKE privilege_name [, ...] FROM username;
`
For Object Privileges:
`sql
REVOKE privilege_name [, ...] ON object_name FROM username;
`
Example
Let's say the role of new_app_user has changed, and they should no longer be able to add new data to the products table. We need to remove their INSERT privilege.
`sql
REVOKE INSERT ON products FROM new_app_user;
`
Explanation: This command removes the INSERT permission on the products table from new_app_user. After this, the user can still SELECT data from the table (because we didn't revoke that privilege), but any attempt to INSERT a new row will result in a "permission denied" error.