An object grant can support another grant. Before withdrawing a delegator's privilege, identify the readers whose access depends on it. Also decide how newly created tables will receive their required permissions.
Oracle DBA Lesson 71 Part 2 — Trace Grant Dependencies and Plan Future Access
Follow the grant chain
APP_DATA owns EMPLOYEES. ACCESS_DELEGATE and REPORT_USER are separate users in the same pluggable database.
As APP_DATA:
GRANT SELECT ON employees TO access_delegate
WITH GRANT OPTION;The delegate can query EMPLOYEES and grant that SELECT privilege onward. Ownership and data remain with APP_DATA.
As ACCESS_DELEGATE:
GRANT SELECT ON app_data.employees TO report_user;The relationships are:
APP_DATA -- SELECT WITH GRANT OPTION --> ACCESS_DELEGATE
ACCESS_DELEGATE -- SELECT --> REPORT_USERWITH GRANT OPTION supports onward object grants. It cannot accompany an object grant to a role. A delegate with the necessary grant option can issue an ordinary object grant to a user or a role.
As APP_DATA:
REVOKE SELECT ON employees FROM access_delegate;Oracle also removes the onward grant that depended on the revoked privilege. If this chain supplied REPORT_USER's only query authority, a newly issued query of APP_DATA.EMPLOYEES is denied. Check SELECT, READ, enabled roles, PUBLIC, and applicable system privileges before predicting effective access. The revoke does not predict the fate of an already running statement.
GRANT and REVOKE are DDL. They can commit the current transaction implicitly. Finish or roll back unrelated work first.
See GRANT, REVOKE, and COMMIT.
An independent route can survive
In a separate starting case, establish the same chain, then have APP_DATA issue this direct grant before revoking the delegate's privilege:
GRANT SELECT ON employees TO report_user;The owner-to-reader grant is independent of the delegate. Revoking SELECT from ACCESS_DELEGATE removes the dependent chain. This direct grant remains usable by REPORT_USER.
Inspect the owner object's grant records as APP_DATA:
SELECT grantor, grantee, privilege, grantable
FROM user_tab_privs_made
WHERE table_name = 'EMPLOYEES'
AND grantee IN ('ACCESS_DELEGATE', 'REPORT_USER')
ORDER BY grantor, grantee;Rows before that revoke:
| GRANTOR | GRANTEE | PRIVILEGE | GRANTABLE |
|---|---|---|---|
| ACCESS_DELEGATE | REPORT_USER | SELECT | NO |
| APP_DATA | ACCESS_DELEGATE | SELECT | YES |
| APP_DATA | REPORT_USER | SELECT | NO |
The remaining row after the revoke:
| GRANTOR | GRANTEE | PRIVILEGE | GRANTABLE |
|---|---|---|---|
| APP_DATA | REPORT_USER | SELECT | NO |
GRANTOR identifies who made the grant. GRANTEE identifies its recipient. GRANTABLE describes onward grant authority. USER_TAB_PRIVS_MADE covers grants on objects owned by the current user, including grants made by a delegate. Keeping GRANTOR in the query makes the separate paths visible. These filters inspect one table and two recipients. They are not an inventory of every effective privilege.
From REPORT_USER:
SELECT employee_id, employee_name
FROM app_data.employees
ORDER BY employee_id;A query that still succeeds may be explained by another valid grant.
See USER_TAB_PRIVS_MADE and ALL_TAB_PRIVS_MADE.
Plan for tables created later
On Oracle 19c, an existing REPORT_ROLE can contain object grants for tables that already exist:
-- APP_DATA session; the role already exists.
GRANT SELECT ON employees TO report_role;
GRANT SELECT ON departments TO report_role;Users with that role granted and enabled can use those permissions. When APP_DATA later creates PROJECTS, the existing entries continue to identify EMPLOYEES and DEPARTMENTS. Include PROJECTS in the deployment's access changes if the report needs it:
GRANT SELECT ON projects TO report_role;Explicit object grants provide a reviewed set of accessible objects. They need maintenance when that set changes. Role creation, membership, and enablement are separate administration.
Later-release schema scope
Oracle AI Database 26ai documents schema privileges that apply to qualifying existing and future objects within a named schema. The following is a later-release comparison, unavailable as schema-grant syntax on 19c:
GRANT SELECT ANY TABLE
ON SCHEMA app_data TO report_role;This selects a continuing schema-wide table access policy. PROJECTS is included when created within APP_DATA. Use that breadth only when the role's work requires it, including access to future additions. A selected list of tables may be better served by explicit object grants. SELECT also permits operations such as row-locking queries. Consider the narrower READ privilege where appropriate.
Verify the installed release, edition, supported privileges, and required grant authority against its documentation before using this syntax. SYS and dictionary-protected schema restrictions apply. Schema privilege administration is distinct from the WITH GRANT OPTION chain above.
See schema privileges and GRANT.
Recap
WITH GRANT OPTION lets the delegate grant that object privilege onward to a user or a role. The option cannot accompany an object grant to a role. Revoking the delegate's SELECT also removes the onward grant that depended on it. When that chain was REPORT_USER's only query authority, a new query of APP_DATA.EMPLOYEES is denied. A direct grant from the owner to the reader is independent and remains. GRANTOR in USER_TAB_PRIVS_MADE shows who made each grant. Check SELECT, READ, enabled roles, PUBLIC, and applicable system privileges before treating one row as the whole access picture. GRANT and REVOKE can commit the current transaction implicitly.
On 19c, object grants on REPORT_ROLE name existing tables such as EMPLOYEES and DEPARTMENTS. A later table such as PROJECTS needs its own grant. Oracle AI Database 26ai documents schema privileges that apply to qualifying existing and future objects in a named schema, including GRANT SELECT ANY TABLE ON SCHEMA. That syntax is unavailable as schema-grant syntax on 19c. Use schema-wide breadth only when the role's work requires future tables as well. SELECT still permits row-locking queries; READ is the narrower query privilege.
No comments:
Post a Comment