Database-level privileges

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.

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.

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. Execute 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).

Tip: To determine which users have DBA privileges on a database, run this query from DB-Access or your application:

select username,usertype from sysusers;

The output shows user names (for example, public and informix) followed by one of the following codes:

D = DBA privilege
C = Connect privilege
R = Resource privilege