OracleSecurity & users

CMU step 1: prepare Active Directory

Done once by an AD administrator before any database work: create the service account the database binds with, install Oracle's password filter on every domain controller, grant the account its three permissions, and enable users for password logins. The schema extension is permanent. Users must change their AD password afterwards, or their database login fails.

Changes data19cRun on domain controller

Not yet verified. How scripts are tested

This changes Active Directory. Check the names and values before you run it.

ora-cmu-ad-prep.ps1

1# On a Windows domain controller as a domain admin, in PowerShell.2# Only needed for password logins. Kerberos and certificate logins skip the filter.3 4# 1. Service account the database binds to AD with. Note its DN for step 3.5New-ADUser -Name oracle_cmu -SamAccountName oracle_cmu -Path "OU=Service Accounts,DC=corp,DC=example,DC=com" -AccountPassword (Read-Host -AsSecureString "Password") -PasswordNeverExpires $true -Enabled $true6Get-ADUser oracle_cmu | Select-Object DistinguishedName7 8# 2. Oracle password filter, on EVERY domain controller in the domain.9#    opwdintg.exe ships in $ORACLE_HOME/bin, but take the latest from My Oracle10#    Support Doc ID 2462012.1. Copy it to C:\temp on each DC and run it there.11#    Windows must be set to English.12cd C:\temp13.\opwdintg.exe14#    Answer Yes to: extend AD schema / continue / install Oracle password filter / reboot.15#    The schema extension can't be undone. It creates the groups ORA_VFR_MD5,16#    ORA_VFR_11G and ORA_VFR_12C.17 18# 3. Permissions for oracle_cmu. Oracle lists these with the account, but the last19#    one needs orclCommonAttribute, which only exists after the filter install.20#    On the OU holding the database users, applying to descendant User objects:21#      Read properties22#      Write lockoutTime23#      Control access on orclCommonAttribute24#    Then deny everyone else access to orclCommonAttribute: it holds the25#    Oracle password verifiers.26 27# 4. Enable users for password logins: add them to the 12c verifier group...28Add-ADGroupMember -Identity ORA_VFR_12C -Members jsmith,akumar29#    ...then each of them must CHANGE their AD password. The verifier is only30#    written on a password change. Until then their database login fails (ORA-28274).31#    ORA_VFR_12C covers 12c, 18c and 19c. Add ORA_VFR_11G only for 11g or 12.1.0.132#    clients, and ORA_VFR_MD5 only for WebDAV.33 34# 5. When someone leaves: remove them from the ORA_VFR groups and reset their35#    password (or clear orclCommonAttribute) so their Oracle verifier is removed.36Remove-ADGroupMember -Identity ORA_VFR_12C -Members jsmith

# On a Windows domain controller as a domain admin, in PowerShell.
# Only needed for password logins. Kerberos and certificate logins skip the filter.

# 1. Service account the database binds to AD with. Note its DN for step 3.
New-ADUser -Name oracle_cmu -SamAccountName oracle_cmu -Path "OU=Service Accounts,DC=corp,DC=example,DC=com" -AccountPassword (Read-Host -AsSecureString "Password") -PasswordNeverExpires $true -Enabled $true
Get-ADUser oracle_cmu | Select-Object DistinguishedName

# 2. Oracle password filter, on EVERY domain controller in the domain.
#    opwdintg.exe ships in $ORACLE_HOME/bin, but take the latest from My Oracle
#    Support Doc ID 2462012.1. Copy it to C:\temp on each DC and run it there.
#    Windows must be set to English.
cd C:\temp
.\opwdintg.exe
#    Answer Yes to: extend AD schema / continue / install Oracle password filter / reboot.
#    The schema extension can't be undone. It creates the groups ORA_VFR_MD5,
#    ORA_VFR_11G and ORA_VFR_12C.

# 3. Permissions for oracle_cmu. Oracle lists these with the account, but the last
#    one needs orclCommonAttribute, which only exists after the filter install.
#    On the OU holding the database users, applying to descendant User objects:
#      Read properties
#      Write lockoutTime
#      Control access on orclCommonAttribute
#    Then deny everyone else access to orclCommonAttribute: it holds the
#    Oracle password verifiers.

# 4. Enable users for password logins: add them to the 12c verifier group...
Add-ADGroupMember -Identity ORA_VFR_12C -Members jsmith,akumar
#    ...then each of them must CHANGE their AD password. The verifier is only
#    written on a password change. Until then their database login fails (ORA-28274).
#    ORA_VFR_12C covers 12c, 18c and 19c. Add ORA_VFR_11G only for 11g or 12.1.0.1
#    clients, and ORA_VFR_MD5 only for WebDAV.

# 5. When someone leaves: remove them from the ORA_VFR groups and reset their
#    password (or clear orclCommonAttribute) so their Oracle verifier is removed.
Remove-ADGroupMember -Identity ORA_VFR_12C -Members jsmith

Run these on a Windows domain controller.

Open in denrepo

Helps with

ORA-28274: No ORACLE password attribute corresponding to user nickname exists

Part of these runbooks

Set up CMU with Active Directory

More Oracle scripts: Security & users

Accounts locked, expired or expiring soonApplication accounts that aren't OPEN, or whose passwords expire in the next 14 days. Catch service accounts before they break an application.
Who has DBA, SYSDBA and powerful system privilegesNon-Oracle accounts and roles holding the DBA role or high-risk ANY privileges, then everyone in the password file. Worth reviewing every audit cycle.
CMU step 2: export the AD root certificateThe database talks to AD over LDAPS, so its wallet must trust the certificate authority that issued the domain controllers' certificates. Export that…
CMU step 3: create the walletBuilds the auto-login wallet the database reads at login: the service account's user name, DN and password, plus the AD root certificate. With PDBs,…
CMU step 4: create dsi.oraTells the database which domain controllers to use. Put it in the same folder as the wallet from step 3. Use fully qualified host names, and list at…
CMU step 5: point each PDB at its wallet (CMU_WALLET)Creates a directory object for the wallet folder from step 3 and sets the CMU_WALLET database property in the PDB, so CMU reads that PDB's wallet and…