In the first post of this series, Enable Kerberos authentication with Amazon RDS Custom for Oracle – Part 1, we showed you how to implement Kerberos authentication with Amazon RDS Custom for Oracle to centralize the storage and management of credentials across multiple databases and improve your organization’s overall security profile. In this post, we take it one step further by demonstrating how you can use centrally managed users (CMU) with RDS Custom to simplify the authorization of authenticated users across databases. This essentially enables role-based access control (RBAC), which determines what operations users or roles can perform and which objects they have access to. This approach eases user administration, minimizes errors, and enhances security.
CMU is an Enterprise Edition feature released in Oracle database 18c that allows for simplified database user management through integration with Microsoft Active Directory (AD). With CMU, AD users can be mapped to exclusive or shared accounts in the database. You can also map AD groups to database global roles, which then provides additional database privileges and roles above what’s provided to the login account.
Solution overview
In this post, we implement the CMU feature using Kerberos authentication, using AWS Directory Service for Microsoft Active Directory, a RDS Custom for Oracle instance, and an Oracle database client on an Amazon Elastic Compute Cloud (Amazon EC2) instance. This includes the following high-level steps:
Configure AWS Managed Microsoft AD for CMU.
Configure the database server.
Connect to the database.
The following diagram illustrates the solution architecture.
In this example, we use CMU to authenticate and authorize the following users in AD.
AD User
AD Group
DB User
DB Role
alice
HRGrp
HRUSER
–
bob
HRGrp, HRMgrGrp
HRUSER
HRMGRROLE
Additionally, we map AD users to exclusive database users (see the following table), and grant administrative privileges to those AD users.
AD User
AD Group
DB User
Privileges
alice
–
ALICE
–
admin
AD_DBA_SYSDBA_USERS
AD_DBA_SYSDBA_USERS
SYSDBA
Prerequisites
Before we start, we assume you have successfully configured Kerberos authentication based on the steps provided in Enable Kerberos Authentication with RDS Custom for Oracle (Part 1). To recap, you start with the following setup configurations:
AWS Managed Microsoft AD:
Directory DNS name – corp.example.com
DNS address – 10.1.4.88 / 10.1.5.76
Security group IDs – sg-0123ec2example
RDS Custom DB instance:
DB identifier – orcl
Endpoint – orcl.abcdefghijk.ap-southeast-1.rds.amazonaws.com
Port – 1521
Hostname – ip-10-1-4-113 (output of command hostname)
Domain – ‘’ (output of command domainname)
Fully qualified domain name (FQDN) – ip-10-1-4-113 (concatenation based on previous hostname and domain values)
Resource ID – db-ABCDEFGHIJKLMNOPQRS0123456
Database client:
Linux OS – Amazon Linux 2
Hostname – ip-10-1-4-29.ap-southeast-1.compute.internal
Oracle base – /u01/app/oracle
Oracle home – /u01/client
Oracle version – 19.13
Configure AWS Managed Microsoft AD for CMU
In this section, we walk through the steps to configure AWS Managed Microsoft AD for CMU.
Configure LDAPS for AWS Managed Microsoft AD
Server-side LDAPS encrypts communication between Oracle and AWS Managed Microsoft AD by using the Secure Sockets Layer (SSL) cryptographic protocol. For detailed instructions on how to set up and configure server-side LDAPS and your certificate authority (CA) server, refer to Enable server-side LDAPS using AWS Managed Microsoft AD.
The following are the key points in the LDAPS configuration:
Server-side LDAPS requires certificates issued by the Microsoft enterprise CA server that is joined to your AWS Managed Microsoft AD domain.
The CA configuration tested in this post is for a root Microsoft enterprise CA as documented in Create a root Microsoft enterprise CA. The choice of subordinate or root CA won’t impact Oracle’s capabilities on CMU integration, as long as the root certificates and the immediate certificates are imported to an Oracle wallet as shown in the later steps.
Your security group rules must allow AWS Managed Microsoft AD to connect to your enterprise CA to request a certificate. This includes inbound rules so that your enterprise CA can accept incoming traffic from your domain controllers and outbound rules to allow traffic from your domain controllers to the enterprise CA.
You can test the LDAPS connection to the AWS Managed Microsoft AD directory by using the LDP tool to connect to corp.example.com with port 636 using the SSL option.
The connection is established successfully if you can see the following first line in the output: ld = ldap_sslinit(“corp.example.com”, 636, 1). The LDP tool is available on the CA server as a path of the Active Directory Administration Tools installation.
Export the root certificate and other immediate certificates (if used) in Base-64 encoded X.509 (.CER) format and save the file name as AD_CA_Root_cert.txt. You can export the root certificate using Microsoft Management Console (MMC) Certificates snap-in (Certmgr.msc) from path Certificates – Local ComputerTrusted Root Certification AuthoritiesCertificates<Your-CA-Name>.
Create an Oracle service directory user account in AD
An Oracle service directory user account is a regular Active Directory user account, and is used for interactions between the Oracle database and the LDAP directory service in AD. Because it’s only used by Oracle Database, it’s typically configured with the account option Password never expires, but this isn’t a requirement for CMU integration.
To create an Oracle service directory user account in Microsoft AD, complete the following steps:
Create a user in AWS Managed Microsoft AD with the following parameters. We recommend using a generic naming convention (for example, oracleldaps) for the service account name, so that it can be used in multiple RDS Custom instances (including primary and read replicas) to avoid confusion.
Create in – corp.example.com/CORP/Users
Full name – oracleldaps
User logon name – [email protected]
User logon name (pre-Windows 2000) – CORPoracleldaps
Password – Complex password meeting your password policy
Account option – Password never expires
The minimum permissions required for the service account is the read properties permission for Kerberos authentication. You will need additional privileges if your CMU integration requires password authentication.
To grant the read properties permission, open Active Directory Users and Computers on the Microsoft Management Console (MMC).
Under corp.example.com/CORP/Users, choose (right-click) Users and choose Delegate Control.
Choose Next.
For Users or Groups, select oracleldaps, which you want to delegate control, then choose Next.
For Tasks to Delegate, select Create a custom task to delegate, then choose Next.
For Active Directory Object Type, select This folder, existing objects in this folder, and creation of new objects in this folder, then choose Next.
For Permissions, select Read.
Choose Next and continue with the defaults to complete the setup.
Create demo users for Kerberos authentication with CMU
A typical configuration with CMU is mapping Oracle Database users to a shared database schema (user). This reduces the database user management efforts when AD users join, change job roles, or leave the organization. In this post, we create two AD groups, HRGrp and HRMgrGrp, with two AD users, alice and bob. When these users log in to the Oracle database, they map to a shared database user HRUSER, and Bob receives additional database privileges via the role HRMGRROLE mapped to group HRMgrGrp. There are multiple supported ways to map your AD users and groups to database users and roles; refer to Configuring Authorization for Centrally Managed Users for additional details.
Create the first group in corp.example.com/CORP/Users with the following parameters:
Group name – HRGrp
Group name (pre-Windows 2000) – HRGrp
Group scope – Global
Group type – Security
Create the second group in corp.example.com/CORP/Users:
Group name – HRMgrGrp
Group name (pre-Windows 2000) – HRMgrGrp
Group scope – Global
Group type – Security
Create the first user in corp.example.com/CORP/Users with the following parameters:
Full name – alice
User logo name – [email protected]
User logon name (pre-Windows 2000) – CORPalice
Password – Use a complex password that meets your password policy
Member of – Domain Users, HRGrp
Create the second user in corp.example.com/CORP/Users:
Full name – bob
User logo name – [email protected]
User logon name (pre-Windows 2000) – CORPbob
Password – Use a complex password that meets your password policy
Member of – Domain Users, HRGrp, HRMgrGrp
Get a distinguished name for users and groups created in AD
You must supply the correct distinguished name (DN) in the subsequent steps. Complete the following steps to retrieve the correct DN value, using user oracleldaps as an example:
Open the Active Directory Users and Computers console.
Choose View and select Advanced Features.
Open the Properties page for the user (oracleldaps) to check the DN.
Choose Attribute Editor, search for distinguishedName, then choose View.
Alternatively, you can retrieve the DN value for user oracleldaps with the following command line:
Configure the database server
In this section, we walk through the steps to configure the database server.
Create an auto-login wallet
Complete the following steps:
Use the orapki command to create an auto-login wallet in a directory.
You will be prompted with a wallet password. Note down the password; you will need this to modify the wallet in subsequent steps.
Create an entry in the wallet for the Oracle service directory user account (oracleldaps) to perform searches in Active Directory:
Create an entry in the wallet with the DN of the Oracle service directory user account (oracleldaps):
Create an entry in the wallet with the user password credential of the Oracle service directory user account (oracleldaps). Replace YourComplexPassword in the command line with your actual password for AD user oracleldaps.
In the event that the password credential for AD user oracleldaps changes in the future, you can use -modifyEntry option to update the password.
Add the certificate (AD_CA_Root_cert.txt) you prepared previously to the wallet:
Verify the wallet credentials:
Specify the Oracle wallet location in sqlnet.ora with the following entries:
Create a dsi.ora file to specify connections to AD
Although you can specify the connection to the Active Directory server in the ldap.ora file, Oracle’s recommendation is to use the file dsi.ora to specify connections to the Active Directory server for centrally managed users.
Create the file /rdsdbdata/config/security/wallet/dsi.ora with the following content:
DSI_DIRECTORY_SERVERS = (corp.example.com:389:636)
DSI_DIRECTORY_SERVER_TYPE = AD
Configure access from your Oracle database to the Active Directory server by setting the parameter LDAP_DIRECTORY_ACCESS:
With the value PASSWORD, Oracle tries to connect to the AD LDAP by using the database password stored in the database wallet.
Database authentication and authorization
Authentication verifies that users connecting to the database are legitimate with valid credentials. Authorization grants the necessary privileges to users to perform their required tasks after logging in.
With the CMU feature integration, you can authenticate to the database using the following methods:
Authenticate via Kerberos authentication – In this series, we have configured Kerberos authentication as documented in Enable Kerberos authentication with Amazon RDS Custom for Oracle – Part 1.
Authenticate using user name and password filters stored in AD – For this configuration, you need to use an Oracle supplied utility opwdintg.exe to extend the AD schema with a new attribute orclCommonAttribute. Because this utility requires tools to be copied and run on the AD server, it only works with a self-managed AD instead of AWS Managed Microsoft AD. For detailed instructions, refer to Configuring Password Authentication for Centrally Managed Users.
Authenticate using public key infrastructure (PKI) certificates – For detailed instructions, refer to Configuring Authentication Using PKI Certificates for Centrally Managed Users.
With CMU, you can map AD users to a shared Oracle database user through the membership of a directory group. This minimizes the effort of maintaining users when they join, leave, or change roles in an organization. In the following example, we map the AD group HRGrp to the database shared user HRUSER. When Alice and Bob log in to the database, they’re mapped to the user HRUSER, because they’re members of the HRGrp AD group.
Oracle recommends having a single mapping per AD user.
For demonstration purposes, we grant the CREATE SESSION privilege to allow users to log in to the database for subsequent steps:
When mapping multiple users in a directory group to a shared database global user, it’s recommended to grant the least amount of privileges required for the shared user. Additional privileges and roles can be granted via global roles, which map to directory groups. In the following example, Bob is a member of the AD directory group HRMgrGrp; his database session has the role HRMGRROLE enabled after login:
Connect to the database
After we configure the CMU integration, we can connect to the database from the database client prepared previously, and verify the login information.
Log in to the database using user Alice
Complete the following steps as user Alice:
Obtain a Kerberos ticket using the command okinit:
List the Kerberos ticket from the Kerberos ticket cache using the command oklist:
Connect to the database:
Verify the user session information:
Log in to the database using user Bob
Complete the following steps as user Bob:
Obtain a Kerberos ticket using okinit:
List the Kerberos ticket from the Kerberos ticket cache using oklist:
Connect to the database:
Verify the user session information:
Because Bob is a member of the group HRMgrGrp, upon successfully login, the role HRMGRROLE is automatically enabled for his session:
Retrieve additional context for the session:
View the audit trail for compliance
Because Alice and Bob are both mapped to the HRUSER in the database, it’s critical that we can identify the source user who performed the actions against the database. We can retrieve this information from the OS_USERNAME and COMMENT_TEXT columns from the DBA_AUDIT_TRAIL view for standard auditing configurations, and the EXTERNAL_USERID column from the UNIFIED_AUDIT_TRAIL view for unified auditing configurations.
Additional considerations
In this section, we discuss additional considerations when implementing this solution.
Exclusive mapping of a directory user to a database global user
If required, you can map a directory user to a database global user exclusively. If the same user has been mapped to both exclusive and shared database global users, the exclusive mapping takes precedence.
The following SQL statements create an exclusive mapping for Alice to a database user ALICE:
After you successfully log in with user Alice, you can verify the session authentication information:
Mapping administrative users to database global users
Administrative privileges, such as SYSDBA, SYSOPER, SYSBACKUP, SYSDG, and SYSKM, can’t be granted via global roles. You can map administrative users in AD to shared or exclusive mapped database global users, which have already been granted with such privileges. In this example, we map the AD user admin to database user AD_DBA_SYSDBA_USERS with SYSDBA privileges.
Set the LDAP_DIRECTORY_SYSAUTH parameter to YES, and bounce the instance for the static parameter to take effect:
Verify that the database password file exists and with version 12.2 format.
You can retrieve SYS password from AWS Secrets Manager. The secret name starts with do-not-delete-rds-custom-db-ABCDEFGHIJKLMNOPQRS0123456, and db-ABCDEFGHIJKLMNOPQRS0123456 is the database resource ID.
Create the AD group AD_DBA_SYSDBA_USERS and add user admin to it with the following parameters:
Create in – corp.example.com/CORP/Users
Group name – AD_DBA_SYSDBA_USERS
Group name (pre-Windows 2000) – AD_DBA_SYSDBA_USERS
Group scope – Global
Group type – Security
Create the database global user AD_DBA_SYSDBA_USERS to map to the directory group AD_DBA_SYSDBA_USERS.
Members of this group can log in to the database as SYSDBA.
Now you can connect to remotely using admin (a member of AD_DBA_SYSDBA_USERS) to log in to the database with SYSDBA privileges:
Troubleshooting
If you encounter the error ORA-28030: Server encountered problems accessing LDAP directory service during database login, make sure you configured your Oracle database wallet correctly and it’s located in the default location or the location specified in sqlnet.ora.
To further troubleshoot the issue, you can enable the following event in the database before reproducing the issue. This generates a trace file in the Automatic Diagnostic Repository (ADR) that contains more verbose information that will assist in troubleshooting.
To disable the trace, run the following SQL statement:
Conclusion
RDS Custom for Oracle provides users with different authentication and authorization options to meet your organization’s security and compliance requirements. In Part 1, we showed how you could use AWS Managed Microsoft AD and Kerberos to centralize the storage and management of credentials for your RDS Custom for Oracle users. In this post, we extended the solution by using the CMU feature of Oracle Enterprise Edition. This allowed for the mapping of Microsoft AD users and roles with database users (shared and exclusive) and global roles. This integration with Microsoft AD provides for the centralization of both authentication and authorization, which eases administration of users and enhances your security posture.
If you have any comments or questions, leave them in the comments section.
About the Authors
Donghua Luo is a Senior RDS Specialist Solutions Architect. He works with AWS customers to migrate and achieve higher flexibility, scale, and resiliency with database services in AWS cloud.
William Wong is a Principal Database Specialist Solutions Architect with Amazon Web Services based out of Australia. He is passionate around all things data and works closely with customers to help improve and modernize their database workloads on AWS. William brings 20+ years of experience in the field, from migrating legacy applications, to modernising with open source engines, or innovating with purpose built databases.
Read MoreAWS Database Blog
