PART -1 Configuring Centrally Managed Users (CMU) with Microsoft Active Directory -> Password authentication

 

Oracle Database can authenticate and authorize Microsoft Active Directory users with the database directly without intermediate directories or Oracle Enterprise User Security.

CMU feature started with Oracle database 18c release 1. It provides a simpler integration with Microsoft Active Directory to allow centralized authentication and authorization of users.

The Oracle Database-Microsoft Active Directory integration supports three common authentication methods.

1. An Active Directory-managed password – “Directory Synchronization” - Password authentication

2. No database credentials provided – Kerberos authentication

3. Public key infrastructure (PKI) authentication (certificate-based authentication)

In this Blog I am going to test the first method - Password authentication.

Implementation steps:

Active Directory One-time Configuration Steps:

1. Create active Directory user that the database software will use to communicate with AD. I used PowerShell to create user but we can use “Active Directory Users and Computers” GUI utility as well.

PS C:\Users\Administrator> New-ADUser `
>>    -Name "orasync" `
>>    -UserPrincipalName "orasync@stagecmu.net" `
>>    -DisplayName "Oracle Service Directory User" `
>>    -Description "Service account for Oracle Database authentication." `
>>    -Path = "CN=Managed Service Accounts,DC=stagecmu,DC=net" `
>>    -ChangePasswordAtLogon $false `
>>    -PasswordNeverExpires $true `
>>    -CannotChangePassword $true `
>>    -Enabled $true `
>>    -AccountPassword(Read-Host -AsSecureString "Initial Password:")
Initial Password:: *******

Check the output of created user.

PS C:\Users\Administrator> Get-ADUser -Identity "orasync" -properties DistinguishedName

DistinguishedName : CN=orasync,CN=Managed Service Accounts,DC=STAGECMU,DC=NET
Enabled           : False
GivenName         :
Name              : orasync
ObjectClass       : user
ObjectGUID        : 6a266fd3-b8b4-4b01-8f08-883ddcbde3c7
SamAccountName    : orasync
SID               : S-1-5-21-4248613581-873213861-3280574616-1107
Surname           :
UserPrincipalName : orasync@stagecmu.net

PS C:\Users\Administrator>

2. Provide permissions to this user requires on the Active Directory side using below command.

dsacls "CN=orasync,CN=Managed Service Accounts,DC=STAGECMU,DC=NET" /I:P /G "STAGECMU\orasync:WP;lockoutTime"
dsacls "CN=orasync,CN=Managed Service Accounts,DC=STAGECMU,DC=NET" /I:P /G "STAGECMU\orasync:RP"

3. On the DC, the Oracle Password Filter executable can be copied from the database server

