Skip to main content

Data Control Language

The Data Control Language (DCL) component of the SQL language is used to create privileges to allow users access to, and manipulation of, the database. There are two main commands:

GRANT to grant a privilege to a user
REVOKE to revoke (remove) a privilege from a user

GRANT command

In order to do anything within an Oracle database you must be given the appropriate privileges. Oracle operates a closed system in that you cannot perform any action at all unless you have been authorised to do so. This includes logging onto the database, creating tables, views, indexes and synonyms, manipulating data (ie select, insert, update and delete) in tables created by other users, etc.

The SQL command to grant a privilege on a table is:

 
GRANT SELECT, INSERT, UPDATE, DELETE ON tablename TO username;
 

There are many more forms of the GRANT command, but this is sufficient for this Unit.

Any combination of the above privileges is allowed. You can issue this command on any tables that you have created. For example:

 
GRANT SELECT ON employee TO hn23;
GRANT SELECT, UPDATE, DELETE ON employee TO hn44;
 

REVOKE command

The SQL command to revoke a privilege on a table is:

REVOKE SELECT, INSERT, UPDATE, DELETE ON tablename FROM username;

For example:

 
REVOKE SELECT ON employee FROM hn23;
REVOKE SELECT, UPDATE, DELETE FROM hn44;
 

Next: Activity 27