SQL Reference

REVOKE (Database Authorities)

This form of the REVOKE statement revokes authorities that apply to the entire database.

Invocation

This statement can be embedded in an application program or issued through the use of dynamic SQL statements. It is an executable statement that can be dynamically prepared. However, if the bind option DYNAMICRULES BIND applies, the statement cannot be dynamically prepared (SQLSTATE 42509).

Authorization

The privileges held by the authorization ID of the statement must include at least one of the following:

• DBADM authority
• SYSADM authority.

To revoke DBADM authority, SYSADM authority is required.

Refer to REVOKE (Index Privileges), REVOKE (Package Privileges), REVOKE (Schema Privileges), REVOKE (Server Privileges) and REVOKE (Table, View, or Nickname Privileges) for other GRANT statements.

Syntax

             .-,----------------------.
             V                        |
>>-REVOKE------+-BINDADD-----------+--+--ON DATABASE------------>
               +-CONNECT-----------+
               +-CREATETAB---------+
               +-CREATE_NOT_FENCED-+
               +-IMPLICIT_SCHEMA---+
               +-DBADM-------------+
               '-LOAD--------------'
 
           .-,------------------------------------.
           V                                      |
>----FROM----+-+-------+---authorization-name--+--+------------><
             | +-USER--+                       |
             | '-GROUP-'                       |
             '-PUBLIC--------------------------'
 

Description

BINDADD

Revokes the authority to create packages. The creator of a package automatically has the CONTROL privilege on that package and retains this privilege even if his BINDADD authority is subsequently revoked.

The BINDADD authority cannot be revoked from an authorization-name holding DBADM authority without also revoking the DBADM authority.

CONNECT

Revokes the authority to access the database.

Revoking the CONNECT authority from a user does not affect any privileges that were granted to that user on objects in the database. If the user is subsequently granted the CONNECT authority again, all previously held privileges are still valid (assuming they were not explicitly revoked).

The CONNECT authority cannot be revoked from an authorization-name holding DBADM authority without also revoking the DBADM authority (SQLSTATE 42504).

CREATETAB

Revokes the authority to create tables. The creator of a table automatically has the CONTROL privilege on that table, and retains this privilege even if his CREATETAB authority is subsequently revoked.

The CREATETAB authority cannot be revoked from an authorization-name holding DBADM authority without also revoking the DBADM authority (SQLSTATE 42504).

CREATE_NOT_FENCED

Revokes the authority to register functions that execute in the database manager's process. However, once a function has been registered as not fenced, it continues to run in this manner even if CREATE_NOT_FENCED is subsequently revoked from the authorization ID that registered the function.

The CREATE_NOT_FENCED authority cannot be revoked from an authorization-name holding DBADM authority without also revoking the DBADM authority (SQLSTATE 42504).

IMPLICIT_SCHEMA

Revokes the authority to implicitly create a schema. It does not affect the ability to create objects in existing schemas or to process a CREATE SCHEMA statement.

DBADM

Revokes the DBADM authority.

DBADM authority cannot be revoked from PUBLIC (because it cannot be granted to PUBLIC).

Revoking DBADM authority does not automatically revoke any privileges that were held by the authorization-name on objects in the database, nor does it revoke BINDADD, CONNECT, CREATETAB, IMPLICIT_SCHEMA, or CREATE_NOT_FENCED authority.

LOAD

Revoke the authority to LOAD in this database.

FROM

Indicates from whom the authorities are revoked.

USER

Specifies that the authorization-name identifies a user.

GROUP

Specifies that the authorization-name identifies a group name.

authorization-name,...

Lists one or more authorization IDs.

The authorization ID of the REVOKE statement itself cannot be used (SQLSTATE 42502). It is not possible to revoke the authorities from an authorization-name that is the same as the authorization ID of the REVOKE statement.

PUBLIC

Revokes the authorities from PUBLIC.

Rules

• If neither USER nor GROUP is specified, then:
  • If all rows for the grantee in the SYSCAT.DBAUTH catalog view have a GRANTEETYPE of U, then USER will be assumed.
  • If all rows have a GRANTEETYPE of G, then GROUP will be assumed.
  • If some rows have U and some rows have G, then an error (SQLSTATE 56092) is raised.
  • If DCE authentication is used, then an error is raised (SQLSTATE 56092).

Notes

• Revoking a specific privilege does not necessarily revoke the ability to perform the action. A user may proceed with their task if other privileges are held by PUBLIC or a group, or if they have a higher level authority such as DBADM.

Examples

Example 1:  Given that USER6 is only a user and not a group, revoke the privilege to create tables from the user USER6.

  REVOKE CREATETAB ON DATABASE FROM USER6

Example 2:  Revoke BINDADD authority on the database from a group named D024. There are two rows in the SYSCAT.DBAUTH catalog view for this grantee; one with a GRANTEETYPE of U and one with a GRANTEETYPE of G.

  REVOKE BINDADD ON DATABASE FROM GROUP D024

In this case, the GROUP keyword must be specified; otherwise an error will occur (SQLSTATE 56092).