GRANT, REVOKE in SQL

With the GRANT and REVOKE commands in SQL, only the database administrator or the database object owner can grant/remove privileges/permissions on a database object.

The GRANT and REVOKE commands in SQL

The GRAT command in SQL

The GRANT statement in SQL is used to grant users access or privileges to database objects.

The GRANT command syntax in SQL

The syntax for the GRANT command in SQL is as follows:

GRANT parent_name

ON object_name

TO {user_name |PUBLIC |role_name}

[WITH GRANT OPTION];

In there:

- `privilege_name` is the access or privilege granted to a user. Some access rights include `ALL`, `EXECUTE`, and `SELECT`.
- `object_name` is the name of a database object such as `TABLE`, `VIEW`, `STORED PROC`, and `SEQUENCE`.
- `user_name` is the name of the user granted access.
- `PUBLIC` is used to grant access to all users.
- `ROLES` is a group of privileges grouped together.
- `WITH GRANT` OPTION allows a user to grant access to other users.
For example, the `GRANT` command in SQL.

Command: GRANT SELECT ON employee TO user1 .

The GRANT statement above grants SELECT privileges to user1 in the employee list table. You should use the WITH GRANT option carefully, because if you use GRANT SELECT to grant privileges in the employee table so that user1 can use the WITH GRANT option, then user1 could grant GRANT SELECT privileges to other users in the employee table, such as user2, . .

If user1 removes the SELECT permission, user2 will still have the SELECT permission on the employee list.

The REVOKE command in SQL

The REVOKE statement in SQL is used to revoke a user's access or privileges to database objects.

The REVOKE command syntax in SQL

The syntax for 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 REVOKE command above will revoke user1's SELECT privileges in the employee table.

When you revoke SELECT privileges from a user in a table, that user can no longer select data from that table. However, if a user receives SELECT privileges on a table from multiple other users, they can select from that table until all other users revoke their privileges. You cannot revoke privileges if you did not grant them in the first place.

Privileges and Roles in SQL

Privileges in SQL

Privileges in SQL define the access rights granted to a user within a database object. There are two types of privileges in SQL:

- System privileges: These privileges allow users to create, alter, or drop database objects.
- Object privileges: These privileges allow users to execute, select, insert, update, or delete data from database objects to which the privileges apply.

Below is a list of some system privileges (CREATE):

These rules also apply to the ALTER and DROP system privileges.

Below is a list of object privileges:

Roles in SQL

Roles are a set of privileges or access rights. When there are many users in a database, granting or revoking user privileges can become difficult.

Therefore, by defining roles, you can grant or revoke privileges to users, automatically granting or revoking privileges. You can create roles or use system roles predefined by Oracle.

Several privileges are granted to system roles, including:

Creating Roles in SQL

Syntax for creating Roles in SQL:

CREATE ROLE role_name
[IDENTIFIED BY password];
Example of Roles 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 user privileges, do so through roles, rather than assigning a privilege directly to each user. If a role is identified by a password, when granting or revoking that role's privileges, you will need to identify the role using the password.

You can grant or revoke role privileges as follows.

For example: To grant CREATE TABLE privilege to a user by creating a testing role:
Example 2

To grant the CREATE TABLE privilege to users by creating a testing role.

The first step is to create role testing:

Create Role Testing

The next step is to grant the CREATE TABLE privilege to the testing role. You can also add other privileges to the ROLE:

GRANT CREATE TABLE TO testing;

Next, assign roles to the users:

GRANT testing TO user1;

To revoke the CREATE TABLE privilege from role testing, you can write:

REVOKE CREATE TABLE FROM testing;

The syntax for removing a role from the database is:

DROP ROLE role_name;
Example 3

To remove the role named "developer," you can write:

Drop role testing;

In the article above, TipsMake introduced you to the GRANT and REVOKE commands in SQL, as well as privileges and roles in SQL. Additionally, readers can find and refer to other articles by TipsMake to learn more about the CREATE, DELETE commands, WHERE clauses in SQL , ORDER BY, and other articles guiding you on SQL.