DCL (Data Control Language)

DCL(Data Control Languages)/ Security

(In this section some example of create users, grant privilege or role, revoke or removal privilege & role, drop user. This is for control or database & your system.)

Used to Control Database Security (SYSTEM SECURITY, DATA SECURITY.)

Create User, Change Password, Assign Privilege/Rights,
GRANT PRIVILEGE / RIGHTS, REVOKE RIGHTS
CREATE ROLE, GRANT PRIVILEGE TO ROLE, GRANT ROLE TO USERS.

USER
CREATE USER
You can create new users by this command and assign password to it.
CREATE USER SCOTT IDENTIFIED BY TIGER;

ALTER USER
ALTER USER SCOTT IDENTIFIED BY TIGERS;

DROP USER
DROP USER SCOTT;


ROLE
CREATE ROLE
CREATE ROLE R1 IDENTIFIED BY R1;
CREATE ROLE R1

GRANT PRIVILEGE TO ROLE
GRANT CREATE SESSION, CREATE TABLE TO R1;
GRANT RESOURCE, CONNECT TO R1;
GRANT UPDATE, DELETE ON EMP TO R1;

GRANT CREATE SESSION, CREATE TABLE,
CREATE SEQUENCE, CREATE VIEW TO SCOTT;

GRANT WITH GRANT OPTION
GRANT select, insert ON departments TO scott WITH GRANT OPTION;

PRIVILEGE
GRANT PRIVILEGE /ROLE
GRANT RESOURCE, CONNECT, CREATE TABLE, CREATE SESSION, DBA TO SCOTT;
GRANT R1 TO SCOTT;

GRANT SELECT ON EMPLOYEES TO SUE, RICH;


REVOKE PRIVILEGE / ROLE
REVOKE CREATE SESSION FROM SCOTT;

REVOKE SELECT, INSERT ON DEPARTMENTS FROM SCOTT;

DROP ROLE
DROP ROLE R1;

-----end--------