Set up CMU with Active Directory

1-- denrepo runbook: Set up CMU with Active Directory2-- Centrally managed users on your own Oracle 19c server, so people log in with their Active Directory password and get access through AD groups. Each PDB gets its own wallet through the CMU_WALLET property. Steps 1 and 2 run on a domain controller, 3 and 4 at the OS prompt on the database server, 5 to 9 in SQL*Plus in each PDB.3-- Source: https://denrepo.com/runbooks/cmu/4 5SET ECHO OFF6 7-- Not yet verified8PROMPT9PROMPT ==== 1 of 9: CMU step 1: prepare Active Directory ====10PROMPT (run on domain controller)11PROMPT Not run from this file: it isn't SQL. Open step 1 on denrepo and run it by hand.12-- # On a Windows domain controller as a domain admin, in PowerShell.13-- # Only needed for password logins. Kerberos and certificate logins skip the filter.14-- 15-- # 1. Service account the database binds to AD with. Note its DN for step 3.16-- 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 $true17-- Get-ADUser oracle_cmu | Select-Object DistinguishedName18-- 19-- # 2. Oracle password filter, on EVERY domain controller in the domain.20-- #    opwdintg.exe ships in $ORACLE_HOME/bin, but take the latest from My Oracle21-- #    Support Doc ID 2462012.1. Copy it to C:\temp on each DC and run it there.22-- #    Windows must be set to English.23-- cd C:\temp24-- .\opwdintg.exe25-- #    Answer Yes to: extend AD schema / continue / install Oracle password filter / reboot.26-- #    The schema extension can't be undone. It creates the groups ORA_VFR_MD5,27-- #    ORA_VFR_11G and ORA_VFR_12C.28-- 29-- # 3. Permissions for oracle_cmu. Oracle lists these with the account, but the last30-- #    one needs orclCommonAttribute, which only exists after the filter install.31-- #    On the OU holding the database users, applying to descendant User objects:32-- #      Read properties33-- #      Write lockoutTime34-- #      Control access on orclCommonAttribute35-- #    Then deny everyone else access to orclCommonAttribute: it holds the36-- #    Oracle password verifiers.37-- 38-- # 4. Enable users for password logins: add them to the 12c verifier group...39-- Add-ADGroupMember -Identity ORA_VFR_12C -Members jsmith,akumar40-- #    ...then each of them must CHANGE their AD password. The verifier is only41-- #    written on a password change. Until then their database login fails (ORA-28274).42-- #    ORA_VFR_12C covers 12c, 18c and 19c. Add ORA_VFR_11G only for 11g or 12.1.0.143-- #    clients, and ORA_VFR_MD5 only for WebDAV.44-- 45-- # 5. When someone leaves: remove them from the ORA_VFR groups and reset their46-- #    password (or clear orclCommonAttribute) so their Oracle verifier is removed.47-- Remove-ADGroupMember -Identity ORA_VFR_12C -Members jsmith48 49-- Not yet verified50PROMPT51PROMPT ==== 2 of 9: CMU step 2: export the AD root certificate ====52PROMPT (run on domain controller)53PROMPT Not run from this file: it isn't SQL. Open step 2 on denrepo and run it by hand.54-- # On a Windows machine that can reach the enterprise CA (often a DC), in PowerShell55-- # or a command prompt. If your PKI team runs the CA, ask them for the root CA56-- # certificate in Base-64 (.cer) instead.57-- 58-- # 1. Export the CA certificate (binary DER)59-- certutil -ca.cert C:\temp\ad_root_ca.cer60-- 61-- # 2. Convert it to Base-64 for orapki62-- certutil -encode C:\temp\ad_root_ca.cer C:\temp\ad_root_ca.txt63-- 64-- # 3. Check it's the right one: the Subject should be your root CA's name65-- certutil -dump C:\temp\ad_root_ca.txt66-- 67-- # 4. Copy ad_root_ca.txt to the database server, for example to /tmp (sftp or WinSCP).68 69-- Not yet verified70PROMPT71PROMPT ==== 3 of 9: CMU step 3: create the wallet ====72PROMPT (run on os prompt)73PROMPT Not run from this file: it isn't SQL. Open step 3 on denrepo and run it by hand.74-- # On the database server as the oracle software owner.75-- 76-- # 1. Pick the wallet folder. With PDBs, use one folder per PDB (or one shared by77-- #    PDBs on the same AD) and point the PDB at it with CMU_WALLET in step 5.78-- #    On 19c, CMU_WALLET needs the CMU patch 31404487. Check it's applied:79-- $ORACLE_HOME/OPatch/opatch lspatches | grep 3140448780-- #    If it isn't listed, check My Oracle Support Doc ID 2462012.1 for your RU.81-- 82-- WALLET=/u01/app/oracle/cmu/<pdb_name>/wallet83-- 84-- #    Without CMU_WALLET, the database uses sqlnet.ora's WALLET_LOCATION85-- #    (plus /<pdb_guid> for a PDB) or, if that isn't set, the default:86-- #      PDB:              $ORACLE_BASE/admin/<db_unique_name>/<pdb_guid>/wallet87-- #      non-CDB or root:  $ORACLE_BASE/admin/<db_unique_name>/wallet88-- #    (SELECT pdb_name, guid FROM dba_pdbs; from the root gives the GUID.)89-- 90-- mkdir -p $WALLET91-- 92-- # 2. Auto-login wallet. Prompts for a new wallet password: keep it in your vault.93-- orapki wallet create -wallet $WALLET -auto_login94-- 95-- # 3. The service account from step 1. Each command asks for the wallet password.96-- mkstore -wrl $WALLET -createEntry ORACLE.SECURITY.USERNAME oracle_cmu97-- mkstore -wrl $WALLET -createEntry ORACLE.SECURITY.DN "CN=oracle_cmu,OU=Service Accounts,DC=corp,DC=example,DC=com"98-- #    No value on the next line: mkstore prompts for the service account password,99-- #    which keeps it out of your shell history.100-- mkstore -wrl $WALLET -createEntry ORACLE.SECURITY.PASSWORD101-- 102-- # 4. Trust the AD root certificate from step 2 (add an intermediate CA the same way).103-- orapki wallet add -wallet $WALLET -cert /tmp/ad_root_ca.txt -trusted_cert104-- chmod 600 $WALLET/*105-- 106-- # 5. Check: three ORACLE.SECURITY entries, and your CA under Trusted Certificates.107-- orapki wallet display -wallet $WALLET108-- 109-- # 6. Check this server trusts the DC's LDAPS certificate.110-- #    Expect: Verify return code: 0 (ok)111-- openssl s_client -connect <dc1.corp.example.com>:636 -CAfile /tmp/ad_root_ca.txt </dev/null 2>/dev/null | grep "Verify return code"112 113-- Not yet verified114PROMPT115PROMPT ==== 4 of 9: CMU step 4: create dsi.ora ====116PROMPT (run on os prompt)117PROMPT Not run from this file: it isn't SQL. Open step 4 on denrepo and run it by hand.118-- # On the database server as the oracle software owner, in the wallet folder119-- # from step 3.120-- cd /u01/app/oracle/cmu/<pdb_name>/wallet121-- 122-- cat > dsi.ora <<'EOF'123-- DSI_DIRECTORY_SERVERS = (dc1.corp.example.com:389:636, dc2.corp.example.com:389:636)124-- DSI_DIRECTORY_SERVER_TYPE = AD125-- EOF126-- 127-- # Optional, and Oracle recommends leaving it out: limit the search to one OU.128-- # DSI_DEFAULT_ADMIN_CONTEXT = "OU=Oracle,DC=corp,DC=example,DC=com"129-- 130-- cat dsi.ora131-- ls -l    # cwallet.sso, ewallet.p12 and dsi.ora side by side132 133-- Not yet verified134PROMPT135PROMPT ==== 5 of 9: CMU step 5: point each PDB at its wallet (CMU_WALLET) ====136PROMPT Not run from this file: it changes the database. Open step 5 on denrepo and run it by hand.137-- -- In each PDB that uses CMU (not the root):138-- ALTER SESSION SET CONTAINER = <pdb_name>;139-- 140-- -- The folder from step 3. PDBs on the same AD can point at one shared folder.141-- CREATE OR REPLACE DIRECTORY cmu_wallet_dir AS '/u01/app/oracle/cmu/<pdb_name>/wallet';142-- 143-- -- The property takes the directory object's name in upper case.144-- ALTER DATABASE PROPERTY SET CMU_WALLET = 'CMU_WALLET_DIR';145-- 146-- -- If the PDB was created with PATH_PREFIX, use a path relative to it instead:147-- -- CREATE OR REPLACE DIRECTORY cmu_wallet_dir AS 'cmu/wallet';148-- 149-- -- Check: the property and the folder it points at150-- SELECT p.property_value AS cmu_wallet, d.directory_path151-- FROM database_properties p152-- LEFT JOIN dba_directories d ON d.directory_name = p.property_value153-- WHERE p.property_name = 'CMU_WALLET';154 155-- Not yet verified156PROMPT157PROMPT ==== 6 of 9: CMU step 6: turn on directory access ====158PROMPT Not run from this file: it changes the database. Open step 6 on denrepo and run it by hand.159-- -- In a CDB, run this in the PDB, not the root:160-- -- ALTER SESSION SET CONTAINER = <pdb_name>;161-- SHOW CON_NAME162-- SHOW PARAMETER ldap_directory163-- 164-- -- Reads the wallet and dsi.ora now. Run it again any time you change either.165-- ALTER SYSTEM SET LDAP_DIRECTORY_ACCESS = 'PASSWORD' SCOPE = BOTH;166-- 167-- -- Optional: AD users who connect AS SYSDBA, SYSOPER, SYSBACKUP and so on.168-- -- With CMU_WALLET (step 5) these logins only work while the database is open,169-- -- so keep a local SYSDBA login for startup and recovery.170-- --171-- -- a) Password file in 12.2 format. At the OS prompt (path from srvctl config172-- --    database on RAC); new 19c databases usually have it already:173-- --      orapwd describe file=$ORACLE_HOME/dbs/orapw<ORACLE_SID>174-- --    If it's older, migrate it, keeping existing entries:175-- --      orapwd file=$ORACLE_HOME/dbs/orapw<ORACLE_SID>.new input_file=$ORACLE_HOME/dbs/orapw<ORACLE_SID> format=12.2176-- --177-- -- b) In the PDB:178-- -- ALTER SYSTEM SET LDAP_DIRECTORY_SYSAUTH = YES SCOPE = SPFILE;179-- --180-- -- c) In the CDB root (usually already EXCLUSIVE; check with SHOW PARAMETER):181-- -- ALTER SYSTEM SET REMOTE_LOGIN_PASSWORDFILE = EXCLUSIVE SCOPE = SPFILE;182-- --183-- -- d) Restart the instance (srvctl on RAC). Grant the admin privilege itself in step 7.184-- 185-- SHOW PARAMETER ldap_directory186 187-- Not yet verified188PROMPT189PROMPT ==== 7 of 9: CMU step 7: map AD users and groups ====190PROMPT Not run from this file: it changes the database. Open step 7 on denrepo and run it by hand.191-- -- In the PDB from step 6. Replace each DN with the real one from AD.192-- 193-- -- Shared schema: everyone in this AD group logs in to one database user.194-- -- Keep its privileges minimal. Put an AD user in only one group mapped to a schema.195-- CREATE USER ad_app_users IDENTIFIED GLOBALLY AS 'CN=Oracle App Users,OU=Groups,DC=corp,DC=example,DC=com';196-- GRANT CREATE SESSION TO ad_app_users;197-- 198-- -- Global role: members of this AD group get the role's privileges when they log in.199-- -- A session can have at most 150 enabled roles.200-- CREATE ROLE ad_app_read IDENTIFIED GLOBALLY AS 'CN=Oracle App Readers,OU=Groups,DC=corp,DC=example,DC=com';201-- GRANT SELECT ON <app_schema>.<table_name> TO ad_app_read;202-- 203-- -- Admin privileges can't be granted to roles: use a shared schema per privilege.204-- -- Needs the admin setup in step 6 first.205-- -- CREATE USER ad_backup_admins IDENTIFIED GLOBALLY AS 'CN=Oracle Backup Admins,OU=Groups,DC=corp,DC=example,DC=com';206-- -- GRANT SYSBACKUP TO ad_backup_admins;207-- 208-- -- Exclusive schema: one AD user gets their own database user. More upkeep,209-- -- since joiners and leavers then need changes in every database.210-- -- CREATE USER jsmith IDENTIFIED GLOBALLY AS 'CN=John Smith,OU=Users,DC=corp,DC=example,DC=com';211-- -- GRANT CREATE SESSION TO jsmith;212 213-- Not yet verified214PROMPT215PROMPT ==== 8 of 9: CMU step 8: check the setup and a login ====216CLEAR COLUMNS217SET LINESIZE 200 PAGESIZE 100 TRIMOUT ON TAB OFF218COLUMN name           FORMAT A26219COLUMN value          FORMAT A12220COLUMN username       FORMAT A25221COLUMN external_name  HEADING "MAPPED TO" FORMAT A70 WORD_WRAPPED222COLUMN account_status HEADING "STATUS" FORMAT A16223COLUMN role           FORMAT A30224COLUMN schema_user    HEADING "SCHEMA" FORMAT A20225COLUMN ad_login       HEADING "AD LOGIN" FORMAT A25226COLUMN ad_dn          HEADING "AD DN" FORMAT A50 WORD_WRAPPED227COLUMN method         HEADING "METHOD" FORMAT A16228COLUMN id_type        HEADING "MAPPING" FORMAT A16229COLUMN ldap_type      HEADING "LDAP" FORMAT A4230 231PROMPT LDAP parameters in this container232SELECT name, value233FROM v$parameter234WHERE name LIKE 'ldap_directory%'235ORDER BY name;236 237PROMPT Wallet location from CMU_WALLET (no rows: default or sqlnet.ora location)238SELECT p.property_value AS name, d.directory_path AS external_name239FROM database_properties p240LEFT JOIN dba_directories d ON d.directory_name = p.property_value241WHERE p.property_name = 'CMU_WALLET';242 243PROMPT Database users mapped to AD244SELECT username, external_name, account_status245FROM dba_users246WHERE authentication_type = 'GLOBAL'247ORDER BY username;248 249PROMPT Roles mapped to AD groups250SELECT role251FROM dba_roles252WHERE authentication_type = 'GLOBAL'253ORDER BY role;254 255PROMPT Who this session is. As an AD user expect PASSWORD_GLOBAL, GLOBAL SHARED or GLOBAL EXCLUSIVE, and AD256SELECT SYS_CONTEXT('USERENV', 'SESSION_USER')           AS schema_user,257       SYS_CONTEXT('USERENV', 'AUTHENTICATED_IDENTITY') AS ad_login,258       SYS_CONTEXT('USERENV', 'ENTERPRISE_IDENTITY')    AS ad_dn,259       SYS_CONTEXT('USERENV', 'AUTHENTICATION_METHOD')  AS method,260       SYS_CONTEXT('USERENV', 'IDENTIFICATION_TYPE')    AS id_type,261       SYS_CONTEXT('USERENV', 'LDAP_SERVER_TYPE')       AS ldap_type262FROM dual;263 264PROMPT Roles enabled in this session265SELECT role266FROM session_roles267ORDER BY role;268 269-- Not yet verified270PROMPT271PROMPT ==== 9 of 9: Roles granted to each user, by how they log in ====272CLEAR COLUMNS273CLEAR BREAKS274SET LINESIZE 160 PAGESIZE 300 TRIMOUT ON TAB OFF275COLUMN authentication HEADING "AUTHENTICATION" FORMAT A14276COLUMN username       FORMAT A30277COLUMN account_status HEADING "STATUS" FORMAT A20 TRUNCATE278COLUMN granted_role   HEADING "ROLE" FORMAT A30279COLUMN admin          HEADING "ADMIN" FORMAT A5280COLUMN dflt           HEADING "DEFAULT" FORMAT A7281COLUMN users          HEADING "USERS" FORMAT 99,990282 283SELECT authentication_type AS authentication, COUNT(*) AS users284FROM dba_users285WHERE oracle_maintained = 'N'286GROUP BY authentication_type287ORDER BY 1;288 289BREAK ON authentication SKIP 1 ON username ON account_status290 291SELECT u.authentication_type AS authentication,292       u.username,293       u.account_status,294       r.granted_role,295       r.admin_option AS admin,296       r.default_role AS dflt297FROM dba_users u298LEFT JOIN dba_role_privs r ON r.grantee = u.username299WHERE u.oracle_maintained = 'N'300ORDER BY u.authentication_type, u.username, r.granted_role;301 302CLEAR BREAKS303