AWS Database Blog

Enable Kerberos authentication with Amazon RDS Custom for Oracle – Part 2

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:

  1. Configure AWS Managed Microsoft AD for CMU.
  2. Configure the database server.
  3. Connect to the database.

The following diagram illustrates the solution architecture.

Architecture Overview

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.

LDAP Server for LDP

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.

LDP output

  • 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 Computer\Trusted Root Certification Authorities\Certificates\<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:

  1. 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.
    1. Create in – corp.example.com/CORP/Users
    2. Full name – oracleldaps
    3. User logon name – oracleldaps@corp.example.com
    4. User logon name (pre-Windows 2000) – CORP\oracleldaps
    5. Password – Complex password meeting your password policy
    6. 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.

  1. To grant the read properties permission, open Active Directory Users and Computers on the Microsoft Management Console (MMC).
  2. Under corp.example.com/CORP/Users, choose (right-click) Users and choose Delegate Control.

Launch Delegate Control

  1. Choose Next.
  2. For Users or Groups, select oracleldaps, which you want to delegate control, then choose Next.
  3. For Tasks to Delegate, select Create a custom task to delegate, then choose Next.
  4. For Active Directory Object Type, select This folder, existing objects in this folder, and creation of new objects in this folder, then choose Next.
  5. For Permissions, select Read.
  6. Choose Next and continue with the defaults to complete the setup.

Permissions for Delegation of Control

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 – alice@corp.example.com
  • User logon name (pre-Windows 2000) – CORP\alice
  • 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 – bob@corp.example.com
  • User logon name (pre-Windows 2000) – CORP\bob
  • 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:

  1. Open the Active Directory Users and Computers console.
  2. Choose View and select Advanced Features.
  3. Open the Properties page for the user (oracleldaps) to check the DN.
  4. Choose Attribute Editor, search for distinguishedName, then choose View.

Distinguished Name in Attribute Editor

Alternatively, you can retrieve the DN value for user oracleldaps with the following command line:

C:\>dsquery user -name oracleldaps -o dn
"CN=oracleldaps,OU=Users,OU=CORP,DC=corp,DC=example,DC=com"

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:

  1. 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.

$ orapki wallet create -wallet /rdsdbdata/config/security/wallet/ -auto_login

Oracle PKI Tool Release 23.0.0.0.0 - Production
Version 23.0.0.0.0
Copyright (c) 2004, 2021, Oracle and/or its affiliates. All rights reserved.

Enter password:   
Enter password again:   
Operation is successfully completed.
  1. Create an entry in the wallet for the Oracle service directory user account (oracleldaps) to perform searches in Active Directory:
$ mkstore -wrl /rdsdbdata/config/security/wallet/ -createEntry ORACLE.SECURITY.USERNAME oracleldaps

Oracle Secret Store Tool Release 23.0.0.0.0 - Production
Version 23.0.0.0.0
Copyright (c) 2004, 2021, Oracle and/or its affiliates. All rights reserved.

Enter wallet password:
  1. Create an entry in the wallet with the DN of the Oracle service directory user account (oracleldaps):
$ mkstore -wrl /rdsdbdata/config/security/wallet/ -createEntry ORACLE.SECURITY.DN CN=oracleldaps,OU=Users,OU=CORP,DC=corp,DC=example,DC=com

Oracle Secret Store Tool Release 23.0.0.0.0 - Production

Version 23.0.0.0.0

Copyright (c) 2004, 2021, Oracle and/or its affiliates. All rights reserved.

Enter wallet password:
  1. 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.
$ mkstore -wrl /rdsdbdata/config/security/wallet/ -createEntry ORACLE.SECURITY.PASSWORD YourComplexPassword

Oracle Secret Store Tool Release 23.0.0.0.0 - Production

Version 23.0.0.0.0

Copyright (c) 2004, 2021, Oracle and/or its affiliates. All rights reserved.

Enter wallet password:

In the event that the password credential for AD user oracleldaps changes in the future, you can use -modifyEntry option to update the password.

  1. Add the certificate (AD_CA_Root_cert.txt) you prepared previously to the wallet:
