REVOKE

>>-REVOKE------------------------------------------------------->
 
      (1)                                   (2)                       (3)
>--+------+-+-| Database-Level Privileges |-------FROM--| User List |------+-------------+-+-><
   |      | '-DEFAULT ROLE--FROM--+-PUBLIC---------+-----------------------'             | |
   |      |                       | .-,----------. |                                     | |
   |      |                       | V            | |                                     | |
   |      |                       '---'--user--'-+-'                                     | |
   |      |               (4)                                                       (5)  | |
   |      '-| Role Name |-------FROM--+-PUBLIC-------------+--+-------------------+------' |
   |                                  | .-,--------------. |  '-AS--'--revoker--'-'        |
   |                                  | V                | |                               |
   |                                  '---+-'--user--'-+-+-'                               |
   |                                      '-'--role--'-'                                   |
   |                              (6)                                                      |
   '-+-| Table-Level Privileges |--------------------------------+--| FROM Options |-------'
     |  (1)                                (7)                   |
     '------+-| Routine-Level Privileges |---------------------+-'
            |  (5)                                   (8)       |
            '--------+-| Language-Level Privileges |-------+---'
                     |                           (9)       |
                     +-| Type-Level Privileges |-----------+
                     |                               (10)  |
                     '-| Sequence-Level Privileges |-------'
 
FROM Options:
 
|--FROM--------------------------------------------------------->
 
                     (3)   .-CASCADE--.                                 (5)
>----+-| User List |-------+----------+------+----+-------------------+-------|
     |                     '-RESTRICT-'      |    '-AS--'--revoker--'-'
     |        .-,--------------------------. |
     |  (1)   V                   (4)      | |
     '------------+-| Role Name |------+---+-'
                  '-'--user--'---------'

Notes:

Informix extension
See page Database-Level Privileges
See page User List
See page Role Name
Dynamic Server only
See page Table-Level Privileges
See page Routine-Level Privileges
See page Language-Level Privileges (IDS)
See page Type-Level Privileges (IDS)
See page Sequence-Level Privileges (IDS)

Element	Description	Restrictions	Syntax
revoker	Authorization identifier of the grantor of the privileges to be revoked	Must be grantor of the specified privileges	Owner Name, p. Owner Name
role	Role from which you revoke another role	Must exist	Owner Name, p. Owner Name
user	User whose role (or default role) you cancel	Must exist	Owner Name, p. Owner Name

Usage

To cancel privileges on one or more fragments of a table that has been fragmented by expression, see REVOKE FRAGMENT.

You can revoke privileges if any of the following conditions is true for the privileges that you are attempting to revoke on some database object:

You granted them and did not designate another user as grantor.
The GRANT statement specified you as grantor.
You are revoking privileges from PUBLIC on an object that you own, and those privileges were granted by default when you created the object.
You have database-level DBA privileges and you specify in the AS clause the name of a user who was grantor of the privilege.

The REVOKE statement can cancel any of the following access privileges or roles that a user, or PUBLIC, or a role currently holds:

Privileges on the database (but a role cannot hold database-level privileges)
Privileges on a table, synonym, view, or sequence object
Privileges on a user-defined data type (UDT), a user-defined routine (UDR), or on the SPL language
A non-default role, or the default role of PUBLIC or of a user.

You cannot revoke privileges from yourself. You cannot revoke grantor status from another user. To revoke a privilege that was granted to another user by the AS grantor clause of the GRANT statement, you must have the DBA privilege, and you must use the AS clause to specify that user as revoker.

If you enclose revoker, role, or user in quotation marks, the name is case sensitive and is stored exactly as you typed it. In an ANSI-compliant database, if you do not use quotation marks as delimiters, the name is stored in uppercase letters.

Database-Level Privileges

Database-Level Privileges:
 
|--+-DBA------+-------------------------------------------------|
   +-RESOURCE-+
   '-CONNECT--'

Three concentric layers of database-level privileges, Connect, Resource, and DBA, authorize increasing power over database access and control. Only a user with the DBA privilege can grant or revoke database-level privileges.

Because of the hierarchical organization of the privileges (as outlined in the privilege definitions that are described later in this section), if you revoke either the Resource or the Connect privilege from a user with the DBA privilege, the statement has no effect. If you revoke the DBA privilege from a user who has the DBA privilege, the user retains the Connect privilege on the database. To deny database access to a user with the DBA or Resource privilege, you must first revoke the DBA or the Resource privilege and then revoke the Connect privilege in a separate REVOKE statement.

Similarly, if you revoke the Connect privilege from a user who has the Resource privilege, the statement has no effect. If you revoke the Resource privilege from a user, the user retains the Connect privilege on the database.

Recommendation:

