Use of grant and revoke privileges to control access

The SQL GRANT statement lets you grant explicit privileges to authorization IDs. The REVOKE statement lets you take them away. Only a privilege that has been explicitly granted can be revoked.

Granting privileges is very flexible. For example, consider table privileges. You can grant all the privileges on a table to an ID. Alternatively, you can grant separate, specific privileges that allow that ID to retrieve data from the table, insert rows, delete rows, or update specific columns. By granting or not granting those privileges on views of the table, you can effectively determine exactly what action an ID can or cannot take on the table.

You can use the GRANT statement to assign privileges as follows:

  • Grant privileges to a single ID or to several IDs in one statement.
  • Grant a specific privilege on one object in a single statement, grant a list of privileges, or grant privileges over a list of objects.
  • Grant ALL, for all the privileges of accessing a single table or for all privileges that are associated with a specific package.

Examples of grant privileges

The following examples show how to grant some system privileges, use privileges, and table privileges.

Begin general-use programming interface information.

To grant the privileges of system operator authority to user NICHOLLS, the system administrator uses the following statement: 

GRANT SYSOPR TO NICHOLLS;
Assume that your business decides to associate job tasks with authorization IDs. In the following examples, PKA01 is the ID of a package administrator, and DBA01 is the ID of a database administrator. Suppose that the system administrator uses the ADMIN authorization ID, which has SYSADM authority, to issue the following GRANT statements:
  • GRANT PACKADM ON COLLECTION GOLFS TO PKA01 WITH GRANT OPTION;

    This statement grants PACKADM authority to PKA01. PKA01 acquires package privileges on all packages in the collection named GOLFS and the CREATE IN privilege on that collection. In addition, specifying WITH GRANT OPTION gives PKA01 the ability to grant those privileges to others.

  • GRANT CREATEDBA TO DBA01;

    CREATEDBA grants DBA01 the privilege to create databases, and DBA01 acquires DBADM authority over those databases.

  • GRANT USE OF STOGROUP SG1 TO DBA01 WITH GRANT OPTION;

    This statement allows DBA01 to use storage group SG1 and to grant that privilege to others.

  • GRANT USE OF BUFFERPOOL BP0, BP1 TO DBA01 WITH GRANT OPTION;

    This statement allows DBA01 to use buffer pools BP0 and BP1 and to grant that privilege to others.

The following examples show specific table privileges that you can grant to users:

  • GRANT SELECT ON DEPT TO PUBLIC;

    This statement grants SELECT privileges on the DEPT table. Granting the select privilege to PUBLIC gives the privilege to all users at the current server.

  • GRANT UPDATE (EMPNO,DEPT) ON TABLE EMP TO NATZ;

    This statement grants UPDATE privileges on columns EMPNO and DEPT in the EMP table to user NATZ.

  • GRANT ALL ON TABLE EMP TO KWAN,ALONZO WITH GRANT OPTION;

    This statement grants all privileges on the EMP table to users KWAN and ALONZO. The WITH GRANT OPTION clause allows these two users to grant the table privileges to others.

End general-use programming interface information.

Examples of revoke privileges

The same ID that grants a privilege can revoke it by issuing the REVOKE statement. If two or more grantors grant the same privilege to an ID, executing a single REVOKE statement does not remove the privilege for that ID. To remove the privilege, each ID that explicitly granted the privilege must explicitly revoke it.

Here are some examples of revoking privileges that were previously granted.

Begin general-use programming interface information.
  • REVOKE SYSOPR FROM NICHOLLS;

    This statement revokes SYSOPR authority from user NICHOLLS.

  • REVOKE UPDATE ON EMP FROM NATZ;

    This statement revokes the UPDATE privilege on the EMP table from NATZ.

  • REVOKE ALL ON TABLE EMP FROM KWAN,ALONZO;

    This statement revokes all privileges on the EMP table from users KWAN and ALONZO.

An ID with SYSADM or SYSCTRL authority can revoke privileges that are granted by other IDs, such as the following statements:

  • REVOKE CREATETAB ON DATABASE DB1 FROM PGMR01 BY ALL;

    In this statement, the CREATETAB privilege that user PGMR01 holds is revoked regardless of who or how many people explicitly granted this privilege to this user.

  • REVOKE CREATETAB, CREATETS ON DATABASE DB1 FROM PGMR01 BY DBUTIL1;

    This statement revokes privileges that are granted by DBUTIL1 and leaves intact the same privileges if they were granted by any other ID.

End general-use programming interface information.

Revoking privileges can be more complicated. Privileges can be revoked as the result of a cascade revoke. In this case, revoking a privilege from a user can also cause that privilege to be revoked from other users.