$ orapki wallet add -wallet /rdsdbdata/config/security/wallet/ -cert /tmp/AD_CA_Root_cert.txt -trusted_cert

Oracle PKI Tool Release 23.0.0.0.0 - Production
Version 23.0.0.0.0
Copyright (c) 2004, 2021, Oracle and/or its affiliates. All rights reserved.

Cannot modify auto-login (sso) wallet
Enter wallet password:
Operation is successfully completed.
  1. Verify the wallet credentials:
$ orapki wallet display -wallet /rdsdbdata/config/security/wallet/

Oracle PKI Tool Release 23.0.0.0.0 - Production
Version 23.0.0.0.0
Copyright (c) 2004, 2021, 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=corp-EC2AMAZ-7RRI139-CA,DC=corp,DC=example,DC=com
  1. Specify the Oracle wallet location in sqlnet.ora with the following entries:
WALLET_LOCATION=  
  (SOURCE=
      (METHOD=file)
      (METHOD_DATA=  
         (DIRECTORY=/rdsdbdata/config/security/wallet)))

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.

  1. 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

  1. Configure access from your Oracle database to the Active Directory server by setting the parameter LDAP_DIRECTORY_ACCESS:
SQL> ALTER SYSTEM SET LDAP_DIRECTORY_ACCESS = 'PASSWORD';

System altered.

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:

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.

CREATE USER HRUSER IDENTIFIED GLOBALLY AS
'CN=HRGrp,OU=Users,OU=CORP,DC=corp,DC=example,DC=com';

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:

GRANT CREATE SESSION TO HRUSER;

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:

CREATE ROLE HRMGRROLE IDENTIFIED GLOBALLY AS
'CN=HRMgrGrp,OU=Users,OU=CORP,DC=corp,DC=example,DC=com';

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:

  1. Obtain a Kerberos ticket using the command okinit:
[oracle@ip-10-1-4-29 ~]$ okinit alice

Kerberos Utilities for Linux: Version 19.0.0.0.0 - Production on 03-OCT-2022 10:53:56

Copyright (c) 1996, 2019 Oracle.  All rights reserved.

Configuration file : /etc/krb5.conf.
Password for alice@CORP.EXAMPLE.COM:
  1. List the Kerberos ticket from the Kerberos ticket cache using the command oklist:
[oracle@ip-10-1-4-29 ~]$ oklist

Kerberos Utilities for Linux: Version 19.0.0.0.0 - Production on 03-OCT-2022 10:54:02

Copyright (c) 1996, 2019 Oracle.  All rights reserved.

Configuration file : /etc/krb5.conf.
Ticket cache: FILE:/tmp/krb5cc_54321
Default principal: alice@CORP.EXAMPLE.COM

Valid starting     Expires            Service principal
10/03/22 10:54:01  10/03/22 20:54:01  krbtgt/CORP.EXAMPLE.COM@CORP.EXAMPLE.COM
        renew until 10/10/22 10:53:56
  1. Connect to the database:
[oracle@ip-10-1-4-29 ~]$ sqlplus /@orcl.abcdefghijk.ap-southeast-1.rds.amazonaws.com:1521/ORCL

SQL*Plus: Release 19.0.0.0.0 - Production on Mon Oct 3 10:55:41 2022
Version 19.13.0.0.0

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


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

SQL>
  1. Verify the user session information:
SQL> SHOW USER 
USER is "HRUSER"
SQL> SELECT SYS_CONTEXT('USERENV', 'CURRENT_USER') FROM DUAL;

SYS_CONTEXT('USERENV','CURRENT_USER')
--------------------------------------------------------------------------------
HRUSER
SQL> SELECT SYS_CONTEXT('USERENV', 'AUTHENTICATED_IDENTITY') FROM DUAL;

SYS_CONTEXT('USERENV','AUTHENTICATED_IDENTITY')
--------------------------------------------------------------------------------
alice@CORP.EXAMPLE.COM
SQL> SELECT ROLE FROM SESSION_ROLES ORDER BY ROLE;

