Users, Privileges, Roles and Profiles

40.1 Users

Proxy authentication: ALTER USER app_schema GRANT CONNECT THROUGH dev_user;

CREATE USER app_user IDENTIFIED BY "Str0ng#Pwd"
  DEFAULT TABLESPACE app_data TEMPORARY TABLESPACE temp
  QUOTA 500M ON app_data PROFILE app_profile PASSWORD EXPIRE;
ALTER USER app_user IDENTIFIED BY "N3w#Pwd";
ALTER USER app_user ACCOUNT LOCK;   ALTER USER app_user ACCOUNT UNLOCK;
CREATE USER ext_user IDENTIFIED EXTERNALLY;                     -- OS authentication
CREATE USER glob_user IDENTIFIED GLOBALLY AS 'CN=...';          -- directory / Entra ID / IAM
CREATE USER app_schema NO AUTHENTICATION;                       -- schema-only account (18c)
DROP USER app_user CASCADE;
SELECT username, account_status, default_tablespace, profile, authentication_type FROM dba_users;

40.2 Privileges

WITH ADMIN OPTION (system privileges: revoking doesn't cascade) vs WITH GRANT OPTION (object privileges: revoking cascades).

Views: DBA_SYS_PRIVS, DBA_TAB_PRIVS, DBA_ROLE_PRIVS, SESSION_PRIVS, SESSION_ROLES, ROLE_TAB_PRIVS.

-- System privileges
GRANT CREATE SESSION, CREATE TABLE, CREATE VIEW, CREATE PROCEDURE, CREATE SEQUENCE TO app_user;
GRANT SELECT ANY TABLE TO auditor;                -- powerful: avoid when possible
GRANT CREATE SESSION TO app_user WITH ADMIN OPTION;

-- Object privileges
GRANT SELECT, INSERT, UPDATE ON hr.employees TO app_user;
GRANT UPDATE (salary) ON hr.employees TO payroll;  -- column level
GRANT EXECUTE ON hr.order_pkg TO app_user WITH GRANT OPTION;
GRANT READ ON hr.employees TO reporter;            -- READ: no SELECT ... FOR UPDATE (12c)

-- Schema privileges (23ai)
GRANT SELECT ANY TABLE ON SCHEMA hr TO analyst;

REVOKE INSERT ON hr.employees FROM app_user;

40.3 Roles

Predefined roles: CONNECT, RESOURCE, DBA, SELECT_CATALOG_ROLE, EXP_FULL_DATABASE, DATAPUMP_EXP_FULL_DATABASE, AUDIT_ADMIN, and DB_DEVELOPER_ROLE (23ai, recommended for developers).

Roles aren't active inside definer's-rights PL/SQL. Grant directly or use code-based access control.

Privilege Analysis (DBMS_PRIVILEGE_CAPTURE) finds unused privileges.

CREATE ROLE app_read;
GRANT SELECT ON hr.employees TO app_read;
GRANT app_read TO app_user;
ALTER USER app_user DEFAULT ROLE ALL EXCEPT app_admin;
SET ROLE app_admin IDENTIFIED BY "rolepwd";
CREATE ROLE secure_role IDENTIFIED USING sec_pkg;     -- secure application role

40.4 Profiles

Resource limits need RESOURCE_LIMIT = TRUE. Prefer Resource Manager for CPU and I/O control.

Gradual password rollover (PASSWORD_ROLLOVER_TIME, 21c / 19.12).

CREATE PROFILE app_profile LIMIT
  FAILED_LOGIN_ATTEMPTS 5  PASSWORD_LOCK_TIME 1/24
  PASSWORD_LIFE_TIME 90    PASSWORD_GRACE_TIME 7
  PASSWORD_REUSE_TIME 365  PASSWORD_REUSE_MAX 10
  PASSWORD_VERIFY_FUNCTION ora12c_strong_verify_function
  INACTIVE_ACCOUNT_TIME 60
  SESSIONS_PER_USER 10  IDLE_TIME 30  CONNECT_TIME UNLIMITED;
ALTER USER app_user PROFILE app_profile;