Oracle Dba User Role Access Management

Oracle DBA - User, Role & Access Management

User & account lifecycle, schemas, roles, privileges, profiles, password policy, access reviews, and the plan-only Tier-3 runbooks for proxy authentication, CMU / LDAP / AD, Enterprise User Security, user drop, and privileged grants — for Oracle 19c and 23ai (EE; single-instance and Data Guard; on-prem). Everything self-executing is T1 (observe, read-only) or T2 (reversible, dry-run-default). Directory-integration setup, user drop, and DBA-class grants are T3 and live only in references/runbooks.md — they print exact commands for a human and never self-execute.

Secrets rule (load-bearing): no plaintext password is ever set, passed, or logged. Accounts are created wallet / external / global identified; resets use EXPIRE or wallet-rotate (rotate the secret in the Oracle Wallet out of band). Plaintext-credential patterns are rejected with ERR_SECRET (8).

Scope & risk map

Preconditions

• SQLcl MCP connection dba_ai_conn is reachable and wallet-backed. All in-DB prechecks, postchecks, idempotency probes, and sql/*.sql run through it via connect_db / Connect-Db (mechanism sqlcl-mcp). Never SYS/SYSTEM — except inside a T3 runbook that explicitly says AS SYSDBA (e.g. setting LDAP_DIRECTORY_ACCESS, directory registration), supplied interactively by the operator.
• dba_ai_conn least privilege: it must legitimately hold the authority it delegates — CREATE USER/ALTER USER/CREATE ROLE/CREATE PROFILE, and any privilege it grants must be held WITH ADMIN OPTION (or it must be the object's grantor). DBA-class grants are refused by the T2 scripts and routed to references/runbooks.md#privileged-grant.
• Secrets resolve from the Oracle Wallet / external password store. No plaintext anywhere. Accounts are wallet/external/global identified; password "reset" = EXPIRE or wallet rotation (out of band). Any identified by / password= / user/pass@db pattern in args is rejected with ERR_SECRET (8).
• Helpers are sourced from _common (scripts/lib.sh / lib.ps1): connect_db, log_event, emit_metric, require_dry_run, guard_blocked_dry_run, require_approval_token, precheck, postcheck, print_banner. Not reinvented.
• Logging: structured key=value lines to the canonical paths /var/log/oracle-dba/user-role-access-management/user-role-access-management.log (Linux) / C:/ProgramData/oracle-dba/logs/user-role-access-management/user-role-access-management.log (Windows).
• T2 dry-run default: every T2 script previews and changes nothing unless given --execute. Multitenant: set the correct container (ALTER SESSION SET CONTAINER) via dba_ai_conn; users/roles/profiles/proxies are container-local (LOCAL, not common). Data Guard: run all DDL/GRANT on the PRIMARY (it replicates via redo); host-local sqlnet.ora/ldap.ora/wallets must be replicated to each standby host by hand.

Procedures

Scripts ship as behaviour-equivalent .sh (Linux) / .ps1 (Windows) pairs. T2 scripts default to --dry-run; pass --execute to act. See each script's --help. Every action follows: trigger -> precheck (read-only guard) -> action -> postcheck (verify) -> rollback.

User account creation (L1, T2)

• Trigger: provision a new account; account-request intake.
• Precheck: default/temp tablespaces exist (dba_tablespaces).
• Action: scripts/create_user.sh --user U --default-ts USERS --temp-ts TEMP [--auth wallet|external|global] [--ext-name DN] --execute / .ps1.
• Postcheck: user present in dba_users (created ACCOUNT LOCK).
• Rollback: removal is T3 references/runbooks.md#drop-user; creation is the reversible direction.

User password reset (L1, T2)

• Trigger: user locked out by expiry / needs a fresh credential.
• Precheck: user exists.
• Action: scripts/reset_password.sh --user U [--mode expire|wallet-rotate] --execute / .ps1. No plaintext — EXPIRE forces self-service set; wallet-rotate unlocks for a wallet rotation done out of band.
• Postcheck: EXPIRE -> EXPIRED; wallet-rotate -> not LOCKED.
• Rollback: user sets a new password (EXPIRE), or rotate the wallet back.

Account lock / unlock (L1, T2)

• Trigger: suspend (lock) or restore (unlock) access.
• Precheck: user exists; SQL refuses Oracle-maintained/system accounts.
• Action: scripts/lock_unlock_account.sh --user U --action lock|unlock --execute / .ps1.
• Postcheck: account_status reflects the requested lock state.
• Rollback: exact inverse (unlock <-> lock).

Expired account handling (L1, T1 report / T2 remediate)

• Trigger: daily expiry sweep (report); remediate a specific expired account.
• Precheck (remediate): user exists; refuses system accounts.
• Action: scripts/expired_accounts.sh report [--days 14] (read-only) or remediate --user U [--strategy reexpire|extend --profile P] --execute / .ps1.
• Postcheck: reexpire -> EXPIRED; extend -> on new profile, not locked.
• Rollback: re-apply prior profile (extend) or let the user self-service (reexpire).

User account drop / deletion (L1, T3)

• Trigger: decommission an account. Default deprovisioning is LOCK + EXPIRE (T2, reversible) for a grace period; only then drop.
• Action: print references/runbooks.md#drop-user, complete pre-flight (export first!), supply approval token; a human runs DROP USER ... CASCADE.
• Rollback: none for CASCADE — recover via Data Pump import / Backup & Recovery runbooks. Never auto-executed.

Standard access request fulfillment (L1, T2)

• Trigger: grant/revoke a PRE-APPROVED catalogued role or low-risk system privilege.
• Precheck: grantee exists (user or role).
• Action: scripts/grant_access.sh --grantee G --type role|syspriv --name N [--operation grant|revoke] [--with-admin] --execute / .ps1. High-risk items refused -> #privileged-grant.
• Postcheck: grant present after GRANT / absent after REVOKE.
• Rollback: exact inverse (--operation revoke).

Schema creation (L2, T2)

• Trigger: new application owner schema.
• Precheck: default/temp tablespaces exist.
• Action: scripts/create_schema.sh --schema S --default-ts APPDATA --temp-ts TEMP [--quota UNLIMITED] --execute / .ps1 (wallet/external/global auth; object-create baseline; ACCOUNT LOCK).
• Postcheck: schema owner present in dba_users.
• Rollback: T3 #drop-user.

Role creation & assignment (L2, T2)

• Trigger: define/assemble a standard access bundle and assign it.
• Precheck: list args are identifier-safe; nested roles/grantees must exist (SQL checks).
• Action: scripts/create_role.sh --role R [--sys-privs '...'] [--role-privs '...'] [--assign-to '...'] --execute / .ps1. DBA-class members refused -> #privileged-grant.
• Postcheck: role present in dba_roles.
• Rollback: DROP ROLE / REVOKE (re-run with prior membership to converge).

System / object privilege grants (L2, T1 review / T2 grant/revoke)

• Trigger: delegate or remove a specific SYS/OBJ/ROLE privilege; audit current grants.
• Precheck: grantee exists; dba_ai_conn holds the privilege WITH ADMIN OPTION/grantor.
• Action: scripts/grant_privilege.sh review [--grantee G] (read-only) or grant|revoke --kind sys|obj|role --name N [--object OWNER.OBJ] [--admin] [--grant-option] --execute / .ps1.
• Postcheck: state in dba_sys_privs/dba_role_privs/dba_tab_privs.
• Rollback: revoke is the exact inverse of grant.

Profile creation & management (L2, T1 list / T2 apply)

• Trigger: enforce password/resource limits; audit profiles.
• Precheck: profile name valid (apply); none for list (read-only).
• Action: scripts/profile_manage.sh list [--profile P] or apply --profile P [--failed-login-attempts N --password-life-time D --password-verify-func FN ...] --execute / .ps1.
• Postcheck: profile present; limits reflect desired values.
• Rollback: re-apply prior limit values.

Password policy / verify function enforcement (L2, T2)

• Trigger: install/refresh a password complexity verify function.
• Precheck: connecting schema is a privileged security schema (out of band, not SYS).
• Action: scripts/password_policy.sh [--func ODB_VERIFY_PASSWORD] [--min-length 12] --execute / .ps1, then attach via profile_manage.sh --password-verify-func.
• Postcheck: function compiled VALID.
• Rollback: detach (profile PASSWORD_VERIFY_FUNCTION -> NULL/DEFAULT); DROP FUNCTION is the T3 escalation.

Periodic access review / recertification (L1, T1)

• Trigger: quarterly recertification; dormant-account / privileged-access audit.
• Precheck: none (read-only). On Data Guard run against the PRIMARY (LAST_LOGIN is primary-side).
• Action: scripts/access_review.sh [--dormant-days 90] / .ps1 -> evidence pack + KEYVAL metrics.
• Postcheck: n/a — read-only; findings feed recertification.
• Rollback: none.

Proxy auth / CMU / EUS — review (L3 task family, T1 read-only)

• Trigger: pre-flight discovery before a T3 directory-integration change, or post-change verification.
• Action: scripts/directory_integration_review.sh proxy|cmu|eus|all / .ps1 -> the matching sql/*_review.sql.
• Postcheck/Rollback: n/a — read-only. The setup of each is T3 (below).

Tier-3 runbooks

Directory-integration setup, user drop, and DBA-class privileged grants are Tier 3 and live in references/runbooks.md: #proxy-auth, #cmu, #eus, #drop-user, #privileged-grant.

These runbooks PRINT exact SQL/OS commands for a human operator and NEVER self-execute. They are gated by require_approval_token / Require-ApprovalToken: an automated agent's only sanctioned action is to print the relevant section and stop with ERR_APPROVAL (6) until a human supplies a real change ticket/token (--token <TICKET> or ODB_APPROVAL_TOKEN; placeholders rejected). The T2 scripts deliberately refuse DBA-class grants (DBA, ALL PRIVILEGES, GRANT ANY *, SYSDBA/SYSOPER/..., BECOME USER, ALTER SYSTEM/DATABASE, CREATE/ALTER/DROP USER) and point to #privileged-grant. SYSDBA is a T3-only, interactively-supplied exception. When in doubt, runbook.

See references/version-notes.md for 19c-vs-23ai behavioural deltas, and cron/crontab.d / cron/task-scheduler.xml for the schedulable T1/T2 jobs (read-only reviews + expiry sweep; no T3 is ever scheduled).