no rows selected

Log in to the database using user Bob

Complete the following steps as user Bob:

  1. Obtain a Kerberos ticket using okinit:
[oracle@ip-10-1-4-29 ~]$ okinit bob

Kerberos Utilities for Linux: Version 19.0.0.0.0 - Production on 03-OCT-2022 10:56:58

Copyright (c) 1996, 2019 Oracle.  All rights reserved.

Configuration file : /etc/krb5.conf.
Password for bob@CORP.EXAMPLE.COM:
  1. List the Kerberos ticket from the Kerberos ticket cache using oklist:
[oracle@ip-10-1-4-29 ~]$ oklist

Kerberos Utilities for Linux: Version 19.0.0.0.0 - Production on 03-OCT-2022 10:57:10

Copyright (c) 1996, 2019 Oracle.  All rights reserved.

Configuration file : /etc/krb5.conf.
Ticket cache: FILE:/tmp/krb5cc_54321
Default principal: bob@CORP.EXAMPLE.COM

Valid starting     Expires            Service principal
10/03/22 10:57:01  10/03/22 20:57:01  krbtgt/CORP.EXAMPLE.COM@CORP.EXAMPLE.COM
        renew until 10/10/22 10:56:58
  1. Connect to the database:
[oracle@ip-10-1-4-29 ~]$ sqlplus /@orcl.abcdefghijk.ap-southeast-1.rds.amazonaws.com:1521/ORCL

SQL*Plus: Release 19.0.0.0.0 - Production on Mon Oct 3 10:57:38 2022
Version 19.13.0.0.0

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

Last Successful login time: Mon Oct 03 2022 10:54:18 +00:00

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

SQL>
  1. Verify the user session information:
SQL> SHOW USER 
USER is "HRUSER"
SQL> SELECT SYS_CONTEXT('USERENV', 'CURRENT_USER') FROM DUAL;

SYS_CONTEXT('USERENV','CURRENT_USER')
--------------------------------------------------------------------------------
HRUSER
SQL> SELECT SYS_CONTEXT('USERENV', 'AUTHENTICATED_IDENTITY') FROM DUAL;

SYS_CONTEXT('USERENV','AUTHENTICATED_IDENTITY')
--------------------------------------------------------------------------------
bob@CORP.EXAMPLE.COM

Because Bob is a member of the group HRMgrGrp, upon successfully login, the role HRMGRROLE is automatically enabled for his session:

SQL> SELECT ROLE FROM SESSION_ROLES ORDER BY ROLE;

ROLE
--------------------------------------------------------------------------------
HRMGRROLE
  1. Retrieve additional context for the session:
SQL> SELECT SYS_CONTEXT('USERENV', 'AUTHENTICATION_METHOD') FROM DUAL;

SYS_CONTEXT('USERENV','AUTHENTICATION_METHOD')
--------------------------------------------------------------------------------
KERBEROS_GLOBAL
SQL> SELECT SYS_CONTEXT('USERENV', 'ENTERPRISE_IDENTITY') FROM DUAL;

SYS_CONTEXT('USERENV','ENTERPRISE_IDENTITY')
--------------------------------------------------------------------------------
cn=bob,ou=Users,ou=CORP,dc=corp,dc=example,dc=com
SQL> SELECT SYS_CONTEXT('USERENV', 'IDENTIFICATION_TYPE') FROM DUAL;

SYS_CONTEXT('USERENV','IDENTIFICATION_TYPE')
--------------------------------------------------------------------------------
GLOBAL SHARED
SQL> SELECT SYS_CONTEXT('USERENV', 'LDAP_SERVER_TYPE') FROM DUAL;

SYS_CONTEXT('USERENV','LDAP_SERVER_TYPE')
--------------------------------------------------------------------------------
AD

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:

CREATE USER ALICE IDENTIFIED GLOBALLY AS
'CN=alice,OU=Users,OU=CORP,DC=corp,DC=example,DC=com';

GRANT CREATE SESSION to ALICE;

After you successfully log in with user Alice, you can verify the session authentication information:

