DCL

DCL (Data Control Language) is the part of SQL that controls who is allowed to do what in the database. It has only two statements: GRANT and REVOKE.

What is DCL

• DCL manages privileges: permission to read a table, update it, run a plan, and so on.
• Without the right privilege, even correct SQL fails with an authorization error (SQLCODE -922 or -551).
• Privileges are usually given by the DBA or the table owner, not by application programmers.

GRANT

• GRANT gives a privilege to a user or a group (authorization id).
• Common table privileges: SELECT, INSERT, UPDATE, DELETE. Example: GRANT SELECT ON EMP TO PAYROLL.
• WITH GRANT OPTION lets the receiver pass the privilege on to others. Use it sparingly.

REVOKE

• REVOKE takes back a privilege that was granted earlier.
• Revoking a privilege can cascade: users who got it through WITH GRANT OPTION lose it too.
• Always re-test the application after a REVOKE, because bound plans may start failing.

Example

• Giving read access to one user, full access to another, then taking it back:-