Oracle Database Security

Enterprise User Security with DB 12c and Active Directory using OUD as Proxy Server and Kerberos Authentication

By Ricardo Gutierrez, July 2017

Overview

Enterprise User Security (EUS) is a component of Oracle Database Enterprise Edition that over the years has brought enhanced features to address the challenges of managing users and security across organizations with multiple Oracle databases. The idea of centralizing the management of database users and authorizations has been always a key driver for most customers, although multiple are the configurations available in EUS, one common is the integration with Microsoft Active Directory (AD). In this article, we will review an approach to integrate EUS with AD while providing a single sign-on experience to database users using Kerberos authentication.

We can summarize the benefits of the integration in the following points:

Using existing AD infrastructure with EUS to centralize the management of database users and authorizations across multiples Oracle databases.
Use Windows AD credentials to access databases via single sign-on without having to synchronize users, change AD schema or deploy password plugins on domain controllers.

Additionally (no described in this article) organizations can also rely in the virtualization capabilities of Oracle Unified Directory (OUD) to consolidate fragmented identities across the organization, e.g. think about having a central place to access all different identity data sources available in an organization.

EUS with AD using OUD as Proxy Server and Kerberos Authentication

As pointed out earlier, EUS is available in the Enterprise Edition of Oracle Databases and usually deployed with an enterprise directory in organizations aiming to centralize the management of database users and security. EUS use the concept of enterprise domains along with enterprise users and roles as an abstraction layer that when combined with a user repository like Activate Directory enables a centralized approach for managing database security, e.g. on-boarding a user in AD, then assigning to this user one or more groups that will automatically grant the user access to one or more databases with an specific set of database privileges, no need to create a user on each database or manually assign privileges or set up new passwords, the new user will rely on his/her AD credentials to access the database.

A Sample Scenario

The previous example is quite interesting in terms of functionality and benefits but how can be this accomplished? so, let's assume that an organization relies on AD for managing all their employees, the same organization maintains several Oracle databases although management of database users and access is done individually on each database, a requirement is to centralize the management of database users across the organization leveraging the existing AD infrastructure for user management and database access.

Solution Overview

The following section outlines the main tasks that make up the solution to meet the requirement in our sample scenario:

Enable EUS in the database and the integration with AD to centralize the management of database users and security.
Use OUD proxy server as the entry point to AD. The addition of OUD allows the storing of EUS metadata that otherwise will require the use of AD to store the same information along with schema changes. Furthermore and since OUD will route data requests to AD, it can be configured to use either load balancing to data distribution or both (no described in this article).
Enable Kerberos authentication in the database to leverage AD credentials providing a single sign-on experience to users when accessing a database.

Figure 1. Solution Overview

Figure 1. Solution Overview

Assumptions and Prerequisites

The configuration steps in this article assume an organization with a new installation of Oracle Database Enterprise Edition and an existing Active Directory domain as the enterprise directory. The article covers the installation of the additional software components, including Oracle Enterprise Manager Cloud Control (EMCC), Oracle Unified Directory and the configuration of all different components to build the solution. The software products and versions used in this article are listed below, please be aware that other versions may require slight changes in the configuration or alternative patches. Note: please be aware that although EMCC is used in this article to demonstrate the configuration of EUS, there is an alternative command line utility EUSM available since the release 11.1 of the Oracle database that can be used to manage the EUS configuration. Please refer to Doc ID 1085065.1 in My Oracle Support for more details.

Oracle Database Enterprise Edition 12c (12.1.0.2.0) with Patch 19285025
Oracle Enterprise Manager Cloud Control 12c (12.1.0.3.0) Infrastructure
Oracle Enterprise Linux Server 6.1 64-bit
Microsoft Windows Server 2008 R2 with Service Pack 1 configured with one Active Directory domain
Oracle Unified Directory 11g Release 2 (11.1.2.3.0) with Patch 25383162

Summary of Configuration Steps

Installing Software Components

1. Installing Oracle Enterprise Manager Cloud Control 12c

Download Oracle Enterprise Manager Cloud Control (EMCC) 12.1.0.3.0 from Oracle Software Delivery Cloud. In our case the Linux distribution is made of 3 zip files.
Unzip the distribution files in a temporary folder. Note: for demonstration purposes we are deploying EMCC in the same DB server, however in a production environment this component should be deployed in a dedicated server.
E.g. run the following commands to unzip the files:
```
cd /home/oracle/Downloads

unzip V40468-01.zip
unzip V40469-01.zip
unzip V40470-01.zip
```
Installation of EMCC requires some libraries or packages specific to the target OS environment, please make sure to review the following link to verify if your OS contains the required libraries.
E.g. in our case, as root user we installed the following packages in Oracle Linux 6.1:
```
yum install make-3* -y

yum install binutils-2* -y 
yum install gcc-4* -y 
yum install libaio-0* -y 
yum install glibc-common-2* -y 
yum install libstdc++-4* -y 
yum install sysstat-* -y 
yum install glibc-devel -y 
yum install glibc-devel.i686 -y
```
Proceed to configure the require settings in the database. Here, we assumed that the new installation of the database server contains a default database named orcl. Note: this database will be used as the data store for EMCC, later we will create another database for the integration with EUS.
E.g. the following settings were applied along with a restart of the database orcl:
```
login / as sysdba

 
ALTER SYSTEM SET processes=300 SCOPE=SPFILE; 
ALTER SYSTEM SET session_cached_cursors=200 SCOPE=SPFILE;
ALTER SYSTEM SET sga_target=2G SCOPE=SPFILE;
ALTER SYSTEM SET shared_pool_size=600M SCOPE=SPFILE;
ALTER SYSTEM SET pga_aggregate_target=1G SCOPE=SPFILE;
ALTER SYSTEM SET job_queue_processes=20 SCOPE=SPFILE;
SHUTDOWN IMMEDIATE 
 STARTUP
```
Start the installation of EMCC. Note: make sure the database listener and target database (e.g. orcl) is running.
E.g. Login as oracle user and run the following commands:
```
cd /home/oracle/Downloads

./runInstaller
```
Uncheck the option to received security updates, and click Next to continue with the installation.
Select Skip updates, and click Next.
The prerequisites check will start, if successful click Next to continue.
Under Installation Type, select Create New Enterprise Manager System. Then, select Simple and click Next to continue.

