Database-level access privileges affect access to a database. Only
individual users, rather than roles, can hold database privileges.
Database-Level Privileges
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:
GRANTCONNECTTOPUBLIC;
You cannot grant database-level privileges to a role. Only individual
users or PUBLIC can hold database-level privileges.