GRANT, REVOKE in SQL


The DCL commands in SQL are used to enforce database security in the user database environment. The GRANT, REVOKE statements in SQL are two types of DCL instructions. To learn more about the DCL command as well as the GRANT and REVOKE statements in SQL, read along with the following article of Taimienphi.vn.


With the GRANT, REVOKE commands in SQL, only database administrators or database object owners can grant / delete privileges / privileges on a database object.

GRANT, REVOKE statements in SQL

GRANT statement in SQL

The GRANT command in SQL is used to grant user access or privileges to a database object.

GRANT command syntax in SQL

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

GRANT privilege_name

ON object_name

TO {user_name | PUBLIC | role_name}

[WITH GRANT OPTION];

Inside:

– privilege_name is the access or privilege granted to the user. Some access rights include ALL, EXECUTE, and SELECT.
– object_name is the name of database objects 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 is a set of privileges grouped together.
– WITH GRANT OPTION Allows users to grant access to other users.
Example GRANT command in SQL

Comeinand: GRANT SELECT ON employee TO user1.

The GRANT command above grants SELECT permission for user1 in the employee list. You should use the WITH GRANT option with care, assuming in case if using GRANT SELECT grants privileges in the employee table so that user1 uses the WITH GRANT option, user1 can grant GRANT SELECT permissions to other users in the table. employees, such as user2, ….

If REVOKE has SELECT right from user1, user2 still has SELECT right on the employee list.

REVOKE statement in SQL

The REVOKE command in SQL is used to revoke user access or privileges with database objects.

Syntax of REVOKE command in SQL

The syntax of the REVOKE command in SQL looks like this:

REVOKE privilege_name
ON object_name
FROM {user_name | PUBLIC | role_name}
Example REVOKE command in SQL

Comeinand: REVOKE SELECT ON employee FROM user1.

The above REVOKE command will revoke the SELECT privileges of user1 in the employee table.

When revoking SELECT rights from a user in a table, that user cannot SELECT (select) data from that table anymore. However, if a user receives SELECT rights on the table from many other users, they can SELECT from that table until all other users revoke permissions. You cannot revoke privileges if you have not originally granted them.

Privileges and Roles in SQL

Privileges in SQL

Privileges in SQL determine access rights granted to users in a database object. There are 2 types of privileges in SQL:

System privileges (system privilege): This privilege allows users to create database objects CREATE, ALTER, or DROP.
Object privileges (object privileges): This privilege allows users to execute EXECUTE, SELECT, INSERT, UPDATE, or DELETE data from database objects to which privileges apply.

The following table lists some of the CREATE System privileges:

grant revoke in sql 2

The above rules also apply to ALTER and DROP system privileges.

The following is a list of object privileges:

grant revoke in sql 3

Role in SQL

A role is a set of privileges or permissions. When there are many users in the database, it is difficult to grant or revoke user rights.

So if you define a role, you can grant or revoke privileges to users to automatically grant or revoke privileges. You can create Roles or use system roles predefined by oracle.

Several privileges are granted for system roles, including:

grant revoke in sql 4

Create Roles in SQL

Syntax for creating a Role in SQL:

CREATE ROLE role_name
[IDENTIFIED BY password];
Example Role in SQL
Example 1

To create a role called “developer” with the password “pwd”, the syntax looks like this:

CREATE ROLE testing
[IDENTIFIED BY pwd];

To grant or revoke privileges to a user through the role rather than assigning a privilege directly to each user If a role is defined by a password, when granting or revoking a role’s privileges, you will have to identify the role with a password.

You can grant or revoke the role privileges as below.

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

To grant CREATE TABLE privileges to users by creating role testing.

The first step is to create Role testing:

CREATE ROLE testing

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

GRANT CREATE TABLE TO testing;

Next, grant the role to the user:

GRANT testing TO user1;

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

REVOKE CREATE TABLE FROM testing;

The syntax for removing a role from a database is:

DROP ROLE role_name;
Example 3

To remove a role named developer, you can write:

DROP ROLE testing;

https://thuthuat.taimienphi.vn/grant-revoke-trong-sql-33413n.aspx
In the article on Taimienphi.vn, I have introduced you to the GRANT, REVOKE commands in SQL as well as the privileges and roles in SQL. In addition, readers can find and refer to some other articles of Taimienphi.vn to learn more information about CREATE, DELETE, … WHERE clause in SQL, ORDER BY, … and other articles about SQL.

.

Add a Comment

Your email address will not be published. Required fields are marked *