OracleSecurity & users

CMU step 7: map AD users and groups

Creates database users and roles tied to AD, the way Oracle's 19c guide recommends: users log in through a shared schema mapped to an AD group, which gets minimal privileges, and get everything else from global roles mapped to other AD groups. Access is then granted or removed in AD. Admin privileges can't go through roles, so they go on a shared schema of their own. Replace every DN before running.

Changes data19c

Not yet verified. How scripts are tested

This script changes the database. Check the names and values before you run it.

ora-cmu-map.sql

1-- In the PDB from step 6. Replace each DN with the real one from AD.2 3-- Shared schema: everyone in this AD group logs in to one database user.4-- Keep its privileges minimal. Put an AD user in only one group mapped to a schema.5CREATE USER ad_app_users IDENTIFIED GLOBALLY AS 'CN=Oracle App Users,OU=Groups,DC=corp,DC=example,DC=com';6GRANT CREATE SESSION TO ad_app_users;7 8-- Global role: members of this AD group get the role's privileges when they log in.9-- A session can have at most 150 enabled roles.10CREATE ROLE ad_app_read IDENTIFIED GLOBALLY AS 'CN=Oracle App Readers,OU=Groups,DC=corp,DC=example,DC=com';11GRANT SELECT ON <app_schema>.<table_name> TO ad_app_read;12 13-- Admin privileges can't be granted to roles: use a shared schema per privilege.14-- Needs the admin setup in step 6 first.15-- CREATE USER ad_backup_admins IDENTIFIED GLOBALLY AS 'CN=Oracle Backup Admins,OU=Groups,DC=corp,DC=example,DC=com';16-- GRANT SYSBACKUP TO ad_backup_admins;17 18-- Exclusive schema: one AD user gets their own database user. More upkeep,19-- since joiners and leavers then need changes in every database.20-- CREATE USER jsmith IDENTIFIED GLOBALLY AS 'CN=John Smith,OU=Users,DC=corp,DC=example,DC=com';21-- GRANT CREATE SESSION TO jsmith;

-- In the PDB from step 6. Replace each DN with the real one from AD.

-- Shared schema: everyone in this AD group logs in to one database user.
-- Keep its privileges minimal. Put an AD user in only one group mapped to a schema.
CREATE USER ad_app_users IDENTIFIED GLOBALLY AS 'CN=Oracle App Users,OU=Groups,DC=corp,DC=example,DC=com';
GRANT CREATE SESSION TO ad_app_users;

-- Global role: members of this AD group get the role's privileges when they log in.
-- A session can have at most 150 enabled roles.
CREATE ROLE ad_app_read IDENTIFIED GLOBALLY AS 'CN=Oracle App Readers,OU=Groups,DC=corp,DC=example,DC=com';
GRANT SELECT ON <app_schema>.<table_name> TO ad_app_read;

-- Admin privileges can't be granted to roles: use a shared schema per privilege.
-- Needs the admin setup in step 6 first.
-- CREATE USER ad_backup_admins IDENTIFIED GLOBALLY AS 'CN=Oracle Backup Admins,OU=Groups,DC=corp,DC=example,DC=com';
-- GRANT SYSBACKUP TO ad_backup_admins;

-- Exclusive schema: one AD user gets their own database user. More upkeep,
-- since joiners and leavers then need changes in every database.
-- CREATE USER jsmith IDENTIFIED GLOBALLY AS 'CN=John Smith,OU=Users,DC=corp,DC=example,DC=com';
-- GRANT CREATE SESSION TO jsmith;

Save it as ora-cmu-map.sql and run it with SQL> @ora-cmu-map.

Open in denrepo

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 1: prepare Active DirectoryDone once by an AD administrator before any database work: create the service account the database binds with, install Oracle's password filter on…
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…