Controlling User Access and Transactions
Controlling User Access and Transactions
This domain tests privileges, roles, and transaction control. Know the difference between system and object privileges, how grant options propagate, and which operations commit implicitly.
Core rules
- System privileges allow actions such as CREATE SESSION, CREATE TABLE, or CREATE ANY TABLE. Object privileges allow SELECT, INSERT, UPDATE, DELETE, REFERENCES, and EXECUTE on a specific object.
- GRANT privilege TO user [WITH GRANT OPTION] lets the grantee grant an object privilege onward. WITH ADMIN OPTION applies to system privileges and roles.
- REVOKE removes privileges. Revoking an object privilege granted WITH GRANT OPTION can cascade to privileges the grantee granted.
- Roles group privileges and can be granted to users; roles simplify administration but can be disabled.
- COMMIT makes changes permanent; ROLLBACK undoes uncommitted changes; SAVEPOINT creates a marker for partial rollback.
- DDL and many DCL statements issue implicit commits. Normal SQL*Plus exit commits; abnormal termination rolls back.
Worked example
GRANT SELECT, UPDATE ON hr.employees TO clerk WITH GRANT OPTION; REVOKE UPDATE ON hr.employees FROM clerk; The first statement lets clerk query and update employees and pass those object privileges on. The revoke removes UPDATE from clerk; it may also remove UPDATE from users to whom clerk granted it.
Traps
- Confusing WITH GRANT OPTION (object privileges) with WITH ADMIN OPTION (system privileges and roles).
- Expecting ROLLBACK to undo a committed transaction or DDL.
- Assuming a role is always enabled.
- Forgetting that a revoke can cascade for object privileges.