Under Installation Details, enter the appropriated values for middleware and agent locations.

E.g. enter the following values:

Middleware Home Location    : /home/oracle/Middleware

Agent Base Directory        : /home/oracle/Agent
Host name                   : db12.oracledemo.com

Click Next to continue.

Under Configuration Details, enter the appropriate values for the target database environment.

E.g. enter the following values:

Administrator password      : Oracle123

Database hostname           : db12.oracledemo.com 
Port number                 : 1521 
Service/SID                 : orcl SYS 
Password                    : Oracle123 
Software Library Location   : /home/oracle/swlib

Click Next to continue.
Note: if you received a warning associated with "Check CBO Stats gathering job, Disable the stats gathering job", click Yes to auto fix the issue, then click OK to continue.
Review the summary and click Install to start the installation.
Note: if you are prompted to run a script as root user, then open another terminal session and run the requested script.
E.g. login as root and run the script:
```
/home/oracle/Middleware/oms/allroot.sh
```
Once the script is completed, go back to the installer and click OK to continue.
The installation summary will be displayed once the installation is completed, click Close to exit the installer. Note: make sure to write down the URL addresses to access EMCC and WebLogic console.

Start EMCC and test accessing the console.

E.g. As oracle user run the following command to start EMCC:

/home/oracle/Middleware/oms/install/unix/scripts/omsstup start

Access the EMCC console, e.g. use the following URL and credentials:



EMCC URL    : https://db12.oracledemo.com:7803/em 
User        : sysman 
Password    : Oracle123

2. Installing Oracle Unified Directory 11g R2

Download Java Runtime and Java SDK from the Oracle Technology Network.
E.g. in our Oracle Linux environment, we downloaded the following versions:
```
/home/oracle/Downloads/jre-7u80-linux-x64.rpm
/home/oracle/Downloads/jdk-7u80-linux-x64.rpm
```
Download Oracle Application Development Framework (ADF) 11g Release 1 (11.1.1.9.0) from the following link in Oracle Technology Network.
Download Oracle Unified Directory 11g Release 2 (11.1.2.3.0) from Oracle Software Delivery Cloud. We also downloaded Oracle WebLogic Server 11g Release 1 (10.3.6) to host the Oracle Directory Services Management console.
Download OUD Patch 25383162 from My Oracle Support.
Proceed to install Java Runtime and SDK.
E.g. login as root user and run the following commands. Note: this will install Java system-wide.
```
rpm -ivh jre-7u80-linux-x64.rpm 
rpm -ivh jdk-7u80-linux-x64.rpm 
```
Set the path and JAVA_HOME variable for the user who will perform the installation.
E.g. login as oracle and run the following commands to edit the bash profile:
```
cd ~ 
vi .bash_profile
```
Add the following lines and save the changes.
```
export JAVA_HOME=/usr/java/jre1.7.0_80 
path=$JAVA_HOME/bin:$PATH 
```
Source the new profile:
```
. .bash_profile
```

Installation of OUD requires some libraries or packages specific to the target OS environment, please make sure to review the following link to verify if your OS contains the required packages.

E.g. in our case, as root user we installed the following packages for Oracle Linux 6.1:



yum install compat-libcap1 
yum install libgcc 
yum install libstdc++ 
yum install gcc 
yum install gcc-c++ 
yum install libgcc.i686 
yum install libstdc++.i686 
yum install compat-libs* 
yum install libaio-devel 
yum install compat-libs*.i686 
yum install libXtst.x86_64 -y 
yum install libXtst.i686 -y

Set the shell limits for the target environment.
E.g. login as root user and edit the limits file:
```
vi /etc/security/limits.conf
```
Add or modify the following lines and save the changes. Note: these values are for demonstration purposes.
```
* soft nofile 4096 *
hard nofile 4096
```
Re-start the OS:
```
shutdown -r now
```

Start the installation of OUD.

E.g. login as oracle user and run the following commands:



cd /home/oracle/Downloads
unzip V75929-01.zip
cd Disk1
./runInstaller -jreLoc $JAVA_HOME

In the Inventory page, confirm the location of the inventory.
E.g. enter the following values:
```
Inventory Directory     : /home/oracle/oraInventory
OS Group name           : oracle
```
Click OK to continue.
Note: if you are prompted to run a script as root user, then open another terminal session and run the requested script.
E.g. login as root and run the script:
```
/home/oracle/oraInventory/createCentralInventory.sh
```
Once the script is completed, go back to the installer and click OK to continue.
In the Welcome page, click Next.
Select Skip Software Update and click Next to continue.
The prerequisites check will start, if successful click Next to continue.
Enter the middleware and home directory locations according to your environment.
E.g. enter the following values:
```
OUD Base Location       : /home/oracle/Middleware
Oracle Home Directory   : Oracle_OUD1
```
Click Next to continue.
Review the summary and click Install to start the installation.
Once the installation is completed, click Next and then Finish to exit the installer.

Proceed to apply patch 25383162.

