Database-Level Privileges

Database-level access privileges affect access to a database. Only individual users, rather than roles, can hold database privileges.

Database-Level Privileges

1 CONNECT
1 RESOURCE
1 DBA

When you create a database with the CREATE DATABASE statement, you are the owner and automatically receive all database-level privileges.

The database remains inaccessible to any other users until you, as DBA, grant database privileges to them.

As database owner, you also receive table-level privileges on all tables in the database automatically. For more information about table-level privileges, see Table-Level Privileges.
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.

When database-level privileges conflict with table-level privileges, the more restrictive privileges take precedence.

Database access levels are, from lowest to highest, Connect, Resource, and DBA. Use the corresponding keyword to grant a level of access privilege.
Privilege Effect
CONNECT Lets you query and modify data

You can modify the database schema if you own the database object that you intend to modify. Any user with 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 the user has the necessary table-level privileges
  • Create views, provided the user has the Select privilege on the underlying tables
  • Create synonyms
  • Create temporary tables and create indexes on the temporary tables
  • Alter or drop a table or an index, provided the user owns the table or index (or has Alter, Index, or References privileges on the table)
  • Grant privileges on a table or view, provided the user owns the table (or was given privileges on the table with the WITH GRANT OPTION keywords)
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 functions:
  • Create new tables
  • Create new indexes
  • Create new UDRs
  • Create new data types
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 them
  • Execute the DROP DISTRIBUTIONS option of the UPDATE STATISTICS statement
  • Execute DROP DATABASE and RENAME DATABASE statements

User informix has the privilege required to alter the tables of the system catalog, including the systables table.

The following example uses the PUBLIC keyword to grant the Connect privilege on the currently active database to all users: 
GRANT CONNECT TO PUBLIC;

You cannot grant database-level privileges to a role. Only individual users or PUBLIC can hold database-level privileges.