GRANT AND REVOKE  - IBM Mainframe

The SQL command by which DB2 can specify the operations that an authorized user may execute on a DB2 object is GRANT and the command by which the privileges once granted is withdrawn is REVOKE. DB2 recognizes a wide range of privileges like table privileges, database privileges, etc., which we have discussed in the earlier section. When DB2 is installed for the first time a person is designated as the system administrator for the DB2 system with certain special privileges. The system administrator is identified by DB2 by his Authorization ID. This person will be responsible for the overall control of the system like monitoring the system and collecting performance statistics, etc. Thus initially there is one user with all the powers vested in him and who has the power to grant and revoke privileges to other users. If the system administrator, SYSADM, chooses not to grant privileges to anybody, then nobody else can do anything at all. Although, the system administrator is the holder of the SYSADM privileges, he can grant the SYSADM privileges to anybody he chooses. But the difference here is that those privileges can be revoked if needed, but the privileges of the system administrator can never be revoked. So be careful when choosing the System administrator!

Once the users have been given the required privileges, they can start create objects like base tables, and the person gets the full privileges, including granting those privileges to others, for that object. The granting of privileges is done using the GRANT command. The syntax of the GRANT statement is given below:

GRANT privileges [ON [type] objects] TO users;

Where 'privileges' is the list of one or more privileges, separated by commas or instead the key word ALL PRIVILEGES can be used for granting all the privileges. Similarly 'users' is either al list of the Authorization IDs separated by commas or the special key word PUBLIC, which means all the Users. 'Objects' indicates the list of names or one or more objects all of the same type separated by commas and 'type' indicates the type of the object. If the 'type' clause is omitted, then it is assumed to be TABLE. The ON clause does not apply if the privileges granted are system privileges.
Most of the examples are self-explanatory. Others are explained:

  • GRANT SELECT ON TABLE S TO THOMAS;
  • GRANT SELECT, UPDATE (STATUS, CITY) ON TABLE S TO THOMAS, MATHEW;
  • GRANT ALL PRIVILEGES ON TABLE S, P TO AUSTIN;
  • GRANT ALL ON TABLE S, P TO AUSTIN;
  • GRANT SELECT OM TABLE P TO PUBLIC;
  • GRANT ALL ON TABLE P TO PUBLIC;
  • GRANT DELETE ON TABLE P TO ALEXIS;
  • GRANT EXECUTE ON PLAN PALNB TO THOMAS;
  • GRANT CREATTB ON DATABASE DET18 TO PILLAI; - User PILLAI is permitted to create tables in database det18.
  • GRANT USE OF TABLESPACE DBX.TS23 TO ARUL; - User ARUL is permitted to use the table space TS23 in database DBX to store any tables he may create.
  • GRANT CREATEDBC TO AZAHAR; - User AZAHAR is permitted to create new databases and if he does so he is automatically given DBCTRL privilege over those databases.

If a privilege is granted to a user then they can be taken back form him using the REVOKE command. The general format of REVOKE statement is:

REVOKE privileges [ON [type] objects] FROM users;

While revoking a given privilege form a given user causes all packages dependent on that privilege to be flagged invalid and hence causes an automatic rebind on the next invocation. Some examples are given below:

  • REVOKE SELECT ON TABLE S FROM THOMAS;
  • REVOKE UPDATE ON TABLE P FROM AUSTIN;
  • REVOKE SYSADM FROM ALEXIS;

It is not possible to be column-specific when revoking an update privilege.

Consider the following case. User 'Thomas' has authority to grant a privilege P to another user. Then 'Thomas' also has the authority to that privilege P to another user' with the GRANT option'. Suppose say 'Thomas' grants the privileges P to 'Mathew' WITH GRANT OPTION. Now 'Mathew' has the authority to grant the privileges to a third user with or without grant option. Now say, if 'Thomas' revokes all the privileges that he has granted to Mathew. Then the revocation will cascade. That is both Mathew and persons that Mathew has granted the privileges will lose their privileges. This happens automatically.


All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

IBM Mainframe Topics