DB2 Version 9.7 for Linux, UNIX, and Windows

Effect of revoking privileges from roles

When privileges are revoked, this can sometimes cause dependent database objects, such as views, packages or triggers, to become invalid or inoperative.

The following examples show what happens to a database object when some privileges are revoked from an authorization identifier and privileges are held through a role or through different means.

Example of revoking privileges from roles

The security administrator creates the role DEVELOPER and grants the user BOB membership in this role:
```
CREATE ROLE DEVELOPER
GRANT ROLE DEVELOPER TO USER BOB
```
User ALICE creates a table, WORKITEM:
```
CREATE TABLE WORKITEM (x int)
```

The database administrator grants SELECT and INSERT privileges on table WORKITEM to PUBLIC and also to the role DEVELOPER:

GRANT SELECT ON TABLE ALICE.WORKITEM TO PUBLIC
GRANT INSERT ON TABLE ALICE.WORKITEM TO PUBLIC
GRANT SELECT ON TABLE ALICE.WORKITEM TO ROLE DEVELOPER
GRANT INSERT ON TABLE ALICE.WORKITEM TO ROLE DEVELOPER

User BOB creates a view, PROJECT, that uses the table WORKITEM, and a package, PKG1, that depends on the table WORKITEM:
```
CREATE VIEW PROJECT AS SELECT * FROM ALICE.WORKITEM
PREP emb001.sqc BINDFILE PACKAGE USING PKG1 VERSION 1
```
If the database administrator revokes SELECT privilege on table ALICE.WORKITEM from PUBLIC, then the view BOB.PROJECT remains operative and package PKG1 remains valid because the view definer, BOB, still holds the privileges required through his membership in the role DEVELOPER:
```
REVOKE SELECT ON TABLE ALICE.WORKITEM FROM PUBLIC
```
If the database administrator revokes SELECT privilege on table ALICE.WORKITEM from the role DEVELOPER, the view BOB.PROJECT becomes inoperative and package PKG1 becomes invalid because the view and package definer, BOB, does not hold the required privileges through other means:
```
REVOKE SELECT ON TABLE ALICE.WORKITEM FROM ROLE DEVELOPER
```

Example of revoking DBADM authority

In this example, the role DEVELOPER holds DBADM authority and is granted to user BOB.

The security administrator creates the role DEVELOPER:
```
CREATE ROLE DEVELOPER
```
The system administrator grants DBADM authority to the role DEVELOPER:
```
GRANT DBADM ON DATABASE TO ROLE DEVELOPER
```
The security administrator grants user BOB membership in this role:
```
GRANT ROLE DEVELOPER TO USER BOB
```
User ALICE creates a table, WORKITEM:
```
CREATE TABLE WORKITEM (x int)
```

User BOB creates a view PROJECT that uses table WORKITEM, a package PKG1 that depends on table WORKITEM, and a trigger, TRG1, that also depends on table WORKITEM:

CREATE VIEW PROJECT AS SELECT * FROM ALICE.WORKITEM
PREP emb001.sqc BINDFILE PACKAGE USING PKG1 VERSION 1
CREATE TRIGGER TRG1 AFTER DELETE ON ALICE.WORKITEM 
              FOR EACH STATEMENT MODE DB2SQL
       INSERT INTO ALICE.WORKITEM VALUES (1)

The security administrator revokes the role DEVELOPER from user BOB:
```
REVOKE ROLE DEVELOPER FROM USER BOB
```
Revoking the role DEVELOPER causes the user BOB to lose DBADM authority because the role that held that authority was revoked. The view, package, and trigger are affected as follows:
- View BOB. PROJECT is still valid.
- Package PKG1 becomes invalid.
- Trigger BOB.TRG1 is still valid.
View BOB.PROJECT and trigger BOB.TRG1 are usable while package PKG1 is not usable. View and trigger objects created by an authorization ID holding DBADM authority are not affected when DBADM authority is lost.