E.g. login as oracle user and run the following commands:



cd /home/oracle/Downloads 
unzip p25383162_111230_Generic.zip 
cd 25383162 
export ORACLE_HOME=/home/oracle/Middleware/Oracle_OUD1

Run the following command to verify the OUD inventory:



/home/oracle/Middleware/Oracle_OUD1/OPatch/opatch lsinventory

In the previous command was successful, then proceed to apply the patch:



/home/oracle/Middleware/Oracle_OUD1/OPatch/opatch apply

Run again the following command to verify if the patch has been applied:



/home/oracle/Middleware/Oracle_OUD1/OPatch/opatch lsinventory

Note: the OUD software is installed, in the next sections we will create a proxy instance in OUD to be integrated with Active Directory.

3. Installing Oracle Directory Services Management Console

The Oracle Directory Services Management Console (ODSM) is part of the OUD distribution and provides a Web interface for managing OUD, this is an alternative to the out-of-the-box command line interface available in OUD. In order to deploy ODSM, we must first install Oracle WebLogic Server.
Unzip the distribution file for WebLogic Server in a temporary folder.
E.g. run the following commands to unzip the file:
```
cd /home/oracle/Downloads
unzip V29851-01.zip
```
Start the installation of WebLogic Server.
E.g. Login as oracle user and run the following commands:
```
chmod +x wls1036_linux32.bin
./wls1036_linux32.bin
```
The previous command will start the installer in text mode. Enter Next to continue.
Enter the middleware location, this must be the same as the middleware home for OUD.
E.g. enter the following location:
```
/home/oracle/Middleware
```
Enter Next to continue.
Enter Next again to proceed with the installation.
Enter Next, then 3 and then No to bypass the updates.
Enter 5 to remain informed.
Select 1 for typical installation.
Confirm the installation of products WebLogic and Coherence.
Review the summary and enter Next to start the installation.
Once the WebLogic Server installation is completed, proceed to install the ADF Runtime. To do so, unzip the distribution file.
E.g. run the following commands to unzip the file:
```
cd /home/oracle/Downloads
unzip ofm_appdev_generic_11.1.1.9.0_disk1_1of1.zip
```
Start the installation of the ADF Runtime.
E.g. Login as oracle user and run the following commands:
```
cd /home/oracle/Downloads/Disk1
./runInstaller -jreLoc $JAVA_HOME
```
In the Welcome page, click Next.
Select Skip Software Updates and click Next to continue.
The prerequisites check will start, if successful click Next to continue.
In Specify Software Location, enter the middleware location according to your environment.
E.g. enter the following value:
```
/home/oracle/Middleware
```
Click Next to continue.
In Application Server, select Weblogic Server and click Next.
In Review Installation, review the summary and click Install to start the installation.
Once completed, click Next and then Finish to exit the installer.
Proceed to create a WebLogic domain to deploy ODSM. To do so, start the WebLogic configuration wizard.
E.g. as oracle user run the following command:
```
/home/oracle/Middleware/oracle_common/common/bin/config.sh
```
Select Create a new WebLogic domain and click Next.
In Select Domain Source, select Oracle Directory Services Manager - 11.1.2.3.0 and click Next.
In Specify Domain Name and Location, enter a domain name and location.
E.g. enter the following values:
```
Domain name     : oud_domain
Location        : /home/oracle/Middleware/user_projects/domains
```
Click Next to continue.
In Configure Administrator User Name and Password, specify the appropriate values.
E.g. enter the following values:
```
Administrator   : weblogic
Password        : Oracle123
```
Click Next to continue.
In Configure Server Start Mode and JDK, specify the JDK location and start mode.
E.g. select Production Mode and enter the JDK location.
```
JDK Location    : /usr/java/jdk1.7.0_80
```
Click Next to continue.
In Optional Configuration, click Next to continue.
In Configuration Summary, review the parameters and click Create.
Once the creation is completed click Done to exit the wizard.
Create a boot.properties file to start WebLogic Server with the administrator credentials.
E.g. as oracle user create a boot.properties file:
```
cd /home/oracle/Middleware/user_projects/domains/oud_domain/servers/AdminServer/security
vi boot.properties
```
Add the following lines and save the file.
```
username=weblogic
password=Oracle123
```
Start WebLogic Server and ODSM.
E.g. as oracle user run the following command:
```
/home/oracle/Middleware/user_projects/domains/oud_domain/bin/startWebLogic.sh
```
Access the ODSM console using the following URL. Note: at this point we haven't created an OUD instance yet and therefore we do not have a set of credentials that we can use, we will accomplish this task in the next sections.
```
ODSM URL    : http://oud.oracledemo.com:7001/odsm
```

4. Installing Patch 19285025 on Oracle Database 12c

This document assumes an Oracle Database 12c (12.1.0.2.0) with patch 19285025. If the patch hasn't been installed, proceed to download Patch 19285025 from My Oracle Support. Make sure to select the correct release number and target platform before downloading the file (e.g. Release Oracle 12.1.0.2.0, Platform Linux x86-64).
Once the download is completed, unzip the file and read the instructions in the README.txt file to install the patch.
Note: the above patch fix an error in 12c associated with SSL handshake when connecting to LDAP servers, including OUD. An alternative workaround can be applied by enabling SSLv3 in OUD however this is only applicable to demo or development environments and not recommended for production.
E.g. login as oracle user and stop any OUD instance that maybe running:
```
/home/oracle/Middleware/asinst_1/OUD/bin/stop-ds
```
Proceed to re-enable SSLv3 by commenting out the following line in file java.security:
```
JAVA_HOME=/usr/java/jre1.7.0_80
vi $JAVA_HOME/lib/security/java.security

=> FROM
jdk.tls.disabledAlgorithms=SSLv3

=> TO
#jdk.tls.disabledAlgorithms=SSLv3
```
Start the OUD instance:
```
/home/oracle/Middleware/asinst_1/OUD/bin/start-ds
```

