Grant Overhead

Grants can affect query processing time. There is overhead on queries for a table or view if:

• Permissions have been granted on the table or view
• Column-specific permissions are granted
• Many permissions are granted in general in the database

For the following, however, there is no overhead:

• For the table owner
• On certain public grants:
  • In select operations
  • Any operation for which all allowed permissions are specified for public
• If no permissions qualify (the query is simply aborted)

There is additional overhead during session initialization to evaluate database privileges for the authorization identifiers associated with the session. Because session initialization must read the catalogs in which groups, roles, and database privileges are stored, certain operations issued by the DBA or system administrator that write to these catalogs can be committed or rolled back as soon as possible. These operations include:

• Granting or revoking database privileges (either in VDBA or using the grant/revoke on database statements)
• Creating, altering, or dropping a group (either in VDBA or using the create/alter/drop group statements)
• Creating, altering, or dropping a role (either in VDBA or using the create/alter/drop role statements)

Multiple Permission Checks

Multiple permissions can apply to the same query, because the system catalog is scanned for all possible permissions that apply. Generally, this means the broadest grant applies. The hierarchy of evaluation is described in more detail below, but the hierarchy is generally not something the DBA needs to formally consider.

For example, assume that grants have been created to allow all permissions on the employee table to public, and that a grant has been created to allow a particular user, Susan, the select privilege on the employee table. Susan, as part of the public, can perform all operations on the employee table, even though her individual grant was only for select permission.

Note: If you want more restrictive grants to apply, the solution is to drop the inclusive grants to public, and define specific grants for specified groups or users.

Authorization Hierarchy

In any session, the privileges in effect for that session are derived from the privileges granted to the authorization identifiers (role, user, group, and public) associated with the session and any applicable defaults. If a particular privilege is defined for more than one authorization identifier associated with a session, then a hierarchy is used to determine which defined privilege is enforced for that session.

The authorization hierarchy, in order of highest to lowest precedence, is:

1. role
2. user
3. group
4. public

For each accessed object in a session, there is a search for a defined privilege that specifies that object and the desired access characteristics (for example, select, insert, execute, and so on).

If the specified object is a table, view, or database procedure, then one of the authorization identifiers in effect for the session must have the required privilege for that object in order for the session to access that object. In the case of these granted privileges that are otherwise restricted, the authorization identifiers are searched for any one that gives the required authorization.

For example, to insert into a specified table, one of the authorization identifiers associated with the session must have the insert permission defined for the specified table. If none of the authorization identifiers associated with the session has this permission and the user does not own the table, then the internal default is used. In this case, because the internal default for the insert permission is not to allow inserts, inserts are not allowed into the specified table.

The authorization hierarchy is also important when the specified object is the database, because the privileges defined on the database can be defined with different values for different authorization identifiers. When a database privilege is defined at differing levels, the hierarchy is used to determine which privilege to enforce.

For example, assume that query row limits have been defined differently for each authorization level as follows:

If a user starts a session and specifies both group and role identifiers, the limit defined for the role is enforced because it has the highest order of precedence in the hierarchy, giving the session a query row limit of 1700. Several other possible scenarios are described below:

• If no query row limit was defined for role, then the query row limit defined for that user is enforced, which is 1500 rows. This is also the case if the user had not specified a role identifier.
• If no query row limit was defined for that user, then the query row limit defined for the group (2000 rows) is enforced.
• If no query row limit was defined for group, or if the user had not specified a group identifier, then the query row limit defined for public (1000 rows) is enforced.
• If none of the identifiers had a query row limit defined, the internal default is enforced, which in this case is an unlimited numbers of rows.

Note: In cases where multiple authorizations apply, the resource limit associated with the highest order of precedence applies, not necessarily the one that grants the most resource.

Database Privileges for a Session

When a user begins a session:

• The privileges in effect for that session are derived from the privileges defined for the user identifier and for public. For example, while you might have the privilege to select all the tables in the database, you might only have the update permission on a limited number of those tablesIf the user includes the G or R flag, or both, on the command line when beginning the session, then the privileges for the specified group or role identifier are also in effect for the session.
• If the user has a default group identifier defined for the user ID, when the user begins a session without specifying a group identifier, the default group identifier is automatically applied to the session. A default group identifier can be specified for a user when a user object is created or modified.

  For more information on the command line flags, -G and -R, see the Command Reference Guide.

The authorization hierarchy is used to determine the session's database privileges. The hierarchy includes the privileges granted to the authorization identifiers in effect for the session and the internal defaults.

Dbmsinfo—View Permissions for Current Session

In addition to the various ways in which you can view permissions using VDBA, you can use the dbmsinfo function to obtain the current value of any database privilege in effect for the current session.

To issue a dbmsinfo request, use the following syntax:

select dbmsinfo('request_name');

The request_name can be any of the following parameters:

• connect_time_limt

  The session's value for the connect time limit, or 1 if none

• create_procedure

  "Y" is the session has create procedure privileges or "N" if not

• create_table

  "Y" is the session has create table privileges or "N" if not

• db_admin

  "Y" is the session has the db_admin privilege or "N" if not

• idle_time_limit

  The session's value for the idle time limit or -1 if none

• lockmode

  "Y" is the session can issue the set lockmode statement or "N" if not

• query_cost_limit

  The session's value for the query cost limit or -1 if none

• query_cpu_limit

  The session's value for the CPU limit or -1 if none

• query_io_limit

  The session's value for the query I/O limit or -1 if none

• query_page_limit

  The session's value for the query page limit or -1 if none

• query_row_limit

  The session's value for the query row limit or -1 if none

• session_priority

  The session's current priority or -1 if none

• select_syscat

  "Y" is the session has the select_syscat privilege or "N" if not

• table_statistics

  "Y" is the session has the table_statistics privilege or "N" if not

• update_syscat

  "Y" is the session has the update_syscat privilege or "N" if not

Example: Return the Value of Query Row Limit for Current Session

Assuming the query_row_limit permission for the current session is 50, the following query returns the value "50" in x:

select x = dbmsinfo('query_row_limit') as x;

Note: The dbmsinfo function allows other request_name values relating to other aspects of the current session. For details, see the chapter "Transactions and Error Handling" in the SQL Reference Guide.