[opc@dbserv1 ~]$ ls $ORACLE_HOME/bin/*.exe
/opt/oracle/product/18c/dbhomeXE/bin/opwdintg.exe
[opc@dbserv1 ~]$

C:\Users\Administrator>scp opc@10.0.1.101:/opt/oracle/product/18c/dbhomeXE/bin/opwdintg.exe .\Desktop\
The authenticity of host '10.0.1.101 (10.0.1.101)' can't be established.
ECDSA key fingerprint is SHA256:FPT5/lB3Unp8pJiDonw0028Zk1FusRNkUnXSnFsYSXw.
Are you sure you want to continue connecting (yes/no/[fingerprint])?
Warning: Permanently added '10.0.1.101' (ECDSA) to the list of known hosts.
opwdintg.exe                                                                          100%  183KB 183.0KB/s   00:00

C:\Users\Administrator>

4. Install the Password Filter into AD

PS C:\Users\Administrator> .\Desktop\opwdintg.exe
PS C:\Users\Administrator>

The utility runs in a Command Shell window automatically:

Do you want to extend AD schema? [Yes/No]:yes
Schema master is DC1.STAGECMU.NET
=============================================================================
Extending AD schema with orclCommonAttribute for user object in AD domain:
DC=STAGECMU,DC=NET
=============================================================================
Schema extension for this domain will be permanent. Continue?[Yes/No]:yes
Connecting to "DC1.STAGECMU.NET"
Logging in as current user using SSPI
Importing directory from file "etadschm.ldf"
Loading entries.....
4 entries modified successfully.

The command has completed successfully
.
Done. Press Enter to continue...

And ends with a prompt to reboot the domain controller:

Do you want to install Oracle password filter?[Yes/No]:yes
Copy .\orapwdfltr.dll to C:\Windows\System32\orapwdfltr.dll
        1 file(s) copied.
Updating registry...
The operation completed successfully.
Registry is updated.
The change requires machine reboot. Do you want to reboot now?[Yes/No]:

After reboot you can see the installation added new password verifier security groups to Active Directory which can be viewed by below command.

PS C:\Users\Administrator> Get-ADGroup -Filter {name -like "ORA*"} | Select Name,ObjectClass,GroupCategory

Name        ObjectClass GroupCategory
----        ----------- -------------
ORA_VFR_11G group            Security
ORA_VFR_12C group            Security
ORA_VFR_MD5 group            Security


PS C:\Users\Administrator>

5. Export the Server’s Public Certificate from Active directory DC and copy to Database Server.

PS C:\Users\Administrator> Export-Certificate -Cert $Cert -FilePath .\$Env:computername.cer -Type CERT -Force


    Directory: C:\Users\Administrator


Mode                LastWriteTime         Length Name
----                -------------         ------ ----
-a----         6/2/2022   9:02 PM           1509 DC1.cer


PS C:\Users\Administrator>
PS C:\Users\Administrator> scp .\$Env:computername.cer opc@10.0.1.101:/tmp/
DC1.cer                                                                                                                                    100% 1509     1.5KB/s   00:00
PS C:\Users\Administrator>

Database Home One-time Configuration Steps:


1. Specifying the Active Directory Servers in a dsi.ora file.

[oracle@dbserv1 ~]$ cat ${ORACLE_HOME}/ldap/admin/dsi.ora
DSI_DIRECTORY_SERVERS = (dc1.ad1.cmuvnc.oraclevcn.com:389:636)
DSI_DEFAULT_ADMIN_CONTEXT = "DC=stagecmu,DC=net"
DSI_DIRECTORY_SERVER_TYPE = AD
[oracle@dbserv1 ~]$

2. Create a Wallet file To hold the “Service Directory User's credential and the certificate

[oracle@dbserv1 ~]$ mkdir -p ${ORACLE_BASE}/admin/${ORACLE_SID}/wallet
[oracle@dbserv1 ~]$ cd ${ORACLE_BASE}/admin/${ORACLE_SID}/wallet
[oracle@dbserv1 wallet]$ orapki wallet create -wallet . -auto_login
Oracle PKI Tool Release 18.0.0.0.0 - Production
Version 18.1.0.0.0
Copyright (c) 2004, 2017, Oracle and/or its affiliates. All rights reserved.

Enter password:
Enter password again:
Operation is successfully completed.
[oracle@dbserv1 wallet]$

[oracle@dbserv1 wallet]$ mkstore -wrl . -createEntry ORACLE.SECURITY.USERNAME orasync
Oracle Secret Store Tool Release 18.0.0.0.0 - Production
Version 18.1.0.0.0
Copyright (c) 2004, 2017, Oracle and/or its affiliates. All rights reserved.

Enter wallet password:
[oracle@dbserv1 wallet]$ mkstore -wrl . -createEntry ORACLE.SECURITY.DN "CN=orasync,CN=Managed Service Accounts,DC=STAGECMU,DC=NET"
Oracle Secret Store Tool Release 18.0.0.0.0 - Production
Version 18.1.0.0.0
Copyright (c) 2004, 2017, Oracle and/or its affiliates. All rights reserved.

Enter wallet password:
[oracle@dbserv1 wallet]$

[oracle@dbserv1 wallet]$ mkstore -wrl . -createEntry ORACLE.SECURITY.PASSWORD
Oracle Secret Store Tool Release 18.0.0.0.0 - Production
Version 18.1.0.0.0
Copyright (c) 2004, 2017, Oracle and/or its affiliates. All rights reserved.

Your secret/Password is missing in the command line
Enter your secret/Password:
Re-enter your secret/Password:
Enter wallet password:
[oracle@dbserv1 wallet]$

3. Import the certificate transferred from the domain controller into the same Oracle wallet using:

[oracle@dbserv1 wallet]$ orapki wallet add -wallet . -cert /tmp/*.cer -trusted_cert
Oracle PKI Tool Release 18.0.0.0.0 - Production
Version 18.1.0.0.0
Copyright (c) 2004, 2017, Oracle and/or its affiliates. All rights reserved.

Cannot modify auto-login (sso) wallet
Enter wallet password:
Operation is successfully completed.
[oracle@dbserv1 wallet]$



[oracle@dbserv1 wallet]$ orapki wallet display -wallet .
Oracle PKI Tool Release 18.0.0.0.0 - Production
Version 18.1.0.0.0
Copyright (c) 2004, 2017, Oracle and/or its affiliates. All rights reserved.

Requested Certificates:
User Certificates:
Oracle Secret Store entries:
ORACLE.SECURITY.DN
ORACLE.SECURITY.PASSWORD
ORACLE.SECURITY.USERNAME
Trusted Certificates:
Subject:        CN=DC1.STAGECMU.NET
[oracle@dbserv1 wallet]$

4. Database Initialization Parameter adjustments:

[oracle@dbserv1 wallet]$ echo "ALTER SYSTEM SET ldap_directory_access='PASSWORD' SCOPE=both;" | sqlplus -s / as sysdba

System altered.

[oracle@dbserv1 wallet]$

One time Database home setup is complete.
Now create a database user for testing.
First we need to create a user in Active directory and added to one of security group created earlier.
I already created a user manoj and added to one of group as we can see below:

PS C:\Users\Administrator> Get-ADUser -Identity "manoj" -properties DistinguishedName


DistinguishedName : CN=Manoj Kumar,CN=Users,DC=STAGECMU,DC=NET
Enabled           : True
GivenName         : Manoj
Name              : Manoj Kumar
ObjectClass       : user
ObjectGUID        : 85dde47e-2742-4519-9bde-87a174a901df
SamAccountName    : manoj
SID               : S-1-5-21-4248613581-873213861-3280574616-1112
Surname           : Kumar
UserPrincipalName : manoj@STAGECMU.NET
PS C:\Users\Administrator>

PS C:\Users\Administrator> Get-ADPrincipalGroupMembership "manoj" | select name

name
----
Domain Users
ORA_VFR_12C

PS C:\Users\Administrator>

Make sure a hash value will be recorded in the orclCommmonAttribute field in Active Directory. If the orclCommonAttribute is blank the database user will fail to authenticate.

PS C:\Users\Administrator> Get-ADUser -Identity "manoj" -properties orclCommonAttribute


DistinguishedName   : CN=Manoj Kumar,CN=Users,DC=STAGECMU,DC=NET
Enabled             : True
GivenName           : Manoj
Name                : Manoj Kumar
ObjectClass         : user
ObjectGUID          : 85dde47e-2742-4519-9bde-87a174a901df
orclCommonAttribute : {MR-SHA512}apN3SAQVrgYmQ4ITFZuQdbmMbf+KkU1sjzj5vcJSmEQZFQHTN3/HDS3QYfPGu0xymTn6UYZRws95rG55q3KUZbiEcYPAN4ut1rtBSpUa6Tg=
SamAccountName      : manoj
SID                 : S-1-5-21-4248613581-873213861-3280574616-1112
Surname             : Kumar
UserPrincipalName   : manoj@STAGECMU.NET

PS C:\Users\Administrator>

Once we know the DN of user as per output of above command now we are ready to create a user in database.

SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 ORCLPDB1                       READ WRITE NO
SQL> ALTER SESSION SET CONTAINER = ORCLPDB1;

Session altered.

SQL> create user manoj IDENTIFIED GLOBALLY AS 'CN=Manoj Kumar,CN=Users,DC=stagecmu,DC=net';

User created.

SQL> GRANT create session TO manoj;

Grant succeeded.

SQL> GRANT SELECT ON v_$database TO manoj;

Grant succeeded.

SQL>

For testing purpose I created a new TNS service entry

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = dbserv1.ad1.cmuvnc.oraclevcn.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ORCLPDB1)
    )
  )
  
  [oracle@dbserv1 ~]$ tnsping ORCL

TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 13-JUL-2022 16:31:56

Copyright (c) 1997, 2022, Oracle.  All rights reserved.

Used parameter files:
/opt/oracle/product/19c/dbhome_1/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = dbserv1.ad1.cmuvnc.oraclevcn.com)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ORCLPDB1)))
OK (0 msec)
[oracle@dbserv1 ~]$

FINALLY, all setup is done now and we can test the connection:

[oracle@dbserv1 wallet]$ sqlplus manoj@ORCL

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Jun 17 19:59:33 2022
Version 19.15.0.0.0

Copyright (c) 1982, 2022, Oracle.  All rights reserved.

Enter password:
Last Successful login time: Fri Jun 17 2022 19:04:13 +00:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.15.0.0.0

SQL> show user
USER is "MANOJ"
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.15.0.0.0
[oracle@dbserv1 wallet]$

At the end we can also see how the user is authenticated using below command:

[oracle@dbserv1 ~]$ echo "
>  set heading off
>  select 'DB_NAME (from v\$database) : '||name,
>         'SESSION_USER              : '||sys_context('USERENV','SESSION_USER'),
>         'AUTHENTICATED_IDENTITY    : '||sys_context('USERENV','AUTHENTICATED_IDENTITY'),
>         'AUTHENTICATION_METHOD     : '||sys_context('USERENV','AUTHENTICATION_METHOD'),
>         'LDAP_SERVER_TYPE          : '||sys_context('USERENV','LDAP_SERVER_TYPE'),
>         'ENTERPRISE_IDENTITY       : '||sys_context('USERENV','ENTERPRISE_IDENTITY')
>    from v\$database;
>  " | sqlplus -s -L manoj/welcome2@//dbserv1.ad1.cmuvnc.oraclevcn.com:1521/ORCLPDB1

DB_NAME (from v$database) : ORCLCDB
SESSION_USER              : MANOJ
AUTHENTICATED_IDENTITY    : STAGECMU\MANOJ
AUTHENTICATION_METHOD     : PASSWORD_GLOBAL
LDAP_SERVER_TYPE          : AD
ENTERPRISE_IDENTITY       : cn=manoj kumar,cn=Users,dc=STAGECMU,dc=NET


[oracle@dbserv1 ~]$