Configuring Components in the Integration

1. Creating Sample Database and Registration with EMCC

Start Database Configuration Assistance to create a sample database. Note: this database will be used with EUS and the integration with AD.
E.g. as oracle user run the following commands:
```
cd $ORACLE_HOME/bin
./dbca
```
Select Create Database and click Next to continue.
Select Advance Mode and choose template General Purpose or Transactional Processing, click Next to continue.

Enter the details for the new database.

E.g. enter the following values:



Global DB Name  : db12c
SID             : db12c
Uncheck Create as Container DB

Under Management Option, uncheck both Config EM and Register with EM. Click Next to continue.
Under Database Credentials, select Use the Same Admin Password for All Accounts (SYS and SYSTEM) and enter a password.
E.g. enter the following value:
```
Password        : Oracle123
```
Under Network Configuration, check the existing listener, e.g. Name: LISTENER, Port: 1521, /home/oracle/app/oracle/product/12.1.0/dbhome_1. Click Next to continue.

Under Storage Locations, enter the appropriate values.

E.g. enter the following values:

Storage Type        : File System

DB File Location    : {ORACLE_BASE}/oradata
Fast Recovery Area  : {ORACLE_BASE}/fast_recovery_area

Click Next to continue.
Under Database Options, select Sample Schemas and click Next to continue. Note: left DB Vault and Label Security tabs with unchecked options.
Under Initialization Parameters, select the character set and connection mode.
E.g. enter the following values:
```
DB Character Set    : WE8MSWIN1252
Connection Mode     : Dedicated Server Mode
```
Left other settings with the default values and click Next to continue.
Under Creation Options, left the default settings and click Next.
Review the Summary and click Finish to exit the wizard.
Re-start the Listener to refresh the new TNS name.
E.g. as oracle user run the following commands:
```
cd $ORACLE_HOME/bin
./lsnrctl stop
./lsnrctl start
```

Connect to the new database and unlock the sample schemas.

E.g. as oracle user run the following command:

. oraenv

When prompted, enter db12c.

Proceed to run the following commands:



echo $ORACLE_SID
sqlplus / as sysdba
ALTER USER hr ACCOUNT UNLOCK IDENTIFIED BY Oracle123;
ALTER USER dbsnmp ACCOUNT UNLOCK IDENTIFIED BY Oracle123;

Perform a quick test.

E.g. as oracle user run the following commands:



cd $ORACLE_HOME/bin
sqlplus hr/Oracle123@db12c
SELECT table_name FROM user_tables;
SELECT USERNAME FROM ALL_USERS ORDER BY USERNAME;
exit

Open a browser and login to the EMCC console.

E.g. use the following URL address and credentials:



EMCC URL    : https://db12.oracledemo.com:7803/em
User        : sysman
Password    : Oracle123

From EMCC home page, select Setup -> Add Target -> Add Targets Manually.
Under the Add Targets Manually page, select Add Targets Using Guided Process
Select as target type Oracle Database, Listener and Automatic Storage Management
Click on Add Using Guided Process.
Under Database Discovery: Search Criteria, click search and select db12.oracledemo.com, click Next to continue.
Under Database Discovery: Results, select db12c and enter the Monitor Password (e.g. Oracle123). Click Test Connect.
Select LISTENER_db12.oracledemo.com and click Next to continue.
Under Database Discovery: Review, confirm the settings, click Save and then Close.
Verify if the new database has been added as target, to do so from the EMCC home page, select Targets -> Databases.
Select Search List. Note: click the Refresh icon if the status for the database shows as pending until the status changes to green color (arrow up).
Click on database db12c.
In the db12c page, make sure the menu list the option: Security -> Enterprise User Security.
Note: clicking on the EUS option will prompt to enter the LDAP connection details, this will be configured later along with OUD and EUS configuration.

2. Creating OUD Proxy Server Instance and Enabling EUS

In this demonstration, we assume that all servers participating in the integration are reachable by their FQDN. If no DNS service is available make sure to add the appropriate entries in the hosts file of each server participating in the integration.
E.g. in our demo environment we have 3 servers with the following entries on each server hosts file.
```
192.168.0.95  dalila.other.com                  # Domain controller
192.168.0.96  db12.oracledemo.com db12          # Database server
192.168.0.97  oud.oracledemo.com oud            # OUD Proxy server
```
Start the OUD Proxy Configuration wizard to create a new proxy instance.
E.g. as oracle user run the following commands:
```
cd /home/oracle/Middleware/Oracle_OUD1
./oud-proxy-setup
```
In the Welcome page, click Next to continue.

In Server Settings, enter the appropriate values for the new instance.

E.g. enter the following values:

Host Name       : oud.oracledemo.com

LDAP Port       : 1389
LDAP Secure     : Enable SSL
Port            : 1636
Certificate     : Generate Self-Signed for Testing
Admin Port      : 4444
Root User DN    : cn=Directory Manager
Password        : Oracle123

