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 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');
No comments:
Post a Comment