How Privileges for a Session Are Determined
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).
Access to Tables, Views, or Procedures and the Authorization Hierarchy
If the specified object attempting to be accessed 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 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.
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.