Buzz
With SQL GRANT and REVOKE commands, only the Database Admin or the owner of the database object can grant/revoke privileges/permissions on a database object.
SQL GRANT and REVOKE Commands
SQL GRANT Command
The SQL GRANT command is used to grant access permissions or privileges on a database object to users.
Syntax of SQL GRANT Command
The syntax of the SQL GRANT command is as follows:
Grant privilege
On object
To {user |PUBLIC |role}
[WITH GRANT OPTION]
In which:
- privilege_name represents the access right or privilege granted to the user. Some access rights include ALL, EXECUTE, and SELECT.
- object_name refers to the name of the database object such as TABLE, VIEW, STORED PROC, and SEQUENCE.
- user_name is the name of the user to whom access is granted.
- PUBLIC is used to grant access to all users.
- ROLES are a collection of privileges grouped together.
- WITH GRANT OPTION allows the user to grant access to other users.
Example of GRANT command in SQL
Command: GRANT SELECT ON employee TO user1.
The above GRANT command grants SELECT privilege to user1 on the employee table. You should use the WITH GRANT option carefully, assuming that if you use GRANT SELECT to grant privilege in the employee table to user1 using the WITH GRANT option, then user1 can grant GRANT SELECT privilege to other users in the employee table, such as user2, ... .
Even if SELECT privilege is revoked from user1, user2 still retains SELECT permission on the employee table.
REVOKE Command in SQL
The REVOKE command in SQL is used to revoke user access or privileges on database objects.
Syntax of REVOKE Command in SQL
Syntax of the REVOKE command in SQL is as follows:
REVOKE privilege_name
ON object_name
FROM {user_name |PUBLIC |role_name}
Example of REVOKE command in SQL
Command: REVOKE SELECT ON employee FROM user1.
The above REVOKE command will revoke the SELECT privilege from user1 on the employee table.
When revoking SELECT privilege from a user on a table, that user cannot SELECT data from that table anymore. However, if the user receives SELECT privileges on the table from multiple other users, they can SELECT from the table until all other users revoke their privileges. You cannot revoke privileges if you did not grant those privileges initially.
Privileges and Roles in SQL
Privileges in SQL
Privileges in SQL define the access rights granted to users on a database object. There are 2 types of privileges in SQL:
- System privileges: These privileges allow users to create database objects such as CREATE, ALTER, or DROP.
- Object privileges: These privileges enable users to manipulate data EXECUTE, SELECT, INSERT, UPDATE, or DELETE from database objects to which the privileges apply.
Below is a table listing some system privileges CREATE:
The same rules apply for system privileges ALTER and DROP.
Below is a table listing object privileges:
Role in SQL
A Role is a collection of privileges or access rights. When dealing with multiple users in a database, granting or revoking permissions to individual users can become cumbersome.
Therefore, by defining roles, you can grant or revoke permissions to users to automatically grant or revoke privileges. You can create custom roles or use pre-defined system roles provided by Oracle.
Some privileges granted to system roles include:
Create Roles in SQL
Syntax for creating Role in SQL:
CREATE ROLE role_name
[IDENTIFIED BY password];
Example Role in SQL
Example 1
To create a role named 'developer' with the password 'pwd', the syntax is as follows:
CREATE ROLE testing
[IDENTIFIED BY pwd];
To grant or revoke privileges to users through roles rather than assigning a privilege directly to each user. If a role is identified by a password, when granting or revoking privileges of the role, you will have to identify the role by the password.
You can grant or revoke privileges of a role as shown below.
For example: To grant CREATE TABLE privilege to a user by creating a testing role:
Example 2
To grant the CREATE TABLE privilege to a user by creating a testing role.
The first step is to create the Role testing:
CREATE ROLE testing
The next step is to grant the CREATE TABLE privilege to the role testing. You can also add other privileges to the ROLE:
GRANT CREATE TABLE TO testing;
Next, assign the role to the user:
GRANT testing TO user1;
To revoke the CREATE TABLE privilege from the role testing, you can write:
REVOKE CREATE TABLE FROM testing;
Syntax to remove a role from the database is:
DROP ROLE role_name;
Example 3
To drop the role named developer, you can write:
Executing the command DROP ROLE testing;.
In the recent Mytour article, we introduced you to SQL commands such as GRANT, REVOKE, and various privileges and roles in SQL. Additionally, readers can explore other Mytour articles to delve deeper into commands like CREATE, DELETE, and WHERE clauses in SQL, as well as concepts like ORDER BY. Dive into the world of SQL with Mytour!
