User Security and Privilege Management
User Security and Privilege Management covers authentication, privileges, roles, profiles and auditing, plus the CDB/PDB distinction in 19c.
Users and authentication
CREATE USER creates a schema of the same name. Authentication may be password, OS (os_authent_prefix), external (externalb) or global (SSL/Kerberos). Administrative logins as SYSDBA, SYSOPER, SYSBACKUP, SYSDG or SYSKM use a password file created with orapwd; V$PWFILE_USERS lists who holds those privileges. SYS owns the data dictionary; SYSTEM is an ordinary DBA account.
Privileges and roles
System privileges such as CREATE TABLE or SELECT ANY TABLE apply database-wide; object privileges such as SELECT or EXECUTE attach to a named object. WITH ADMIN OPTION allows regranting a system privilege, and revoking it does not cascade. WITH GRANT OPTION on an object privilege does cascade when revoked. PUBLIC behaves like a grantee everyone holds, so grants to PUBLIC are dangerous.
Roles group privileges but are not enabled inside definer's-rights PL/SQL, so code that needs an object requires a direct grant to the owning schema. SET ROLE controls non-default roles. Profiles enforce resource limits and password policy: FAILED_LOGIN_ATTEMPTS, PASSWORD_LIFE_TIME, PASSWORD_VERIFY_FUNCTION.
Worked example
Schema APP needs to read HR.EMPLOYEES inside a stored procedure. GRANT SELECT ON hr.employees TO app_role works for ad-hoc queries but the procedure raises ORA-00942, because roles are disabled in definer's-rights code. The fix is GRANT SELECT ON hr.employees TO app directly.
Traps
Expecting a system-privilege revoke to cascade; granting to PUBLIC by accident; forgetting the password file when SYSDBA logins fail; creating a user with no quota or default tablespace; mixing unified auditing policies with the older AUDIT_TRAIL setting.