Data Control Language – DCL

Suppose we have created a table STUDENT in the database. Now who can view or access these tables? No one other than who has created it! We need to explicitly tell the database, who can view this table and what kind of access like read, write, delete should be given to the other users.

Similarly, some of the users who were accessing this table have left the organization. But still they have access to this table, which is not acceptable. Hence we need to remove their access on such tables and database.

DCL languages are used to control the user access to the database, tables, views, procedures, functions and packages. They give different levels of access to the objects in the database.

GRANT

GRANT provides the privileges to the users on the database objects. The privileges could be select, delete, update and insert on the tables and views. On the procedures, functions and packages it gives select and execute privileges. We can either give all the privileges or any one or more privileges to the objects. The syntax of GRANT is as below:

GRANT privilege_name
ON object_name
TO {user_name |PUBLIC |role_name}
[WITH GRANT OPTION];

Where

  • Privilege_name is the level of access given to the users. Some of the access rights are ALL, DELETE, UPDATE, INSERT, EXECUTE and SELECT.
  • Object_name is the name of a database object like TABLE, VIEW, PROCEDURE, FUNCTION, PACKAGE and SEQUENCE.
  • User_name is the name of the user to whom an access is being granted.
  • PUBLIC is used to grant access rights to all users.
  • ROLES are a set of privileges grouped together.
  • WITH GRANT OPTION – allows a user to grant access rights to other users. i.e.; usually grants are given by the user who has created the database objects. But with this option, the users who have got the access rights can also provide the grants and access other tables/views.
GRANT SELECT, INSERT ON STUDENT TO Mathew;

Here SELECT and INSERT grants are given to Mathew on STUDENT table.

GRANT EXECUTE on sp_getStudentNames to Joseph;

This grant gives the execution rights to Joseph on the stored procedure sp_getStudentNames.

REVOKE

REVOKE removes the privileges given on the database objects. We can remove all the privileges or remove one or more privileges from the objects.

REVOKE privilege_name
ON object_name
FROM {user_name |PUBLIC |role_name}

REVOKE INSERT ON STUDENT FROM Mathew; -- Removes the INSERT grant from Mathew on STUDENT Table
Translate ยป