Database Grants
Database permissions are defined on the database as a whole. They set a number of limits that affect the authorization identifiers (that is, groups, roles, users, or public) specified when the grant is defined.
Most of the database permissions are prohibiting permissions--if not specified, the default is no restrictions. Prohibiting permissions, even if defined, are not enforced for the owner of the database or for any user with the security privilege, such as the system administrator.
Note: To override the default for database permission, create a grant for the permission that specifies the grantee as public.
The valid database permissions are as follows:
Access
Enables grantees to connect to the database.
Default: All authorization identifiers can connect to all public databases.
Private databases can be accessed only by users who are explicitly granted permission to access them. Permission to access a private database can be granted in the following ways:
• Using a database grant
• Enabling the database under Access to Non-Granted Databases in the appropriate dialog (for example, the Create Group dialog)
Connect_time_limit
Specifies the maximum time (in seconds) that a session can consume.
Default: No connect time limit
Create_procedure
Enables grantees to create database procedures in the database.
Default: All authorization identifiers can create database procedures.
Create_table
Enables grantees to create tables in the database.
Default: All authorization identifiers can create tables.
Db_admin
Gives grantees unlimited database privileges for the database and the ability to impersonate another user (using the -u flag).
Default: Granted to the owner of the database and to any user with the security privilege, such as the system administrator. For all other users, the default is not to allow unlimited database privileges.
Idle_time_limit
Specifies the maximum time that a session can take between issuing statements.
Default: No idle time limit
Lockmode
Enables grantees to issue the set lockmode statement.
Default: All authorization identifiers can issue the set lockmode statement.
Query_cost_limit
Specifies the maximum cost per query on the database, in terms of disk I/O and CPU usage.
Default: All authorization identifiers are allowed an unlimited cost per query.
Query_cpu_limit
Specifies the maximum CPU usage per query on the database.
Default: All authorization identifiers are allowed unlimited CPU usage per query.
Query_io_limit
Specifies the maximum number of I/O requests per query on the database.
Default: All authorization identifiers are allowed an unlimited number of I/O requests.
The database privileges query_io_limit and query_row_limit are enforced based on estimates from the query optimizer. If the optimizer predicts that a query can require more I/O operations or return more rows than are allowed for the session, the query is aborted prior to execution. This prevents resource consumption by queries that are not likely to succeed.
Query_page_limit
Specifies the maximum number pages per query on the database.
Default: All authorization identifiers are allowed an unlimited number of pages per query.
[Actual] Query_row_limit
Specifies the maximum ESTIMATED or ACTUAL number of rows returned per query on the database. ESTIMATED is the default behavior and does not need to be specified.
Default: All authorization identifiers are allowed an unlimited number of rows per query.
Select_syscat
Allows a session to query system catalogs to determine schema information.
Default: Sessions are allowed to query the system catalogs.
Session_priority
Note: Whether this privilege has an effect depends on the operating system.
Determines whether a session is allowed to change its priority, and if so what its initial and highest priority can be.
Default: A session cannot change its priority.
Update_syscat
Allows grantees to update system catalogs.
Default: No authorization identifier can update system catalogs.
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 can require more I/O operations or return more rows than are allowed for the session, the query is aborted prior to execution. This prevents resource consumption by queries that are not likely to succeed. The accuracy of the optimizer's estimates can be impeded by out-of-date or insufficient statistics about the contents of tables. For Query_row_limit, you can specify that actuals rather than estimates be used.
Preventing Permissions--Each permission has a corresponding preventing permission to specifically disallow the permission. For example, to prevent access to the database, specify the Noaccess permission.
How Database Permissions for a Session are Determined
The database permissions for a session are calculated when the session connects to the database and remain in effect for the duration of the session. If, after a session connects to a database, the database permissions for one of that session’s authorization identifiers are changed, the active session is not affected. Any new sessions that are established with the same authorization identifiers are subject to the revised database permissions.
Database Grant Examples
Here are examples of granting permissions on a database:
1. Define a query row limit of 100 rows on the new_accts database for user Ralph:
GRANT QUERY_ROW_LIMIT 100
ON DATABASE new_accts TO ralph;
2. Prohibit group prodrams from creating tables and database procedures in the new_accts database:
GRANT NOCREATE_TABLE, NOCREATE_PROCEDURE
ON DATABASE new_accts TO prodrams;
3. A database privilege can be superseded by issuing a subsequent GRANT statement for the user authorization. For example, assume that user karenk has been granted a query row limit of 1000 rows on the customers database:
GRANT QUERY_ROW_LIMIT 1000
ON DATABASE customers TO karenk;
Her job changes and she does not need to access so much of the database, so the DBA issues a new GRANT statement giving her a query row limit of 250:
GRANT QUERY_ROW_LIMIT 250
ON DATABASE customers TO karenk;
This new privilege replaces the old 1000-row privilege. If the DBA subsequently revokes the new limit:
GRANT NOQUERY_ROW_LIMIT
ON DATABASE customers TO karenk;
karenk’s query row limit privilege for the database becomes undefined (the old limit of 1000 is not re-established). At this point if no value for QUERY_ROW_LIMIT has been defined for any of the other authorization identifiers associated with karenk’s session, then the number of rows that her session’s queries can return is unrestricted.