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 <5;

 EMPNO     ENAME      JOB       MGR        HIREDATE  SAL        COMM
---------- ---------- --------- ---------- --------- ---------- ----------
 DEPTNO
----------
 7369      SMITH      CLERK     7902       17-DEC-80 800
 20

 7499      ALLEN      SALESMAN  7698       20-FEB-81 1600       300
 30

 7521      WARD       SALESMAN  7698       22-FEB-81 1250       500
 30


 EMPNO     ENAME      JOB       MGR        HIREDATE  SAL        COMM
---------- ---------- --------- ---------- --------- ---------- ----------
 DEPTNO
----------
 7566      JONES      MANAGER   7839       02-APR-81 2975
 20


SQL>

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 <5;
select * from scott.employees where rownum 
 *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL>

Click here for more on privileges and roles.

Advertisements

One thought on “Data Control Language (DCL) Statements

  1. Awesome blog you have here but I was wondering if you knew of any forums
    that cover the same topics discussed in this article?
    I’d really love to be a part of community where I can get responses from other experienced individuals that share the same interest.
    If you have any recommendations, please let me know. Bless you!

    Liked by 1 person

Post your Comment

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s