An object privilege allows a particular action on a named object. For an employee report, decide which table the reader may query, whether write access is necessary, and which columns the reader should see. Verify the grant record and the reader's actual operations after making the change.
Oracle DBA Lesson 71 Part 1 — Grant Access to One Table and Verify It
What an object grant names
APP_DATA owns EMPLOYEES. REPORT_USER runs the employee report. An object grant names the owner, the object, the recipient, and the action.
| Component | Example | Meaning |
|---|---|---|
| Owner | APP_DATA | Schema containing the table |
| Object | EMPLOYEES | Specific table being accessed |
| Recipient | REPORT_USER | User receiving the privilege |
| Action | SELECT or READ | Query the table, with the distinctions below |
The owner already has the privileges on objects in its schema and can grant those object privileges. No ANY granting privilege is required. A grant on APP_DATA.EMPLOYEES does not grant access to the owner's other tables.
READ, SELECT, and UPDATE
| Privilege on a table | What it permits |
|---|---|
READ | Query with a SELECT statement. It does not permit SELECT ... FOR UPDATE or an exclusive LOCK TABLE. |
SELECT | Query, including SELECT ... FOR UPDATE. It also permits table locking. |
UPDATE | Change stored values with UPDATE, subject to applicable additional conditions. |
For a consumer that only needs ordinary queries, Oracle recommends the narrower READ object privilege. Both READ and SELECT use a SELECT statement to retrieve data. SELECT is not equivalent to "unable to acquire locks." This lesson keeps an existing SELECT grant and removes its unnecessary UPDATE grant. Replacing SELECT with READ requires a review of the application's locking requirements and of every other grant. Adding a READ row does not make that replacement.
See managing object privileges.
Remove the unnecessary write privilege
A query-only report needs the query privilege alone. The statements below start from a direct SELECT and UPDATE grant, then remove UPDATE. Run them as the table owner. The unqualified object name is the owner's own table in that session.
GRANT SELECT, UPDATE ON employees TO report_user;Remove the action the report does not require:
REVOKE UPDATE ON employees FROM report_user;The owner, the object, and the recipient stay the same. The owner's direct UPDATE grant is removed. The separate direct SELECT grant remains. Revoke does not delete the table or its data. When the recipient has another source of update authority, the ability to update can remain.
GRANT, REVOKE, and CREATE VIEW are DDL. Oracle implicitly commits before a syntactically valid DDL statement, even if it later errors, and after successful DDL. Finish or roll back the current transaction before running them. A later ROLLBACK does not undo a successful privilege change.
See GRANT, REVOKE, and COMMIT.
Read the remaining grant
In the owning APP_DATA session:
SELECT grantee, table_name, privilege, grantable
FROM user_tab_privs_made
WHERE table_name = 'EMPLOYEES'
AND grantee = 'REPORT_USER'
AND grantor = USER
ORDER BY privilege;USER_TAB_PRIVS_MADE describes object grants on objects owned by the current user. GRANTOR = USER selects grants issued by that owner. Unquoted Oracle names are stored in uppercase, so these dictionary predicates use uppercase literals.
The remaining row is:
| GRANTEE | TABLE_NAME | PRIVILEGE | GRANTABLE |
|---|---|---|---|
| REPORT_USER | EMPLOYEES | SELECT | NO |
GRANTEE: recipient of this grant.TABLE_NAME: named object, within this owner's schema.PRIVILEGE: action granted on it.GRANTABLE = NO: this grant does not add authority to grant that object privilege onward.
The absent UPDATE row confirms removal within this filtered owner-to-reader path. It does not list every effective access path. Column-level grants appear in the *_COL_PRIVS views and need their own check. The task here is the reader's usable access.
See USER_TAB_PRIVS_MADE and ALL_TAB_PRIVS_MADE.
Query the table as the reader
In the REPORT_USER session, connected to the same database:
SELECT employee_id, employee_name
FROM app_data.employees
ORDER BY employee_id;APP_DATA. identifies the owner of the table. With rows for employees 101 and 102, the result is:
| EMPLOYEE_ID | EMPLOYEE_NAME |
|---|---|
| 101 | Employee 101 |
| 102 | Employee 102 |
A successful query shows that this connected reader can run this statement on this object under the session's current privileges. An error that the object is missing can also come from the name, the container, or a privilege. That error alone does not prove that the object is absent.
Check the update that should be denied
In that same reader session:
UPDATE app_data.employees
SET salary = salary WHERE 1 = 0;1 = 0 is false, so no rows qualify. Oracle still checks the privileges the statement requires. With no remaining update authority, the statement fails with ORA-01031: insufficient privileges.
If the statement succeeds and reports zero rows updated, the reader still has sufficient update authority. Zero changed rows are not a denial. Trace the other paths: enabled roles, another grantor, PUBLIC, column-level grants, and applicable system privileges. ALL_TAB_PRIVS can show grants involving the user, enabled roles, and PUBLIC. It does not inventory system privileges or every privilege mechanism.
Issue ROLLBACK before any later DDL, whether the statement fails or succeeds. The false predicate prevents row changes. It does not make a mutating statement free of other execution effects: statement-level triggers and other code that runs with the statement can still execute.
The allowed query and the denied update belong to the same user, object, and container, together with the grant row.
See ALL_TAB_PRIVS.
A report's column list does not restrict the grant
The report asks only for identifiers and names. A table-level SELECT or READ grant also permits a query of SALARY:
SELECT employee_id, employee_name, salary
FROM app_data.employees
ORDER BY employee_id;| EMPLOYEE_ID | EMPLOYEE_NAME | SALARY |
|---|---|---|
| 101 | Employee 101 | 5000 |
| 102 | Employee 102 | 6000 |
The user chooses the columns in each query. The earlier report projection does not constrain later queries. Oracle's column-specific grant syntax is available for INSERT, UPDATE, and REFERENCES, not a column list on SELECT. When particular columns must be hidden, expose the intended columns through a view and control every other route to the base data.
See GRANT.
Restrict exposed columns with an owner-created view
The owner of the underlying table creates the view, which requires CREATE VIEW. As APP_DATA:
CREATE VIEW employee_names AS
SELECT employee_id, employee_name
FROM employees;
GRANT READ ON employee_names TO report_user;
REVOKE SELECT ON employees FROM report_user;Granting READ on the view permits the ordinary query without adding row-locking query authority. The view exposes only its selected columns. Removing the direct base-table SELECT grant is necessary in this case. Also remove or otherwise resolve any separate READ, SELECT, broad system, or role path that still exposes the base table. Creating the view leaves any privilege that already exposes the base table in place.
In the reader's session:
SELECT employee_id, employee_name
FROM app_data.employee_names
ORDER BY employee_id;The view query returns the exposed columns. Projecting SALARY from this view fails because that column is not defined in it. A query of the base table fails when no other privilege still exposes APP_DATA.EMPLOYEES.
A synonym supplies an alternative name for an underlying object. Access still comes from privileges on that object. Creating or using a synonym does not itself grant access.
See CREATE VIEW and managing object privileges.
Recap
An object privilege allows one action on one named object. APP_DATA can grant SELECT or READ on EMPLOYEES to REPORT_USER without granting the owner's other tables, and without an ANY granting privilege. READ fits ordinary queries: the reader still uses a SELECT statement, and READ does not allow SELECT ... FOR UPDATE or an exclusive LOCK TABLE. SELECT allows those locks. UPDATE allows changes. Removing UPDATE leaves an existing SELECT grant in place. Adding READ does not replace SELECT.
As the owner, REVOKE UPDATE ON employees FROM report_user removes that direct UPDATE grant. USER_TAB_PRIVS_MADE, filtered to EMPLOYEES, REPORT_USER, and GRANTOR = USER, shows the remaining SELECT row with GRANTABLE = NO. That value means this grant does not confer authority to grant the privilege onward. A missing UPDATE row covers only that filtered path. Column grants are in the *_COL_PRIVS views. In the reader's session, the query of employee_id and employee_name from app_data.employees is allowed, and UPDATE ... WHERE 1 = 0 fails with ORA-01031 when no update path remains. A successful update that changes zero rows means update authority is still present. ALL_TAB_PRIVS shows grants for the user, enabled roles, and PUBLIC. A table-level query grant includes SALARY even when one report omits it. Column-specific grants exist for INSERT, UPDATE, and REFERENCES, not as a column list on SELECT. An owner-created view can expose employee_id and employee_name, with READ on the view and every path to the base table removed. A synonym is another name for an object. It does not grant access. GRANT, REVOKE, and CREATE VIEW commit implicitly, so ROLLBACK does not undo them.
No comments:
Post a Comment