Access to Databases and the Authorization Hierarchy
When the specified object attempting to be accessed is the database, the authorization hierarchy is also important 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 resources.