GRANT and REVOKE

GRANT gives a privilege to a user, role, or group. REVOKE takes it back. Together they are the day-to-day tools of DB2 authorization management.

GRANT statement

• GRANT lists one or more privileges, the object they apply to, and who receives them.
• Common table privileges are SELECT, INSERT, UPDATE, DELETE, ALTER, INDEX, and REFERENCES.
• WITH GRANT OPTION lets the receiver pass the privilege on to others.
• System privileges like DBADM, SYSADM, CREATETAB, and BINDADD are granted at the subsystem or database level.

Example: GRANT

• Example giving read and insert rights on the employee table:-
  GRANT SELECT, INSERT ON TABLE EMPLOYEE TO USER001; GRANT UPDATE (SALARY, PHONENO) ON TABLE EMPLOYEE TO MGR_ROLE; GRANT EXECUTE ON PROCEDURE GET_EMP_NAME TO APP_ID; GRANT DBADM ON DATABASE DBSALES TO DBA001;
• Column-level GRANT (SALARY, PHONENO) limits UPDATE to just those columns.
• Grants to roles are easier to manage than grants to dozens of individual user IDs.

REVOKE statement

• REVOKE removes a privilege that was granted earlier.
• REVOKE ... CASCADE also removes privileges that the receiver had passed on to others.
• Example taking rights back:-
  REVOKE INSERT ON TABLE EMPLOYEE FROM USER001; REVOKE DBADM ON DATABASE DBSALES FROM DBA001;
• Revoking a privilege does not drop objects the user already created with it.
• Always double-check the FROM list so you do not revoke from the wrong ID.