DCL Commands

GRANT: It is used to provide previleges (select / dml operations) on a table to the user

Syntax: SQL> GRANT ALL / PRIVILEGE_NAME ON TO USER_NAME

ALL :  select, update,delete,insert previleges will be provided

REVOKE : It is used to cancell the prvileges given to the user on a table

Syntax : SQL> GRANT ALL / PREVILEGE_NAME FROM USER_NAME

============================================================================

SQL> show user;
Scott

SQL> CREATE USER TEMP_USER IDENTIFIED BY welcome;
User created

sql> SELECT * FROM ALL_USERs; to views users information

SQL> Connect temp_user/welcome;
Connected

SQL> Show user;
temp_user

SQL> select * from tab;
No rows selected

SQL> connect scott/tiger;
connected

SQL> Show user;
Scott

SQL> GRANT ALL ON EMP TO TEMP_USER;
Granted

SQL> connect temp_user/welcome;
connected

SQL> show user
temp_user

SQL> select * from scott.emp;
Eno EName sal
—————–
100 aaa 15000
101 baa 15500

SQL> update scott.emp set sal=sal+500;
10 rows updated

SQL> select * from scott.emp;

Cancelling prvileges given on emp table to temp_user

SQL> SHOW USER;
temp_user

SQL> connect scott/tiger;
connected

sql> SHOW USER
SCOTT

SQL> REVOKE ALL ON EMP FROM TEMP_USER;

sql> connect temp_user/welcome;
connected

SQL> SHOW USER
TEMP_USER

sql> SELECT * FROM SCOTT.EMP;

Post Views: 491