Managing explicit privileges
You can use the SQL GRANT and REVOKE statements to grant and remove privileges if you enable authorization checking during DB2® installation. You can grant to or revoke privileges from authorization IDs or roles if they run in a trusted context. You can revoke only privileges that are explicitly granted.
About this task
You can grant privileges in the following ways:
- Grant a specific privilege on one object in a single statement
- Grant a list of privileges
- Grant privileges on 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
If you grant privileges on a procedure or a package, all versions of that procedure or package have those privileges. DB2 ignores duplicate grants and keeps only one record of a grant in the catalog. The suppression of duplicate records applies not only to explicit grants, but also to the implicit grants of privileges that are made when a package is created.
For example, suppose that Susan grants the SELECT privilege on the EMP table to Ray. Then suppose that Susan grants the same privilege to Ray again, without revoking the first grant. When Susan issues the second grant, DB2 ignores it and maintains the record of the first grant in the catalog.
Database privileges that are granted on DSNDB04 apply to all implicitly created databases. For example, if you have the DBADM authority on DSNDB04, you can select data from any table in any implicitly created database. If you have the STOPDB privilege on DSNDB04, you can stop any implicitly created database. However, you cannot grant the same authorities or privileges to others on any implicitly created database.
- Granting privileges to a role
You can grant privileges to a role by using the GRANT statement. You can associate primary authorization IDs with a role in the definition of the trusted context and then use the GRANT statement with the ROLE option to grant privileges to the role. - Granting privileges to the PUBLIC ID
You can grant to the PUBLIC ID privileges
or authorities other than CREATE_SECURE_OBJECT, system DBADM, DATAACCESS,
or ACCESSCTRL.
- Granting privileges to remote users
A query that arrives at your local DB2 subsystem through the distributed data facility (DDF) is accompanied by an authorization ID. After connection processing, the ID can be translated to another value and be associated with secondary authorization IDs. - Granting privileges through views
You can grant most table privileges (except ALTER, REFERENCES, TRIGGER, and INDEX) on a view. By creating a view and granting privileges through it, you can give an ID access to only a specific combination of data. - Granting privileges through administrative authorities
A DB2 administrative authority is generally vested with a predefined group of privileges. If you need to grant a user a specific set of privileges that are already included in an authority, you can grant the user the authority, instead of individual privileges. - Granting privileges with the GRANT statement
You can assign privileges to an ID or a role by issuing the GRANT statement. - Revoking privileges with the REVOKE statement
You can use the REVOKE statement to remove the privileges that you explicitly grant to an ID or a role.