Click Next to continue.
In Deployment Options, select Configure EUS (Enterprise User Security) and click Next.
In Back Server Type, select as server type Microsoft Active Directory and click Next.
In Back-End Servers, click Add Server to specify the AD server information.
E.g. enter the following values:
```
Hostname        : dalila.other.com
Protocol        : LDAP
Port            : 389
```
Click Add and then Close. Then click Next to continue.
In Naming Contexts, enter or select the appropriate value for the AD domain. Note: you must replace the value in this step and further commands with the corresponding value of your target AD domain.
E.g. for our AD domain other.com, we selected the following value:
```
Naming Context DN   : dc=other,dc=com
```
Note: make sure this is the only value selected or entered.
Once the value is selected or entered (via the Add button) click Next to continue.
In Runtime Options click Next to continue.
In Review verify the selections and click Finish to start the creation of the new instance.
Once the process is completed, click Close to exit the wizard.
Note: by default the new instance is created in the middleware home directory.
E.g. location of the proxy instance:
```
/home/oracle/Middleware/asinst_1/OUD
```
If the instance wasn't started by the tool, you can proceed to start the new proxy server instance.
E.g. as oracle user run the following command:
```
/home/oracle/Middleware/asinst_1/OUD/bin/start-ds
```
Proceed to add the new instance to ODSM console.
E.g. if ODSM hasn't started yet, as oracle user run the following command:
```
/home/oracle/Middleware/user_projects/domains/oud_domain/bin/startWebLogic.sh
```
Open a browser and enter the ODSM URL:
```
http://oud.oracledemo.com:7001/odsm
```

Proceed to add a connection for the new proxy server instance.

E.g. enter the following values:



Name        : Proxy1
Server      : oud.oracledemo.com
Admin Port  : 4444
SSL Enable  : [checked]
User        : cn=Directory Manager
Pass        : Oracle123

Click Login and then Yes, trust always to always trust the Server Certificate. The ODSM console for the new instance is displayed. Verify in the Home tab the connection handlers and naming contexts are correct.
Proceed to configure the proxy workflow elements, remote root DN and remote root user accounts.
E.g. as oracle user run the following command to create a temporary password file.
```
vi /home/oracle/pwd.txt
```
Add the admin password for the proxy instance and save the file:
```
Oracle123
```
Run the following commands to configure the workflow elements and remote root properties.
```
cd /home/oracle/Middleware/Oracle_OUD1/bin
./dsconfig set-workflow-element-prop --element-name proxy-we1 --set remote-root-dn:cn=administrator,cn=users,dc=other,dc=com
--set remote-root-password:******* --hostname localhost --port 4444 --trustAll --bindDN "cn=directory manager" --bindPasswordFile /home/oracle/pwd.txt --no-prompt
```
Note: the remote-root-dn value must be an AD domain administrator or an AD user with privileges to access the AD domain.

Configure the proxy workflow elements for the external AD with use-client-identity.

E.g. as oracle user run the following commands:



cd /home/oracle/Middleware/Oracle_OUD1/bin
./dsconfig set-workflow-element-prop --element-name proxy-we1 --add exclude-list:cn=directory\ manager --add exclude-list:cn=oraclecontext,dc=other,dc=com --set remote-ldap-server-bind-dn:cn=administrator,cn=users,dc=other,dc=com
--set remote-ldap-server-bind-password:******* --hostname localhost --port 4444 --trustAll --bindDN "cn=directory manager" --bindPasswordFile /home/oracle/pwd.txt --no-prompt

Note: the remote-ldap-server-bind-dn value must be an AD domain administrator or an AD user with privileges to access the AD domain.

3. Modifying OUD Proxy Server Configuration for EUS

Use the sample LDIF template to update the realm information in OUD.
E.g. as oracle user make a copy of the sample LDIF template located in the OUD config folder:
```
cp /home/oracle/Middleware/Oracle_OUD1/config/EUS/modifyRealm.ldif /home/oracle/modifyRealm.ldif
```
Edit the copy as follows:
- Replace dc=example,dc=com with the correct naming context for the AD domain. E.g. in our case we replace it with dc=other,dc=com
- Replace ou=people and ou=groups with the correct location of the user and group entries in the AD domain. E.g. in our case we replace both with ou=employees, this is because our demo environment for AD stores all users and groups in the OU employees.

Create the additional LDIF files.

E.g. as oracle user create the following LDIF file:

vi /home/oracle/orclcommonNickTosamaaccountname.ldif

Add the following lines and save the file:



dn: cn=Common,cn=Products,cn=OracleContext,dc=other,dc=com
changetype: modify
replace: orclCommonNicknameAttribute
orclCommonNicknameAttribute: sAMAccountName

Create another LDIF file:



vi /home/oracle/orclcommonkrbprincipalattributeToUserprincipalname.ldif

Add the following lines and save the file:



dn: cn=Common,cn=Products,cn=OracleContext,dc=other,dc=com
changetype: modify
replace: orclcommonkrbprincipalattribute
orclcommonkrbprincipalattribute: userprincipalname

Proceed to update the OUD configuration.

E.g. as oracle user run the following commands:



cd /home/oracle/Middleware/asinst_1/OUD/bin
./ldapmodify -h oud.oracledemo.com -p 1389 -D "cn=Directory Manager" -w Oracle123 -f /home/oracle/modifyRealm.ldif
./ldapmodify -h oud.oracledemo.com -p 1389 -D "cn=Directory Manager" -w Oracle123 -f /home/oracle/orclcommonNickTosamaaccountname.ldif
./ldapmodify -h oud.oracledemo.com -p 1389 -D "cn=Directory Manager" -w Oracle123 -f /home/oracle/orclcommonkrbprincipalattributeToUserprincipalname.ldif

Create an LDIF file to enable anonymous access in AD.

E.g. as oracle user create the following LDIF file:



vi /home/oracle/anonymousLogin.ldif

Add the following lines and save the file:



dn: cn=directory service,cn=windows nt,cn=services,cn=configuration,dc=other,dc=com
changetype: modify
replace: dsHeuristics
dsHeuristics: 0000002

