3. Assigning Privileges and Granting Permissions : Grant Overhead : How Privileges for a Session Are Determined : Access to Databases and the Authorization Hierarchy
 
Share this page                  
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:
Authorization Identifier
Query Row Limit
The role identifier
1700
The user
1500
The group identifier
2000
The public
1000
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.