Administering User Security
Scope
This domain tests users, authentication, privileges, roles, profiles, quotas, and common versus local users in a multitenant database. Expect questions on why a connection or a CREATE TABLE fails.
Core rules
- CREATE USER creates a local user in a PDB; common users start with C_ and exist in root and PDBs. A user needs CREATE SESSION to connect and a quota on a tablespace to allocate space there.
- System privileges allow an operation; object privileges allow access to a specific object. Roles group privileges and can be default or nondefault. PUBLIC applies to every user.
- Profiles limit resources and passwords: FAILED_LOGIN_ATTEMPTS, PASSWORD_LOCK_TIME, PASSWORD_LIFE_TIME, SESSIONS_PER_USER, IDLE_TIME. A password verify function enforces complexity.
- Authentication can be password, OS, or external. Remote SYSDBA requires a password file. UNLIMITED TABLESPACE is a system privilege, not a quota.
Worked example
CREATE PROFILE app_prof LIMIT FAILED_LOGIN_ATTEMPTS 3 PASSWORD_LOCK_TIME 1; CREATE USER app_user IDENTIFIED BY ... DEFAULT TABLESPACE app TEMPORARY TABLESPACE temp QUOTA 500M ON app PROFILE app_prof; GRANT CREATE SESSION, CREATE TABLE TO app_user; The user can connect and create a table up to the quota; exceeding it raises ORA-01536.
Traps
A grant of CREATE TABLE does not grant quota. Roles are not always enabled; DEFAULT ROLE matters. PUBLIC is not a user. A password file is required for remote SYSDBA, not for ordinary users.