Only user informix can modify system catalog tables directly. Except as noted specifically in your database server documentation, however, do not use DML statements to insert, delete, or update rows of system catalog tables directly, because modifying data in these tables can destroy the integrity of the database.

Only users or PUBLIC can hold database-level privileges. You cannot revoke these privileges from a role, because a role cannot hold database level privileges.

The following table lists the keyword for each database-level privilege.

Privilege	Effect
DBA	Has all the capabilities of the Resource privilege and can perform the following additional operations: Grant any database-level privilege, including the DBA privilege, to another user. Grant any table-level privilege to another user or to a role. Grant a role to a user or to another role. Revoke a privilege whose grantor you specify as the revoker in the AS clause of the REVOKE statement. Restrict the Execute privilege to DBAs when registering a UDR. Execute the SET SESSION AUTHORIZATION statement. Create any database object. Create tables, views, and indexes, designating another user as owner of these objects. Alter, drop, or rename database objects, regardless of who owns it. Execute the DROP DISTRIBUTIONS option of the UPDATE STATISTICS statement. 3Execute DROP DATABASE and RENAME DATABASE statements.
RESOURCE	Lets you extend the structure of the database. In addition to the capabilities of the Connect privilege, the holder of the Resource privilege can perform the following operations: Create new tables. Create new indexes. Create new user-defined routines. Create new data types.
CONNECT	If you have this privilege, you can query and modify data, and modify the database schema if you own the database object that you want to modify. A user holding the Connect privilege can perform the following operations: Connect to the database with the CONNECT statement or another connection statement. Execute SELECT, INSERT, UPDATE, and DELETE statements, provided that the user has the necessary table-level privileges. Create views, provided that the user has the Select privilege on the underlying tables. Create synonyms. Create temporary tables and create indexes on temporary tables. Alter or drop a table or an index, if the user owns the table or index (or has the Alter, Index, or References privilege on the table). Grant privileges on a table, if the user owns the table (or was given privileges on the table with the WITH GRANT OPTION keyword).

Table-Level Privileges

Table-level privileges, also called table privileges, specify which operations a user or role can perform on a table or view in the database. You can use a synonym to specify the table or view on which you grant or revoke table privileges.

Select, Update, and References privileges can be granted on a subset of the columns of a table or view, but can be revoked only for all columns. If Select privileges are revoked from a user for a table that is referenced in the SELECT statement defining a view that the same user owns, then that view is dropped, unless it also includes columns from tables in another database.

Use the following syntax to specifying which table-level privileges to revoke from one or more users or roles:

Table-Level Privileges:
 
          .-PRIVILEGES-.
|--+-ALL--+------------+---------------+--ON--+----------+------>
   | .-,-----------------------------. |      '-owner--.-'
   | V                               | |
   '-----+-INSERT----------------+---+-'
         +-DELETE----------------+
         +-UPDATE----------------+
         |  (1)                  |
         '------+-SELECT-------+-'
                +-ALTER--------+
                +-INDEX--------+
                +-REFERENCES---+
                |  (2)         |
                '--------UNDER-'
 
>--+-table---+--------------------------------------------------|
   +-view----+
   '-synonym-'

Notes:

Informix extension
Dynamic Server only

Element	Description	Restrictions	Syntax
owner	Name of the user who owns the table, view, or synonym	Must be a valid authorization identifier	Owner Name, p. Owner Name
synonym, table, view	Synonym, table, or view on which privileges are granted	Must exist in the current database	Identifier, p. Identifier

In one REVOKE statement, you can list one or more of the following keywords to specify the privileges on the specified table to be revoked from the users or roles.

Privilege	Effect after REVOKE
INSERT	User cannot insert rows.
DELETE	User cannot delete rows.
SELECT	User cannot display data retrieved by a SELECT statement.
UPDATE	User cannot change column values.
INDEX	User cannot create permanent indexes. You must have the Resource privilege to take advantage of the Index privilege. (But any user who has the Connect privilege can create indexes on temporary tables.)
ALTER	The holder cannot add or delete columns, modify column data types, add or delete constraints, change the locking mode of a table from PAGE to ROW, nor add or drop a corresponding named-ROW-type table. The user also cannot enable or disable indexes, constraints, nor triggers, as described in SET Database Object Mode.

Privilege	Effect after REVOKE
REFERENCES	User cannot reference columns in referential constraints. You must also have the Resource privilege on the database to take advantage of the References privilege on tables. (You can add, however, a referential constraint during an ALTER TABLE statement. without holding the Resource privilege on the database.) Revoking the References privilege disallows cascading DELETE operations.
UNDER (IDS)	User cannot create subtables under a typed table.
ALL	This removes all of the table privileges that are listed above. (Here the PRIVILEGES keyword is optional. )

REVOKE

Syntax

Usage

Database-Level Privileges

Table-Level Privileges