Access Control and User Privileges
Domain 11 tests Oracle privilege management: users, system privileges, object privileges, roles, and grant options. The exam expects least-privilege reasoning and precise GRANT/REVOKE syntax.
Core rules
- A user must be able to connect and create a session before doing normal work; CREATE SESSION is a system privilege.
- System privileges allow actions such as CREATE TABLE or CREATE VIEW. Object privileges allow actions on a specific object, such as SELECT, INSERT, UPDATE, DELETE, or EXECUTE.
- GRANT gives a privilege. REVOKE removes it. Object privileges can be granted to a user, a role, or PUBLIC.
- WITH GRANT OPTION on an object privilege lets the grantee grant that privilege to others. WITH ADMIN OPTION applies to roles and some system privileges.
- Roles bundle privileges and simplify administration. Grant roles to users rather than many individual privileges when appropriate.
- PUBLIC is a special group representing all users; grants to PUBLIC should be used sparingly.
Worked example
GRANT SELECT, INSERT ON hr.employees TO clerk; GRANT SELECT ON hr.employees TO clerk WITH GRANT OPTION;
The first statement lets clerk query and insert into hr.employees but not update or delete. The second statement grants SELECT again with the ability for clerk to grant SELECT on hr.employees to another user or role. It does not grant INSERT with grant option.
Traps
Object privileges are not the same as system privileges. A user cannot grant a privilege they do not hold with grant option. Granting to PUBLIC can expose data too broadly. Revoking a role can remove many privileges at once. Privileges on a view do not automatically include privileges on the base table.
Key points
- CREATE SESSION is a system privilege.
- SELECT on a table is an object privilege.
- WITH GRANT OPTION allows onward grants.
- Roles bundle privileges.
- Avoid broad grants to PUBLIC.