Run the following commands:



cd /home/oracle/Middleware/asinst_1/OUD/bin
./ldapmodify -h dalila.other.com -p 389 -D "cn=administrator,cn=users,dc=other,dc=com" -w ******* -f /home/oracle/anonymousLogin.ldif

4. Configuring Oracle DB for OUD Proxy Server

Set the os_authent_prefix in the target database db12c.
E.g. as oracle user run the following command:
```
. oraenv
```
When prompted, enter db12c.

Proceed to run the following commands:
```
cd $ORACLE_HOME/bin
sqlplus / as sysdba
SQL> SHOW PARAMETER os_authent_prefix;
SQL> ALTER SYSTEM SET os_authent_prefix='' scope=spfile;
```
Start the Network Configuration Assistant to update the network and directory configuration in the database.
E.g. as oracle user run the following commands:
```
cd $ORACLE_HOME/bin
./netca
```
Select Directory Usage Configuration and click Next to continue.
Select Oracle Internet Directory and click Next.
Enter the details for the OUD proxy server.
E.g. enter the following values:
```
Hostname    : oud.oracledemo.com
Port        : 1389
SSL Port    : 1636
```
Click Next, then in Oracle Context, select the following value:
```
Context     : cn=OracleContext,dc=other,dc=com
```
Click Next to continue.
Confirm the distinguished name, click Next and then Finish to exit the wizard.
Note: the wizard creates a configuration file ldap.ora in $ORACLE_HOME/network/admin folder, e.g.
```
/home/oracle/app/oracle/product/12.1.0/dbhome_1/network/admin/ldap.ora
```
With a content similar to the following snippet:
```
DIRECTORY_SERVERS= (oud.oracledemo.com:1389:1636)

DEFAULT_ADMIN_CONTEXT = "dc=other,dc=com"

DIRECTORY_SERVER_TYPE = OID
```
Start the Database Configuration Assistant to register the database.
E.g. as oracle user run the following commands:
```
cd $ORACLE_HOME/bin
./dbca
```
In Database Operation page, select Configure Database Options and click Next to continue.
In Database list, select db12c and provide the database credentials.
E.g. enter the following values:
```
Username    : SYS
Password    : Oracle123
```
Click Next to continue.
In Directory Service click Yes, register the database, provide the OUD proxy server credentials and set the wallet password.
E.g. enter the following values:
```
User DN         : cn=Directory Manager
Password        : Oracle123
Wallet password : Oracle123
```
Click Next to continue.
In Database Options (Database Components, Sample Schemas) click Next to continue.
In Database Vault, uncheck both Configure Database Vault and Configure Label Security. Click Next to continue.
In Connection Mode, select Dedicated Server Mode and click Next to continue.
Review the Summary, click Finish to start the configuration.
Upon completion, click Close to exit the wizard.
Note: check if auto-login for the database wallet is enable by verifying the presence of file cwallet.sso. E.g.:
```
/home/oracle/app/oracle/admin/db12c/wallet/cwallet.sso
```

5. Configuring Oracle DB with Kerberos Authentication and AD

Before configuring Kerberos we will create some test users and groups in the AD domain, these will be later used to demonstrate how to configure EUS and then to test the integration.
Login as administrator to the Windows domain controller.

Open the Active Directory Users and Computers tool to create 3 sample users and groups.

E.g. use the following values to create 3 sample users:



First Name  Last Name   Logon name/Login    Password    Pwd Never Expired   User Must change Pwd
----------  ---------   ----------------    ---------   -----------------   --------------------
Ana         Smith       asmith              Oracle123   [checked]           [unchecked]
Robert      Green       rgreen              Oracle123   [checked]           [unchecked]
Ted         Turner      tturner             Oracle123   [checked]           [unchecked]

E.g. use the following values to create 3 sample groups:



Group name      Scope       Type
-------------   ----------- --------------
ora_connect     Global      Security
ora_resource    Global      Security
ora_dba         Global      Security

Assign the users to the groups as follow:



User            Groups
-------------   -------------------------------
asmith          [none]
rgreen          ora_connect, ora_resource
tturner         ora_connect, ora_dba

Login as administrator to the Windows domain controller and create a Kerberos principal and keytab file.
E.g. as domain administrator, open a command window and run the following commands:
```
dsadd user "cn=db12.oracledemo.com,ou=employees,dc=other,dc=com" -pwd "Oracle123" -samid "db12" -display "db12" -fn "db12" -ln "db12" -upn "db12.oracledemo.com@other.com" -pwdneverexpires yes
ktpass -princ oracle/db12.oracledemo.com@OTHER.COM -mapuser OTHER\db12 -pass Oracle123 -crypto AES256-SHA1 -ptype KRB5_NT_PRINCIPAL -kvno 3 -out c:\temp\db12.keytab
```
Note: in the first command, the user name must be the FQDN of the database server or an alias. In our case we are using db12.oracledemo.com that correspond to the database server. The second command adds oracle to the principal name, this name will be referred by the database as the Kerberos service name.
Using the Active Directory Users and Computers tool, edit the user account created in the previous step (e.g. db12.oracledemo.com) and set the appropriate account options for Kerberos.
E.g. set the following options for the user account:
```
Do not require Kerberos preauthentication   : [checked]
This account supports Kerberos AES 128 bit  : [checked]
This account supports Kerberos AES 256 bit  : [checked]
```

Update the service file as needed in the Windows domain controller.

E.g. edit file C:\Windows\System32\drivers\etc\services and update the following line:



=> From
kerberos           88/tcp    krb5 kerberos-sec              #Kerberos
=> To
kerberos           88/tcp    kerberos5 krb5 kerberos-sec    #Kerberos

