DATABASE Privileges
Database privileges control the consumption of computing resources.
To override the default for a database privilege, grant a specific value to PUBLIC. For example, by default, everyone (PUBLIC) has the privilege to create database procedures. To override the default, grant NOCREATE_PROCEDURE to PUBLIC, and grant the CREATE_PROCEDURE privilege to any user, group, or role that you want to have this privilege. (Users, groups, and roles are referred to collectively as authorization IDs.)
Database privileges do not apply to DBAs in their own databases, nor to security administrators.
The database privileges in effect for a session are determined by the values that were granted to the authorization IDs in effect for the session, according to the following hierarchy:
1. Role
2. User
3. Group
4. Public
For example, if different values for QUERY_ROW_LIMIT are granted to PUBLIC, and to the user, group, and role that are in effect for a session, the value for the role of the session prevails.
Valid database privileges are as follows:
[NO]ACCESS
Allows the specified authorization IDs to connect to the specified database.
NOACCESS prevents the specified authorization IDs from connecting.
Note: The access bitmask of the iidatabase_info catalog is set only if database utilities are used to change the access rights to the database. Using a GRANT [NO]ACCESS ON DATABASE dbname TO PUBLIC statement does not change the access from global to private or vice versa in iidatabase_info.
[NO]CREATE_PROCEDURE
Allows the specified authorization IDs to create database procedures in the specified database.
NOCREATE_PROCEDURE prevents the specified users, groups, or roles from creating database procedures.
Default: All authorization IDs can create database procedures.
[NO]CREATE_SEQUENCE
Allows the specified authorization IDs to create, alter and drop sequences in the specified database.
NOCREATE_SEQUENCE prevents the specified authorization IDs from creating sequences. By default, all authorization IDs can create, alter and drop sequences.
[NO]CREATE_TABLE
Allows the specified authorization IDs to create tables in the specified database.
NOCREATE_TABLE prevents the specified authorization IDs from creating tables.
Default: All authorization IDs can create tables.
[NO]DB_ADMIN
Confers unlimited database privileges for the specified database and the ability to specify effective user (using the -u flag). A session that has the DB_ADMIN privilege does not have all the rights that a DBA has; some utilities can be run only by a DBA. The DBA of a database and users with the SECURITY privilege have the DB_ADMIN privilege by default. For all other users, the default is NODB_ADMIN.
[NO]LOCKMODE
Allows the specified authorization IDs to issue the SET LOCKMODE statement.
NOLOCKMODE prevents the specified users, groups, or roles from issuing the SET LOCKMODE statement.
Default: Everyone can issue the SET LOCKMODE statement.
[NO]QUERY_COST_LIMIT
Specifies the maximum cost per query on the database, in terms of disk I/O and CPU usage.
Default: Authorization identifiers are allowed an unlimited cost per query.
[NO]QUERY_CPU_LIMIT
Specifies the maximum CPU usage per query on the database.
Default: Authorization identifiers are allowed unlimited CPU usage per query.
[NO]QUERY_IO_LIMIT
Specifies the maximum estimated number of I/O requests allowed for a single query for the specified authorization IDs when connected to the specified database. Integer must be a non-negative integer (or 0 to specify that no I/O is performed).
NOQUERY_IO_LIMIT grants an unlimited number of I/O requests per query.
Default: NOQUERY_IO_LIMIT
[NO]QUERY_PAGE_LIMIT
Specifies the maximum number of pages per query on the database.
Default: Authorization identifiers are allowed an unlimited number of pages per query.
[NO]QUERY_ROW_LIMIT
Query_row_limit integer specifies the maximum estimated number of rows returned by a single query for the specified authorization IDs when connected to the specified database. Integer must be a positive number (or 0 to specify that no rows are returned).
NOQUERY_ROW_LIMIT allows a single query to return an unlimited number of rows.
Default: NOQUERY_ROW_LIMIT
[NO]UPDATE_SYSCAT
Allows the specified authorization IDs to update system catalogs when working in a session connected to the iidbdb.
[NO]SELECT_SYSCAT
Allows a session to query system catalogs to determine schema information. When connected to the iidbdb database, this includes the master database catalogs such as iiuser and iidatabase. SELECT_SYSCAT can be granted to user, group, role or public, and can only be issued when connected to the iidbdb database.
This privilege restricts user queries against the core DBMS catalogs containing schema information, such as iirelation and iiattribute. Standard system catalogs such as iitables can still be queried.
[NO]CONNECT_TIME_LIMIT
Limits the total connect time that a session can consume. The connect time is checked periodically by the DBMS Server and if the limit has been exceeded for a session, it is disconnected, rolling back any open database transactions.
The units are seconds. The maximum connection time limit is approximately 130 years. The minimum connection time limit is 1 second.
As with other database privileges this can be granted to user, group, role or public, and can only be issued when connected to the iidbdb database.
Default: No limit, that is, a session can remain connected indefinitely.
[NO]IDLE_TIME_LIMIT
Specifies the time that a session can take between issuing statements. The idle time for each session is checked periodically by the DBMS Server, and if a session exceeds its idle time limit it is disconnected, rolling back any open database transactions.
The units are seconds. The maximum idle time limit is approximately 130 years. The minimum idle time limit is 1 second. Idle_time_limit can be granted to user, group, role or public, and can only be issued when connected to the iidbdb database.
Default: No limit, that is, a session can remain idle indefinitely without being disconnected.
[NO]SESSION_PRIORITY
Determines whether a session is allowed to change its priority, and if so, its initial and highest priority.
If NOSESSION_PRIORITY (the default) is specified, users can not alter their session priority.
If SESSION_PRIORITY is specified, users can alter their session priority, up to the limit determined by the privilege.
[NO]TABLE_STATISTICS
Allows users to view (by way of SQL and statdump) and create (by way of optimizedb) database table statistics.
If statistics exist in the database catalogs the DBMS Server automatically uses them when processing queries, even if the user does not possess this privilege.
[NO]TIMEOUT_ABORT
Allows the specified authorization IDs to issue the SET JOINOP TIMEOUTABORT statement.
NOTIMEOUT_ABORT prevents the specified users, groups, or roles from issuing the SET JOINOP TIMEOUTABORT statement.
Default: Everyone can issue the SET JOINOP TIMEOUTABORT statement.
Note: The restrictions set by QUERY_COST_LIMIT, QUERY_CPU_LIMIT, QUERY_IO_LIMIT, QUERY_PAGE_LIMIT, and QUERY_ROW_LIMIT are enforced based on estimates from the DBMS query optimizer. If the optimizer predicts that a query consumes more I/Os than allowed by the session, the query is aborted prior to execution. The accuracy of the optimizer's estimates can be impeded by out-of-date or insufficient statistics about the contents of tables.