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.
Note:
• 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.
• 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 User 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.
Query_row_limit
Specifies the maximum number of rows returned per query on the database.
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
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.
Table_statistics
Allows grantees to view and create database table statistics.
Default: All authorization identifiers can view and create table statistics.
Update_syscat
Allows grantees to update system catalogs.
Default: No authorization identifier can update system catalogs.
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.