A role groups related privileges so that access for a duty can be managed through role membership. For an interactive query, check both the role grant and whether the role is enabled in the connected session.
Oracle DBA Lesson 73 Part 1 — A Local Role from Request to Removal
Build the access path
The duty is to read department names from APP_DATA.DEPARTMENTS in the PDB APP_PDB. The local role DEPT_READ holds that SELECT privilege, and the reporting user APP_READER receives the role:
APP_DATA.DEPARTMENTS
SELECT granted to DEPT_READ
DEPT_READ granted to APP_READERThe role groups privileges. The table remains in the owner's schema. Several users with the same duty can receive this focused role. Roles can also contain other roles, so a complete investigation may need to follow nested memberships.
See user roles.
Confirm the session is in APP_PDB, then create an unused local role there. Creating the role requires CREATE ROLE:
SHOW CON_NAME
CREATE ROLE dept_read NOT IDENTIFIED;Creating the role in a PDB makes it local. CONTAINER=CURRENT is optional in this case. The new role has no privileges initially. NOT IDENTIFIED means this ordinary role does not require a role password when it is enabled. It does not provide database login permission. The creator receives this ordinary role with ADMIN OPTION.
See CREATE ROLE.
The table owner supplies the object privilege:
GRANT SELECT ON app_data.departments TO dept_read;Grant the role to the reader:
GRANT dept_read TO app_reader;Granting the role requires authority over it, for example the role's ADMIN OPTION. The reader receives no administration option from this statement. This path adds no direct table grant to the reader.
See GRANT and granting and revoking roles.
Inspect membership
Query the direct role grant:
SELECT grantee, granted_role, admin_option
FROM dba_role_privs
WHERE grantee = 'APP_READER'
AND granted_role = 'DEPT_READ';The membership row is:
| GRANTEE | GRANTED_ROLE | ADMIN_OPTION |
|---|---|---|
| APP_READER | DEPT_READ | NO |
This row establishes a direct role grant to the reader. ADMIN_OPTION=NO means this particular grant does not allow the reader to grant or revoke the role for other users. Independent administrative authority would need separate inspection.
DBA_ROLE_PRIVS also includes DEFAULT_ROLE, which identifies login-default designation. It is not a live indicator of whether the role is enabled in another session. USER_ROLE_PRIVS provides information about the current user's grants.
See DBA_ROLE_PRIVS.
Inspect activation in the reader's session
Connect as the reader to the same PDB. Login normally enables the user's default roles. Inspect the current session:
SELECT role
FROM session_roles
WHERE role = 'DEPT_READ';SESSION_ROLES reports currently enabled roles, including enabled indirect roles. Run it in the session whose behavior you are investigating. An administrator's own session does not report another user's active role set.
See SESSION_ROLES.
For contrast, the reader disables all roles for the current session:
SET ROLE NONE;
SELECT role
FROM session_roles
WHERE role = 'DEPT_READ';The filtered query now has no rows. The membership grant still exists. If this role is the reader's only access path to the table, an interactive table query cannot use that path while the role is disabled. Direct privileges and PUBLIC privileges are separate paths. SET ROLE NONE does not remove them.
The reader then enables the ordinary role:
SET ROLE dept_read;
SELECT role
FROM session_roles
WHERE role = 'DEPT_READ';The enabled role is:
| ROLE |
|---|
| DEPT_READ |
SET ROLE dept_read sets the session's active role list to the named role and its applicable nested roles. Other roles not named are disabled. This changes session activation, not permanent default-role configuration. Reconnect to restore the account's login defaults. The statement does not change ALTER USER ... DEFAULT ROLE settings. A nondefault ordinary role can still be enabled when the user has been granted it, so nondefault status alone is not an access prohibition. Password, global, and secure application roles have additional rules.
See SET ROLE.
With the role enabled:
SELECT department_name
FROM app_data.departments;One possible result is:
| DEPARTMENT_NAME |
|---|
| Operations |
| Finance |
Actual rows depend on the table contents. Establishing the grant, session activation, and query outcome together explains the access. If the query still works with the role disabled, investigate direct object or system privileges, PUBLIC, and other applicable paths.
Remove membership, then retire the role if appropriate
When the reader's duty ends, remove that membership:
REVOKE dept_read FROM app_reader;The role continues to exist and can remain assigned to other users. Oracle 19c documents an important session effect: a user who already has the role enabled can continue exercising its privileges while it remains enabled, but cannot subsequently enable the revoked role. Check the dictionary change, test a fresh reader connection, and review existing sessions. Revocation is not proof that an already enabled session has stopped using the role.
See REVOKE.
If the custom role itself is no longer required by any recipient, retire it:
DROP ROLE dept_read;This removes the role and its grants to all recipients, and removes it from default-role lists. It does not drop the departments table. Existing sessions with the role already enabled are not affected by the drop. New sessions cannot enable the dropped role. DROP ROLE requires the role with ADMIN OPTION or DROP ANY ROLE. Removing a role and removing a user's membership have different scopes.
See DROP ROLE and dropping roles.
No comments:
Post a Comment