Oracle SQL Statement Types and Usage - Part III

Data Control Language (DCL) 

The Data Control Language (DCL) is a subset of the Structured Query Lanaguge (SQL) that allows database administrators to configure security access to relational databases.Two types of DCL commands are GRANT and REVOKE. Only Database Administrator’s or owner’s of the database object can provide/remove privileges on a database object.

GRANT:
Gives user’s access privileges to database.

Syntax:

GRANT privilege_name
ON object_name
TO {user_name |PUBLIC |role_name}
[WITH GRANT OPTION];

Examples:

grant select on
emp to scott;

grant select,insert,delete on
emp to scott;

REVOKE:
Withdraw access privileges given with the GRANT command.

Syntax:

REVOKE privilege_name
ON object_name
FROM {user_name |PUBLIC |role_name} ;

Example:
revoke insert, delete on
emp from scott;

========================

Transaction Control Language (TCL) :

TCL statements used to manage the changes made by DML statements. It allows statements to be grouped together into logical transactions.

COMMIT:
Permanent save to work done.here we can’t roll back once applied commit.

Example:
COMMIT;

SAVEPOINT:
identify a point in a transaction and which you can later roll back also.

Example:

save point to sp1;

roll back to sp1;

ROLLBACK:
Restore database to original since the last COMMIT

Example:

ROLLBACK;