Controlling User Access and Privileges
Controlling User Access and Privileges
This domain tests authentication-related object creation and authorization. You need to distinguish system privileges from object privileges, use roles, and apply grant options correctly.
Core rules
- A user is a schema with a password and privileges. CREATE USER establishes the account; CREATE SESSION is required to connect.
- System privileges allow actions such as CREATE TABLE, CREATE VIEW, or CREATE ANY TABLE. Object privileges allow actions on a specific object, such as SELECT, INSERT, UPDATE, DELETE, EXECUTE, REFERENCES, or ALTER.
- GRANT assigns privileges; REVOKE removes them. Roles group privileges and can be granted to users or other roles. Default roles are enabled at login.
- WITH ADMIN OPTION lets a grantee grant a system privilege or role to others. WITH GRANT OPTION lets a grantee grant an object privilege to others.
- Revoking an object privilege granted WITH GRANT OPTION can cascade to dependent grants. Revoking a system privilege does not cascade in the same way.
- PUBLIC is a special role available to all users. Granting to PUBLIC should be limited because it broadens access widely.
Worked example
CREATE ROLE report_role; GRANT SELECT ON hr.employees TO report_role; GRANT report_role TO analyst; GRANT CREATE SESSION TO analyst;
The role receives an object privilege on the hr.employees table. analyst receives the role and can query the table after the role is enabled. CREATE SESSION is a separate system privilege needed to log in. The table owner or a privileged administrator must perform these grants.
Traps
Confusing WITH ADMIN OPTION and WITH GRANT OPTION is a common error: ADMIN applies to system privileges and roles, GRANT applies to object privileges. Granting a role does not grant CREATE SESSION automatically. Object privileges do not imply the ability to create objects. Revoking a role removes privileges obtained through it, but direct grants remain. PUBLIC grants can expose objects far beyond the intended users.