Verify the Kerberos realm in the target AD domain.

E.g. as domain administrator run the following command:



nslookup -type=any _kerberos._tcp

The result may look similar to the following snippet:



C:\Users\Administrator>nslookup -type=any _kerberos._tcp
Server:  localhost
Address:  127.0.0.1
_kerberos._tcp.other.com        SRV service location:
priority       = 0
weight         = 100
port           = 88
svr hostname   = dalila.other.com
dalila.other.com        internet address = 192.168.0.95

Create a Kerberos configuration file in the database server using the realm information.

E.g. as oracle user create a configuration file krb5.conf in a temporary folder.



vi /home/oracle/krb5.conf

Add the following content according to the target Kerberos realm:



[logging]
default = FILE:/var/log/krb5libs.log
kdc = FILE:/var/log/krb5kdc.log
admin_server = FILE:/var/log/kadmind.log

[libdefaults]
default_realm = OTHER.COM
dns_lookup_realm = false
dns_lookup_kdc = false
ticket_lifetime = 24h
renew_lifetime = 7d
forwardable = true

[realms]
OTHER.COM = {
kdc = dalila.other.com:88
admin_server = dalila.other.com:749
default_domain = other.com
}

[domain_realm]
.other.com = OTHER.COM
other.com = OTHER.COM

As root user copy the krb5.conf file to the /etc folder.



/etc/krb5.conf

Copy the keytab file generated in the previous steps to the database server.
E.g. as oracle user copy the keytab file to the following location:
```
/home/oracle/app/oracle/product/12.1.0/dbhome_1/network/admin/db12.keytab
```

Update the sqlnet.ora file in the database server.

E.g. as oracle user edit the sqlnet.ora file:



vi /home/oracle/app/oracle/product/12.1.0/dbhome_1/network/admin/sqlnet.ora

Update the content accordingly, the following is the configuration in our demo environment:



NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT, HOSTNAME)

ADR_BASE = /home/oracle/app/oracle 
SQLNET.KERBEROS5_KEYTAB=/home/oracle/app/oracle/product/12.1.0/dbhome_1/network/admin/db12.keytab 
SQLNET.KERBEROS5_CONF=/etc/krb5.conf 
SQLNET.KERBEROS5_CONF_MIT=TRUE 
SQLNET.AUTHENTICATION_KERBEROS5_SERVICE=oracle 
SQLNET.AUTHENTICATION_SERVICES=(BEQ,KERBEROS5) 

WALLET_LOCATION=   
  ( SOURCE=     
   (METHOD = FILE)     
   (METHOD_DATA =        
    (DIRECTORY=/home/oracle/app/oracle/admin/db12c/wallet)     
    ) 
)

Verify the Kerberos configuration.

E.g. as oracle user run the following command:



. oraenv

When prompted, enter db12c.

Run the following command to request a Kerberos ticket:



kinit -k -t /home/oracle/app/oracle/product/12.1.0/dbhome_1/network/admin/db12.keytab oracle/db12.oracledemo.com@OTHER.COM

Check if a successful Kerberos ticket was generated by running the following command:



klist

The command output should be similar to the following snippet:



Ticket cache: FILE:/tmp/krb5cc_500
Default principal: oracle/db12.oracledemo.com@OTHER.COM

Valid starting     Expires            Service principal
07/12/17 13:09:13  07/12/17 23:09:06  krbtgt/OTHER.COM@OTHER.COM
renew until 07/19/17 13:09:13

Compare the kvno numbers in the Kerberos configuration.

E.g. as oracle user run the following command:



kvno oracle/db12.oracledemo.com@OTHER.COM
  => RESULT 
oracle/db12.oracledemo.com@OTHER.COM: kvno = 3

Compare the kvno number with the result in the following command. Note: the numbers should match otherwise you need to regenerate the keytab file.



klist -k /home/oracle/app/oracle/product/12.1.0/dbhome_1/network/admin/db12.keytab
=> RESULT
KVNO Principal
---- --------------------------------------------------------------------------
3 oracle/db12.oracledemo.com@OTHER.COM

Destroy the ticket by running the following command:



okdstry

6. Configuring EUS with Enterprise Manager

In this section we describe how to configure EUS with the target database db12c. EMCC is used to define a sample shared schema, global roles and appropriated mappings that demonstrate the EUS approach to centralize the management of users and security.
Login to EMCC as sysman/Oracle123 to create a sample shared schema.
Go to Targets -> Databases and select target database db12c.
Once the database page is displayed, select Security -> Users -> Create, proceed to enter the values to create a new user.
E.g. enter the following values:
```
Name            : GLOBAL_USER
Profile         : Default
Authentication  : Global
User Tablespace : USERS
Temp Tablspace  : TEMP
```
Make sure to remove the default CONNECT role, and click OK to confirm the creation.
Create sample global roles. Go to Targets -> Databases and select target database db12c.

Once the database page is displayed, select Security -> Roles -> Create, proceed to enter the values to create 3 new global roles.

E.g. use the following values:



Name            Authentication      Role
-------------   ------------------  ---------------
G_CONNECT       Global              CONNECT
G_DBA           Global              DBA
G_RESOURCE      Global              RESOURCE

Define EUS mappings. Go to Targets -> Databases and select target database db12c.

Once the database page is displayed, select Security -> Enterprise User Security.

E.g. enter the connection details for the OUD proxy server:



Host        : oud.oracledemo.com
Port        : 1389
Realm       : dc=other,dc=com
User        : cn=Directory Manager
Pass        : Oracle123