[oracle@ip-10-1-4-29 ~]$ okinit alice

Kerberos Utilities for Linux: Version 19.0.0.0.0 - Production on 03-OCT-2022 11:10:27

Copyright (c) 1996, 2019 Oracle.  All rights reserved.

Configuration file : /etc/krb5.conf.
Password for alice@CORP.EXAMPLE.COM: 
[oracle@ip-10-1-4-29 ~]$ sqlplus /@orcl.abcdefghijk.ap-southeast-1.rds.amazonaws.com:1521/ORCL

SQL*Plus: Release 19.0.0.0.0 - Production on Mon Oct 3 11:10:44 2022
Version 19.13.0.0.0

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


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

SQL> 

SQL> SELECT SYS_CONTEXT('USERENV', 'CURRENT_USER') FROM DUAL;

SYS_CONTEXT('USERENV','CURRENT_USER')
--------------------------------------------------------------------------------
ALICE


SQL> SELECT SYS_CONTEXT('USERENV', 'AUTHENTICATED_IDENTITY') FROM DUAL;

SYS_CONTEXT('USERENV','AUTHENTICATED_IDENTITY')
--------------------------------------------------------------------------------
alice@CORP.EXAMPLE.COM

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.

  1. Set the LDAP_DIRECTORY_SYSAUTH parameter to YES, and bounce the instance for the static parameter to take effect:
ALTER SYSTEM SET LDAP_DIRECTORY_SYSAUTH=YES SCOPE=SPFILE;
  1. Verify that the database password file exists and with version 12.2 format.
-bash-4.2$ orapwd FILE=$ORACLE_HOME/dbs/orapw${ORACLE_SID} FORMAT=12.2

Enter password for SYS:

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.

  1. Create the AD group AD_DBA_SYSDBA_USERS and add user admin to it with the following parameters:
    1. Create in – corp.example.com/CORP/Users
    2. Group name – AD_DBA_SYSDBA_USERS
    3. Group name (pre-Windows 2000) – AD_DBA_SYSDBA_USERS
    4. Group scope – Global
    5. Group type – Security
  2. Create the database global user AD_DBA_SYSDBA_USERS to map to the directory group AD_DBA_SYSDBA_USERS.
CREATE USER AD_DBA_SYSDBA_USERS IDENTIFIED GLOBALLY AS
'CN=AD_DBA_SYSDBA_USERS,OU=Users,OU=CORP,DC=corp,DC=example,DC=com';
GRANT SYSDBA TO AD_DBA_SYSDBA_USERS;

Members of this group can log in to the database as SYSDBA.

  1. Now you can connect to remotely using admin (a member of AD_DBA_SYSDBA_USERS) to log in to the database with SYSDBA privileges:
[oracle@ip-10-1-4-29 ~]$ okinit admin

Kerberos Utilities for Linux: Version 19.0.0.0.0 - Production on 03-OCT-2022 11:33:00

Copyright (c) 1996, 2019 Oracle.  All rights reserved.

Configuration file : /etc/krb5.conf.
Password for admin@CORP.EXAMPLE.COM: 
[oracle@ip-10-1-4-29 ~]$ sqlplus /@orcl.abcdefghijk.ap-southeast-1.rds.amazonaws.com:1521/ORCL as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Mon Oct 3 11:33:42 2022
Version 19.13.0.0.0

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


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

SQL> 
SQL> SELECT SYS_CONTEXT('USERENV', 'CURRENT_USER') FROM DUAL;

SYS_CONTEXT('USERENV','CURRENT_USER')
--------------------------------------------------------------------------------
SYS

SQL> SELECT SYS_CONTEXT('USERENV', 'AUTHENTICATED_IDENTITY') FROM DUAL;

SYS_CONTEXT('USERENV','AUTHENTICATED_IDENTITY')
--------------------------------------------------------------------------------
admin@CORP.EXAMPLE.COM

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.

alter system set events '28033 trace name context forever, level 9';

To disable the trace, run the following SQL statement:

alter system set events '28033 trace name context off';

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.