This is the third in a four-part article series related to testing Oracle Database 18c Centrally Managed Users (CMU) by leveraging the Oracle Cloud Infrastructure (OCI) for Oracle DBAs to create a lab or testbed. The second article covered Configuring Windows And Linux Servers For CMU Testing.
Implementing Oracle Centrally Managed Users (CMU) via “password synchronization” with Active Directory (AD) is the most practical configuration for many use cases as the authentication process from the perspective of the application remains unchanged. Applications still prompt for credentials – the key difference is that the database communicates with Active Directory for password validation.
Consequently, the database must be configured so that it can securely communicate with AD and that database users and groups are created accordingly. This article walks through how to configure and test at both the AD and database level.
Active Directory One-time Configuration Steps
The first requirement is to have an Active Directory user that the database software will use to communicate with AD. When the database receives an authorization request (i.e. a “database logon”) it needs to talk to AD to validate the user-provided credentials. Hence, a user is required for Oracle Database software to AD interaction.
As usual, this user can be added via the “Active Directory Users and Computers” GUI utility. However, for simplicity, a PowerShell equivalent command can be used.
On the Domain Controller DC1, open a PowerShell window as Administrator. Then a new user, in this case called “orasync” can be created. (IMPORTANT: update the UserPrincipalName and Path to show your actual domain vs the STAGECOACH.NET domain being used in this example):
New-ADUser ` -Name = "orasync" ` -UserPrincipalName = "[email protected]" ` -DisplayName = "Oracle Service Directory User" ` -Description = "Service account for Oracle Database authentication." ` -Path = "CN=Managed Service Accounts,DC=stagecoach,DC=net" ` -ChangePasswordAtLogon = $false ` -PasswordNeverExpires = $true ` -CannotChangePassword = $true ` -Enabled = $true ` -AccountPassword(Read-Host -AsSecureString "Initial Password:") |
Putting the service account user in the “Managed Service Accounts” folder in Active Directory is a preference to reduce clutter. It’s in no way mandatory.
We can do a simple confirmation that the user was created using the PowerShell command dsquery:
dsquery user -name orasync |
Sample output:
PS C:\Users\Administrator> dsquery user -name orasync "CN=orasync,CN=Managed Service Accounts,DC=STAGECOACH,DC=NET" PS C:\Users\Administrator> |
A more detailed output can be provided using the PowerShell command Get-ADUser:
Get-ADUser -Identity "orasync" -properties DistinguishedName |
Sample output:
PS C:\Users\Administrator> Get-ADUser -Identity "orasync" -properties DistinguishedName DistinguishedName : CN=orasync,CN=Managed Service Accounts,DC=STAGECOACH,DC=NET Enabled : True GivenName : Name : orasync ObjectClass : user ObjectGUID : 0335e42e-f597-4bd8-a6dd-5f41e75177bf SamAccountName : orasync SID : S-1-5-21-4278349699-2454951225-2679278977-1108 Surname : UserPrincipalName : [email protected] PS C:\Users\Administrator> |
These same results can also be confirmed using the GUI utility “Active Directory Users and Computers”:
The permissions that this user requires on the Active Directory side are not overly clear from the official documentation. However, the following simple dsacls commands seem to provide what is required (again, change the domain name as required):
dsacls "CN=orasync,CN=Managed Service Accounts,DC=STAGECOACH,DC=NET" /I:P /G "STAGECOACH\orasync:WP;lockoutTime" dsacls "CN=orasync,CN=Managed Service Accounts,DC=STAGECOACH,DC=NET" /I:P /G "STAGECOACH\orasync:RP" |
The dsacls commands produce a lot of output which can be ignored.
In the previous article, an SSH public-private keypair for the Domain Controller DC1 was created. Since we will need to copy files to and from the database server, the domain controller’s public key should be copied to the database server.
On the Active Directory Domain Controller DC1, first display and copy the public key:
Copy the public SSH key from the Windows host to the database server DBSERV1 into the ~/.ssh/authorized_keys file for the “opc” user. Be careful not to include carriage returns or end of line markers when copying.
Example:
