Data Control Language (DCL) Statements

      DCL statements are used to control access to data stored in the database. GRANT statement is used to allow specified users to perform specified task and REVOKE statement is used to cancel the permission which are previously granted.

GRANT : It is used provide the access or privileges on database objects to users.

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

SQL> sho user
USER is "MARK"
SQL>


SQL> select * from scott.employees;
select * from scott.employees
 *
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> show user;
USER is "SCOTT"
SQL>
SQL> GRANT SELECT ON EMPLOYEES TO MARK;

Grant succeeded.

SQL>
SQL> show user;
USER is "MARK"
SQL>
SQL> select * from scott.employees where rownum 

REVOKE : It is used to remove privileges on database object from users.

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

SQL> show user;
USER is "SCOTT"
SQL>
SQL> REVOKE SELECT ON EMPLOYEES FROM MARK;

Revoke succeeded.

SQL>
SQL> show user;
USER is "MARK"
SQL>
SQL> select * from scott.employees where rownum 

Click hereClick for more for more on privileges and roles.

Advertisements