SQL Tutorial : DCL (Data Control Language)

SQL Tutorial : DCL (Data Control Language)

Let's learn about next topic: DCL

DCL: DATA CONTROL LANGUAGE

******************************

- GRANT  => GIVING THE RIGHTS

- REVOKE => GETTING BACK THE RIGHTS

- CREATE USER USER1

IDENTIFIED BY LION

- ALTER USER USER1

IDENTIFIED BY TIGER

SYSTEM PRIV:

- BASIC PRIVS A USER MUST HAVE TO INTERACT WITH THE

DATABASE

- 100 TO 150 SYSTEM PRIVS

- CREATE SESSION,CREATE TABLE,CREATE TRIGGER,CREATE PROCEDURE ,
CREATE VIEW ...ETC

- SQL>CONN SYSTEM /TIGER

- SQL>GRANT CREATE SESSION TO USER1;

- SQL>GRANT CREATE TABLE TO USER1;

- SQL>GRANT UNLIMITED TABLESPACE TO USER1;

- SQL>CONN USER1/TIGER

- SQL>CREATE TABLE SAMPLE(ID NUMBER,NAME VARCHAR2(20));

- SQL >DESC SAMPLE

- SQL>GRANT CREATE SESSION TO USER1 WITH ADMIN OPTION;

OBJECT PRIV:

- RIGHTS GIVEN AND RECIEVED  TO OTHER USERS BY THE OBJECT

OWNERS

- DROP USER USER1 CASCADE

ROLE:

*****

- ROLE IS A NAMED GROUP OF RELATED PRIVS

- SQL> CREATE ROLE R1;

- SQL> GRANT CREATE SESSION ,CREATE TABLE TO R1;

- SQL> GRANT R1 TO USER1;

- SQL> GRANT UNLIMITED TABLESPACE TO USER1;   

DEFAULT ROLES:

****************

- CONNECT,RESOURCE

- SQL>GRANT CONNECT,RESOURCE TO COMPANY2

        IDENTIFIED BY TIGER

OBJECT PRIVS:

**************

HR:

- SQL> GRANT SELECT ,UPDATE(SALARY)

          ON EMPLOYEES

          TO COMPANY1

COMPANY1:

- SQL> DESC HR.EMPLOYEES

- SQL> SELECT LAST_NAME,SALARY

          FROM HR.EMPLOYEES

WHERE DEPARTMENT_ID=30

- SQL> UPDATE HR.EMPLOYEES

         SET SALARY=20000

WHERE EMPLOYEE_ID=111;

HR:

- SQL> REVOKE SELECT ,UPDATE

         ON EMPLOYEES   

         FROM COMPANY1

HR:

SQL> GRANT SELECT ,UPDATE(SALARY)

          ON EMPLOYEES

          TO COMPANY1 WITH GRANT OPTION

HR =>COMPANY1(WITH GRANT OPTION) => COMPANY2

- WHEN HR REVOKES THE RIGHTS FROM COMPANY1

IT IS REVOKED FROM EVERY ONE TO WHOM

RIGHTS ARE PASSED THROUGH WITH GRANT OPTION

DD:

- USER_SYS_PRIVS

- USER_TAB_PRIVS

- USER_ROLE_PRIVS

- SELECT GRANTED_ROLE

FROM USER_ROLE_PRIVS;

- SELECT PRIVILEGE

FROM USER_SYS_PRIVS;

- SELECT PRIVILEGE FROM

ROLE_SYS_PRIVS

WHERE ROLE IN('CONNECT','RESOURCE');