Click Login to continue.
Once the EUS page is displayed, select Manage Enterprise Domains and select Oracle Default Domain.
Click Configure to define the user schema mapping.
Select User - Schema Mappings and click Create. Add the appropriate mapping.
E.g. enter the following values:
```
User Name               Mapping Type        To Schema
--------------------    ------------------- ----------------
employees               SUBTREE             global_user
```
Note: employees correspond to the user and group base DN (ou=employees,dc=other,dc=com) specified in the LDIF template during the configuration of the OUD proxy server.
Define Enterprise Roles mappings. Select Enterprise Roles tab and click Create to define a new enterprise role.
Enter e_connect as the name and click Add, then provide the database connection details.
E.g. use the following values:
```
Database        : db12c
User Name       : sys as sysdba
Password        : Oracle123
```
Click Go and select G_CONNECT.
Click on Grantees tab, click Add, select GROUP in view and click GO.
Select ora_connect, click Continue and then OK. Note: ora_connect is one of the AD groups created in the previous steps.

Repeat the previous steps to create two additional enterprise roles.

E.g. use the following values:



Name            Global Role         Grantee (select GROUP as type)
--------------- ------------------  ---------------------------------
e_dba           G_DBA               ora_dba
e_resource      G_RESOURCE          ora_resource

Make sure to save the changes in the Enterprise User Security page.

Testing the Solution

In order to test the solution we will execute 3 different use cases. In all cases, users will single sign-on to the database after they have been authenticated with their AD credentials via Kerberos authentication. The main purpose is to demonstrate the different access levels or permissions granted to users based on their AD group membership, as well as showing that no users need to be created in the database as in all cases the AD users will be mapped to a shared schema while accessing the database.

A summary of the users and their groups membership is listed below as a reference to run the tests.



Name            UserID          Groups                      Password
--------------  ------------    -------------------------   -------------
Ana Smith       asmith          [no group]                  Oracle123
Robert Green    rgreen          ora_connect, ora_resource   Oracle123
Ted Turner      tturner         ora_connect, ora_dba        Oracle123

1. Testing database access with NO privileges

User Ana Smith is an employee with valid AD credentials who tries to access the database db12c.
E.g. Ana run the following command to set the Oracle environment:
```
. oraenv
```
When prompted, enter db12c.

Then, proceed to authenticate with AD via Kerberos authentication.
```
kinit asmith
```
When prompted, she enters her AD password Oracle123. Note: she can verify the current Kerberos session by running the following command:
```
klist
```
Now, Ana tries to access the database using the SQLPlus tool. Note: no user credentials are provided when invoking the tool.
```
sqlplus /@db12c
```
Although she has a valid Kerberos ticket, no having a valid group membership stop her from accessing the database. She will get an error similar to the following snippet:
```
ERROR:
ORA-01045: user GLOBAL_USER lacks CREATE SESSION privilege; logon denied
```
Run the command okdstry to clean up the Kerberos cache before continue with the next test.

2. Testing database access with DBA privileges

User Ted Turner is an employee with valid AD credentials who tries to access the database db12c.
E.g. Ted run the following command to set the Oracle environment:
```
. oraenv
```
When prompted, enter db12c.

Then, proceed to authenticate with AD via Kerberos authentication.
```
kinit tturner
```
When prompted, he enters his AD password Oracle123. After successful authentication he tries to access the database using the SQLPlus tool.
```
sqlplus /@db12c
```
Since Ted belongs to the ora_connect group in AD, he's able to connect to the database. This is possible because the enterprise role e_connect was granted to this group and linked to global role G_CONNECT which gives users privileges to establish a session with the database.

Now, Ted run the following PL/SQL commands against the database:
```
select * from session_roles;
create view role_view AS select * from session_roles;
drop view role_view;
```
Ted is able to run all the commands successfully because he also belongs to the ora_dba group in AD. This group has been granted the enterprise role e_dba that is linked to global role G_DBA which gives users privileges as DBA.
Run the command okdstry to clean up the Kerberos cache before continue with the next test.

3. Testing database access with CONTROLLED privileges

User Robert Green is an employee with valid AD credentials who tries to access the database db12c.
E.g. Robert run the following command to set the Oracle environment:
```
. oraenv
```
When prompted, enter db12c.

Then, proceed to authenticate with AD via Kerberos authentication.
```
kinit rgreen
```
When prompted, he enters his AD password Oracle123. After successful authentication he tries to access the database using the SQLPlus tool.
```
sqlplus /@db12c
```
Since Robert belongs to the ora_connect group in AD, he's able to connect to the database.

Now, Robert run the following PL/SQL commands against the database:
```
select * from session_roles;
create view role_view AS select * from session_roles;
```
The first command run successfully, however the second command returns an error. This is because Robert belongs to the ora_resource group in AD, and this group has been granted the enterprise role e_resource that is linked to global role G_RESOURCE which gives users privileges to query data but no to create or delete objects in the database.
Run the command okdstry to clean up the Kerberos cache.

Conclusion

Enterprise User Security is a solution that addresses many of the security challenges found in customers managing multiple Oracle databases, it does so by centralizing storage and management of user-related information in an enterprise directory service. In this article we have learned about a common approach to deploy EUS with Active Directory.

About the Author

Ricardo Gutierrez is a Senior IT Consultant with Oracle, focusing in research, development and solution engineering in the areas of IDaaS, CASB, Identity and Access Management, Identity Analytics, Identity Governance, Enterprise SSO, Federation, Privileged Account Management, Database and Application Security. With over 25 years of experience working with several technologies, Ricardo has spent the last 13 years working with the full suite of security products from IBM, Microsoft and Oracle. He is also a PMP, CCSP and VMware Certified Professional, and has published several white papers, articles, and training material covering security and cloud computing.