DCL: Users, Roles & Privileges
DCL (Data Control Language) is a sub-language of SQL used to control privileges in a database.
It is used for administrative purpose like:
- Create /Remove User
- Grant /Revoke privileges on objects.
Some Useful Syntax
Syntax to create a user: CREATE USER <USERNAME> IDENTIFIED BY <PASSWORD>; Syntax to delete an existing user: DROP USER <USERNAME> [CASCADE]; Cascade keyword is used to delete non-empty users. Syntax to change password: ALTER USER <USERNAME> IDENTIFIED BY <PASSWORD>; Syntax to lock/unlock a user: ALTER USER <USERNAME> ACCOUNT LOCK|UNLOCK;
Privileges are various rights to manipulate object in a database that can be assigned to a user. Privileges are of two types:
- System Privileges
- Object Privileges
System Privileges are those privileges that are assigned to a user.
Object Privileges are those privileges that are are assigned to an object.
System privileges can be granted or revoked only by the superuser, whereas object privileges can be granted or revoked by the superuser as well as the user who owns the object.
The frequently used system privileges are:
- CREATE SESSION
- CREATE TABLE
- CREATE VIEW
- CREATE SYNONYM
- CREATE SEQUENCE
- CREATE INDEX
- CREATE TYPE
- CREATE PROCEDURE
Note: CREATE SESSION privilege is the primary privilege which is responsible to activate a user by creating session id.
Frequently used object privileges are:
Grant and Revoke Privileges
Any privileges can be assigned or removed from a user. The commands used to do so are:
Syntax to grant system privileges: GRANT <SP1>, <SP2> ... N|<ROLE> TO <USERNAME>; Syntax to revoke system privileges: REVOKE <SP1>, <SP2> ... N|<ROLE> FROM <USERNAME>; Syntax to grant object privileges: GRANT <OP1>, <OP2> ... N ON <OBJECT_NAME> TO <USERNAME>; Syntax to revoke object privileges: REVOKE <OP1>, <OP2> ... N ON <OBJECT_NAME> FROM <USERNAME>;
GRANT CREATE_SESSION, CREATE_TABLE, CREATE_VIEW TO SYSUSER; REVOKE CREATE SESSION FROM SYSUSER; GRANT SELECT, INSERT ON EMP TO SYSUSER; REVOKE INSERT ON EMP FROM SYSUSER;
Multiple system privileges are grouped together to create a role. Roles can only be created in super user and can only contains system privileges.
Examples: CREATE ROLE MYROLE; GRANT CREATE SESSION, CREATE TABLE TO MYROLE; GRANT MYROLE TO SYSUSER;
Drop statement is used to delete a role.
DROP ROLE MYROLE;