DCL (Data Control Language) commands are used to manage access privileges for users and database objects. These commands allows to grant specific permissions to users or roles, enabling them to interact with the database in controlled ways.
GRANT
Assigns specific privileges (e.g., SELECT, INSERT, UPDATE, DELETE) on a table, schema object, or system privilege to a user or role.
Syntax
GRANT privilege_type[(column_list)] ON [schema_name.]object_type [object_name] TO user_name [role_name];
Example
GRANT SELECT, INSERT ON Customers TO Sales_User;
-- Granting all privileges on the Orders table to the Manager role
GRANT ALL ON Orders TO Manager;
-- Granting CREATE SESSION system privilege to a new user
GRANT CREATE SESSION TO New_User;
REVOKE
Takes away previously granted privileges from a user or role.
Syntax
REVOKE privilege_type[(column_list)] ON [schema_name.]object_type [object_name] FROM user_name [role_name];
Example
-- Revokes delete permission
REVOKE DELETE ON Orders FROM Marketing_User;
-- Revoking all privileges on the Customers table from the Guest role
REVOKE ALL ON Customers FROM Guest;
