Managing Users, Privileges, and Profiles
This domain tests user creation and the privilege model: system versus object privileges, roles, quotas, and the profile limits that control resources and password behavior.
Creating and constraining users
CREATE USER names an authentication method (password, BY VALUES, EXTERNALLY, GLOBALLY), a default tablespace, a temporary tablespace, a profile, and optional quotas. A user with no explicit quota cannot create segments. In a multitenant database, common users are created in the root and use the common_user_prefix (C## by default), while local users exist only inside one PDB. Dropping a user who owns objects requires CASCADE, or the objects must be dropped or transferred first.
Privileges and roles
System privileges authorize actions (CREATE TABLE, SELECT ANY TABLE); object privileges authorize access to a named object (SELECT on APP.ORDERS). System privileges are granted WITH ADMIN OPTION, object privileges WITH GRANT OPTION. Revoking a system privilege does not cascade to grantees; revoking an object privilege does cascade. Roles bundle privileges, but privileges acquired through a role are not enabled inside definer's-rights PL/SQL.
Profiles
Profiles combine resource limits such as SESSIONS_PER_USER, IDLE_TIME, CONNECT_TIME, and CPU_PER_SESSION with password limits such as FAILED_LOGIN_ATTEMPTS, PASSWORD_LOCK_TIME, PASSWORD_LIFE_TIME, PASSWORD_REUSE_MAX, and PASSWORD_VERIFY_FUNCTION. Users without an explicit profile use DEFAULT.
Worked example
A user can query APP.ORDERS in SQL*Plus, but a stored procedure owned by that same user fails with ORA-00942. Reasoning: the SELECT came from a role, and roles are disabled during definer's-rights PL/SQL compilation and execution. The fix is a direct GRANT SELECT ON app.orders TO clerk, or converting the procedure to invoker's rights.
Traps that cost marks
- Assuming a role can carry an object privilege into stored PL/SQL.
- Forgetting that revoking a system privilege with ADMIN OPTION does not cascade.
- Expecting a quota to be inherited from the default tablespace; it must be granted explicitly.