# Oracle Dba User Role Access Management

> USE THIS SKILL WHENEVER the user mentions database users / accounts, schemas, roles, privileges / grants / revokes, system or object privileges, profiles, password policy or a password verify / complexity function, account lock/unlock, expired / locked-out accounts, password reset/expire, default-password accounts, access requests / access provisioning or de-provisioning, periodic access review / recertification / dormant accounts, least privilege, proxy authentication (CONNECT THROUGH), Centrally Managed Users (CMU) / LDAP / Active Directory integration, or Enterprise User Security (EUS / OID / OUD) — EVEN IF they do not name the specific task — or the errors ORA-01017 (invalid login), ORA-28000 (account locked), ORA-28001 (password expired), ORA-01918 (user not exists), ORA-01031 (insufficient privileges), ORA-01919 (role not granted). Covers user/account lifecycle, schema & role creation/assignment, system/object grants (idempotent + reversible), profiles & password verify functions, standard access-reques

- Skill: `shreyas70773/oracle-dba-user-role-access-management` (Agent Skill, multi-file: 47 files)
- Install (CLI): `npx skillmds@latest add shreyas70773/oracle-dba-user-role-access-management`
- Raw SKILL.md: https://api.skillmd.com/api/skills/shreyas70773/oracle-dba-user-role-access-management/raw
- Safety review: pending
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: Security
- Author: Shreyas70773 (https://skillmd.com/u/shreyas70773)
- Updated: 2026-09-22
- Page: https://skillmd.com/skills/shreyas70773/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

| Task | Level | Tier | Mechanism (script / sql / runbook path) | Idempotent? |
|------|-------|------|------------------------------------------|-------------|
| User account creation | L1 | T2 | `scripts/create_user.sh` / `.ps1` -> `sql/create_user.sql` | yes — existing user -> noop; created ACCOUNT LOCK |
| User password reset | L1 | T2 | `scripts/reset_password.sh` / `.ps1` -> `sql/reset_password.sql` (`--mode expire`\|`wallet-rotate`) | yes — already in target state -> noop; no plaintext |
| Account lock / unlock | L1 | T2 | `scripts/lock_unlock_account.sh` / `.ps1` -> `sql/lock_unlock_account.sql` | yes — already locked/unlocked -> noop; refuses system accts |
| Expired account handling (report) | L1 | T1 | `scripts/expired_accounts.sh report` / `.ps1 report` -> `sql/expired_accounts_report.sql` | yes — read-only |
| Expired account handling (remediate) | L1 | T2 | `scripts/expired_accounts.sh remediate` / `.ps1 remediate` -> `sql/expired_account_remediate.sql` (`--strategy reexpire`\|`extend`) | yes — already remediated -> noop; refuses system accts |
| User account drop / deletion | L1 | **T3** | `references/runbooks.md#drop-user` (deprovision = LOCK+EXPIRE via the T2 scripts first) | no — `DROP USER CASCADE` is irreversible |
| Standard access request fulfillment | L1 | T2 | `scripts/grant_access.sh` / `.ps1` -> `sql/grant_access.sql` (`--operation grant`\|`revoke`) | yes — granted/absent -> noop; high-risk refused -> runbook |
| Schema creation | L2 | T2 | `scripts/create_schema.sh` / `.ps1` -> `sql/create_schema.sql` | yes — existing schema -> creation noop; baseline re-asserted |
| Role creation & assignment | L2 | T2 | `scripts/create_role.sh` / `.ps1` -> `sql/create_role.sql` | yes — existing role -> noop; re-grant of held privs no-ops |
| System / object privilege grants | L2 | T2 | `scripts/grant_privilege.sh grant`/`revoke` / `.ps1` -> `sql/grant_privilege.sql` + `sql/revoke_privilege.sql` | yes — held grant / absent revoke -> noop |
| System / object privilege review | L2 | T1 | `scripts/grant_privilege.sh review` / `.ps1 review` -> `sql/privilege_review.sql` | yes — read-only |
| Profile creation & management (apply) | L2 | T2 | `scripts/profile_manage.sh apply` / `.ps1 apply` -> `sql/profile_apply.sql` | yes — limits matching -> skipped; all-match -> noop |
| Profile inventory (list) | L2 | T1 | `scripts/profile_manage.sh list` / `.ps1 list` -> `sql/profile_list.sql` | yes — read-only |
| Password policy / verify function enforcement | L2 | T2 | `scripts/password_policy.sh` / `.ps1` -> `sql/password_verify_function.sql` (attach via `profile_manage`) | yes — CREATE OR REPLACE refreshes body |
| Periodic access review / recertification | L1 | T1 | `scripts/access_review.sh` / `.ps1` -> `sql/access_review.sql` | yes — read-only (emits KEYVAL metrics) |
| Proxy authentication — review (pre-flight/verify) | L3 | T1 | `scripts/directory_integration_review.sh proxy` / `.ps1 proxy` -> `sql/proxy_auth_review.sql` | yes — read-only |
| Proxy authentication — setup | L3 | **T3** | `references/runbooks.md#proxy-auth` | reverse via `REVOKE CONNECT THROUGH` |
| CMU / LDAP / AD — review (pre-flight/verify) | L3 | T1 | `scripts/directory_integration_review.sh cmu` / `.ps1 cmu` -> `sql/centrally_managed_users_review.sql` | yes — read-only |
| CMU / LDAP / AD integration — setup | L3 | **T3** | `references/runbooks.md#cmu` | reverse: drop global users/roles, `LDAP_DIRECTORY_ACCESS=NONE`, remove wallet/ldap.ora |
| EUS — review (pre-flight/verify) | L3 | T1 | `scripts/directory_integration_review.sh eus` / `.ps1 eus` -> `sql/enterprise_user_security_review.sql` | yes — read-only |
| Enterprise User Security (EUS) — setup | L3 | **T3** | `references/runbooks.md#eus` | reverse: unregister directory, drop global schema/roles |
| Privileged (DBA-class) grant (escape hatch) | L2/L3 | **T3** | `references/runbooks.md#privileged-grant` | reverse via `REVOKE` / password-file removal |

## 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).