Centralized user authentication and authorization for the Oracle Databases with the following requirements:
For above use case, the solution is to configure databases for Kerberos authentication and deploy the Oracle Unified Directory (OUD) proxy for AD with Enterprise User Security for authorization.
Active Directory & Kerberos Server (Microsoft KDC):
Oracle Unified Directory:
Oracle Database:
Oracle Client:
testuser1, which will be used to connect to DB from client
Second for Oracle DB server,
→ Check password never expires option for sever principal Check password never expires option for sever principal
The final step on the Windows 2008 R2 server is to extract a key table for the database server principal. This is done using the ktpass tool.
ktpass.exe -princ oracle/db1.ziontech.demo@ZIONTECH.DEMO -mapuser ZIONTECHDEMO\db1.ziontech.demo -crypto all -pass Welcome9 -out c:\keytab
The resulting keytab file should then be transferred to the machine running Oracle
There is a process on how to validate keytab (read this post). Using this approach, we can avoid lot of troubleshooting upfront by ensuring that the KVNO value is compatible.
As mentioned in above referenced post, perform following ldapsearch query to obtain msDS-KeyVersionNumber from Active Directory.
ldapsearch -h ad.ziontech.demo -p 389 -D "testuser1@ziontech.demo" -w "Welcome1" -b "DC=ziontech,DC=demo" -s sub servicePrincipalName=oracle/db1.ziontech.demo msDS-KeyVersionNumber
The output will look like:
CN=db1,CN=Users,DC=ziontech,DC=demo msDS-KeyVersionNumber=6
Then, increment value of msDS-KeyVersionNumber by 1 and pass it as a value to the parameter -kvno in ktpass command.
We can even specify specific supported algorithm(s) for crypto parameter and use this version of ktpass instead of above.
ktpass.exe -princ oracle/db1.ziontech.demo@ZIONTECH.DEMO -mapuser ZIONTECHDEMO\db1.ziontech.demo -crypto AES256-CTS-HMAC-SHA1-96 -pass Welcome9 -ptype KRB5_NT_PRINCIPAL -kvno 7 -out c:\keytab
→ Create a group called g1 and add testuser1 to it.
Pretty much standard installation. Accept all defaults.
$MW_HOME/Oracle_OUD1/oud-proxy-setup
→ Ensure, to enable SSL In server settingsEnsure, to enable SSL In server settings
→ Select Configure EUS under deployment optionsSelect Configure EUS under deployment options
→ Select Microsoft Active Directory as the backend-server typeSelect Microsoft Active Directory as the backend-server type
→ Add AD server instanceAdd AD server instance
→ Select or specify Naming ContextSelect or specify Naming Context
→ FinishFinish
Configure the proxy workflow elements using dsconfig tool located in $MW_HOME/Oracle_OUD1/bin
dsconfig set-workflow-element-prop \ --element-name proxy-we1 \ --set remote-root-dn:CN=Administrator,CN=Users,DC=ziontech,DC=demo \ --set remote-root-password:Welcome1 \ --hostname localhost \ --port 6444 \ --trustAll \ --bindDN cn=Directory\ Manager \ --bindPasswordFile pwd.txt \ --no-prompt dsconfig set-workflow-element-prop \ --element-name proxy-we1 \ --add exclude-list:cn=directory\ manager \ --add exclude-list:cn=oraclecontext,dc=ziontech,dc=demo \ --set remote-ldap-server-bind-dn:CN=Administrator,CN=Users,DC=ziontech,DC=demo \ --set remote-ldap-server-bind-password:Welcome1 \ --hostname localhost \ --port 6444 \ --trustAll \ --bindDN cn=directory\ manager \ --bindPasswordFile pwd.txt \ --no-prompt
Locate the LDIF template file at $MW_HOME/Oracle_OUD1/config/EUS/modifyRealm.ldif
Edit the modifyRealm.ldif file as follows:
→ Replace dc=example,dc=com with dc=ziontech,dc=demoReplace dc=example,dc=com with dc=ziontech,dc=demo
→ Replace ou=people and ou=groups with the correct locations of the user and group entries in ADReplace ou=people and ou=groups with the correct locations of the user and group entries in AD
Use the ldapmodify command to update the configuration with the edited LDIF template file, for example:
$ ldapmodify -h localhost -p 1389 -D "cn=directory manager" -j pwd-file -f modifyRealm.ldif
Modify the attribute orclcommonkrbprincipalattribute in the EUS config (cn=OracleContext,<realm>) to the attribute name which stored the Kerberos principal on AD.
Additionally, I modified few other attributes.
$ ldapmodify -h localhost -p 1389 -D "cn=directory manager" -j pwd-file -f modifyConfig.ldif # cat modifyConfig.ldif dn: cn=Common,cn=Products,cn=OracleContext,dc=ziontech,dc=demo changetype: modify replace: orclCommonKrbPrincipalAttribute orclCommonKrbPrincipalAttribute: userPrincipalName - dn: cn=Common,cn=Products,cn=OracleContext,dc=ziontech,dc=demo changetype: modify replace: orclCommonWindowsPrincipalAttribute orclCommonWindowsPrincipalAttribute: samaccountname - dn: cn=Common,cn=Products,cn=OracleContext,dc=ziontech,dc=demo changetype: modify replace: orclCommonNicknameAttribute orclCommonNicknameAttribute: samaccountname -
SQL> select value from v$parameter where name = 'os_authent_prefix'; SQL> select value from v$parameter where name = 'remote_os_authent';
If you see something like ops$ for os_authent_prefix, it has to be changed to null using following process:
SQL> create pfile='/tmp/pfile.txt' from spfile; SQL> shutdown immediate;
Add this to the “/tmp/pfile.txt” file:
os_authent_prefix=''
Recreate the pfile:
SQL> sqlplus / as sysdba SQL> create spfile from pfile='/tmp/pfile.txt'; SQL> startup
Modify sqlnet.ora with following configuration:
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT) ADR_BASE = /app/db1 SQLNET.KERBEROS5_KEYTAB=/app/kerberos/keytab SQLNET.KERBEROS5_CONF=/app/kerberos/krb5.conf SQLNET.KERBEROS5_CONF_MIT=TRUE SQLNET.AUTHENTICATION_KERBEROS5_SERVICE=oracle SQLNET.AUTHENTICATION_SERVICES=(BEQ,KERBEROS5)
Create /app/kerberos/krb5.conf with following content:
[libdefaults] default_realm = ZIONTECH.DEMO [realms] ZIONTECH.DEMO = { kdc = addemo1.ziontech.demo:88 } [domain_realm] .ziontech.demo = ZIONTECH.DEMO ziontech.demo = ZIONTECH.DEMO
Configure the database to use the directory:
Run netca and then,
1) Select ‘Directory Usage Configuration’
2) Set the Directory Type to ‘Oracle Internet Directory’
3) Set the Hostname, Port, and SSL Port for the OUD Server
4) Select the Oracle Context to use. In this document, the context used is “cn=OracleContext,dc=ziontech,dc=demo”
5) Click Finish.
This will create an ldap.ora file in the $ORACLE_HOME/network/admin directory with following content:
# ldap.ora Network Configuration File: /app/db1/product/11.2.0/dbhome_1/network/admin/ldap.ora # Generated by Oracle configuration tools. DIRECTORY_SERVERS= (oud.ziontech.demo:4389:4636) DEFAULT_ADMIN_CONTEXT = "dc=ziontech,dc=demo" DIRECTORY_SERVER_TYPE = OID
Registering Database with the Directory:
Run the Database Configuration Assistant (dbca) from the database Oracle Homes to register the database with OID.
Run dbca and then,
1) Click ‘Next’
2) Select ‘Configure Database Options’
3) Select the database from the list – there may then be a short delay.
4) On the Directory Service screen select ‘Yes, register the database’ and enter the username and password of an OUD user who is authorized to register databases. For example a username of ‘cn=Directory Manager’.
Then enter a password to be used for the new database wallet. This wallet is used to store a randomly generated internal password for the database.
5) Click ‘Next’, and the ‘Next’ again on the Database Components screen.
6) Finally click ‘Finished’ on the Connection Mode screen, and ‘OK’ on the pop-up Confirmation window.
Create user:
create user testuser1 identified globally as 'CN=testuser1,CN=Users,DC=ziontech,DC=demo'; grant create session to testuser1;
Create global role in the Database:
create role g1 identified globally;
These can be performed either by using Oracle Enterprise Manager or using a command line alternative tool ‘eusm’ located under $ORACLE_HOME/bin
# Create a role in Directory
eusm createRole enterprise_role=g1 domain_name="OracleDefaultDomain" realm_dn="dc=ziontech,dc=demo" ldap_host="oud.ziontech.demo" ldap_port=1389 ldap_user_dn='cn=Directory\20Manager' ldap_user_password='Welcome1'
# Map the ENT role with DB role
eusm addGlobalRole enterprise_role="g1" domain_name="OracleDefaultDomain" realm_dn="dc=ziontech,dc=demo" database_name="db1" global_role="g1" dbuser="system" dbuser_password="Welcome1" dbconnect_string="db1.ziontech.demo:1521:db1" ldap_host="oud.ziontech.demo" ldap_port=1389 ldap_user_dn='cn=Directory\20Manager' ldap_user_password='Welcome1'
# Grant Role
eusm grantRole enterprise_role="g1" domain_name="OracleDefaultDomain" realm_dn="dc=ziontech,dc=demo" group_dn="CN=g1,CN=Users,DC=ziontech,DC=demo" ldap_host="oud.ziontech.demo" ldap_port=1389 ldap_user_dn='cn=Directory\20Manager' ldap_user_password='Welcome1'
Modify sqlnet.ora to contain following information:
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT) SQLNET.KERBEROS5_CC_NAME=OSMSFT:// SQLNET.AUTHENTICATION_SERVICES= (beq,kerberos5) SQLNET.KERBEROS5_CONF =c:\kerberos\krb5.conf SQLNET.KERBEROS5_CONF_MIT = true
Create krb5.conf in the location defined above:
SQLNET.KERBEROS5_CC_NAME=c:\kerberos\cc SQLNET.AUTHENTICATION_SERVICES= (beq,kerberos5) SQLNET.KERBEROS5_CONF =c:\kerberos\krb5.conf SQLNET.KERBEROS5_CONF_MIT = true
Create tnsnames.ora
DB1 = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = db1.ziontech.demo)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = db1.ziontech.demo) ) )
Connect to Database using SQLplus.
User is automatically logged on to database as Oracle Client uses the internal Windows credentials cache.
Global role is assigned as expected
Here are some of the commonly seen issues during integrating a database with Directory Services:
Create an ldap.ora in $ORACLE_HOME/network/admin that points to your directory services instance. The file may be created using the Network Configuration Assistant (netca) or any text editor. Below is an example of the content of the file.
DIRECTORY_SERVERS= (oud.ziontech.net:1389:1636) DEFAULT_ADMIN_CONTEXT = "dc=ziontech,dc=net" DIRECTORY_SERVER_TYPE = OID
I will keep updating this post with any new issues that I encounter.
EUS can be enabled for an OUD proxy server during installation or can be configured later if a proxy instance is already existing. In this post, I will cover steps on how to create a new OUD proxy instance for EUS using a GUI and Active Directory as the backend.
Change to OUD_install_dir directory. For example:
cd /opt/app/mw/Oracle_OUD/
Ensure JAVA_HOME environment variable is set to a supported JVM.
export JAVA_HOME=/opt/app/jdk
Set INSTANCE_NAME environment variable.
export INSTANCE_NAME=oud-proxy
Run the oud-proxy-setup command to configure the proxy server installation.
./oud-proxy-setup
The utility launches the graphical installer.
Screenshots for reference:
Then, import the content of the files by running the following command
$OUD_INSTANCE_ROOT/OUD/bin/import-ldif -n oraclecontext -l eusData.ldif -F --hostname localhost --port 4444 --bindDN "cn=directory manager" --bindPasswordFile /tmp/password.txt
This completes preparing the OUD proxy for EUS. The next steps are to prepare the database and create user mappings.
After preparing the OUD and the database for EUS, users from the directory can be authenticated to the database. However, they have to be associated with a schema in the database.
Directory identities are mapped to database schemas. The database schema can be dedicated or a shared schema. Directory groups are mapped to database roles.
CREATE USER global_ident_schema_user IDENTIFIED GLOBALLY; GRANT CONNECT TO global_ident_schema_user;
To map a directory user directly to a database schema, the first step is to get the complete DN of this particular user from the OUD.
A complete DN looks like this:
uid=nasir,ou=people,dc=ziontech,dc=net
Now, on the database run the following statements to create a user called ldap_nasir locally in the database. This will be authenticated using OID credentials.
create user ldap_nasir identified globally as 'uid=nasir,ou=people,dc=ziontech,dc=net';
We can also alter an existing user in the database so that the user will now use OUD credentials to login.
alter user existing_user identified globally as 'uid=nasir,ou=people,dc=ziontech,dc=net';
Mapping roles
CREATE ROLE hr_access IDENTIFIED GLOBALLY; GRANT SELECT ON hr.employees TO hr_access;
To create the enterprise role, hr_access:
Note: You will be required to log in to the database before you can select the global role.
The enterprise user, Nasir, can now access the hr.employees table in the database.
After preparing Oracle Unified Directory (OUD), the database itself must be prepared for Enterprise User Security (EUS). Preparing a database involves configuring the database, registering the database, mapping the user(s) or group(s) to a schema.
Similar to OUD, there is more than one way to achieve this. We will use GUI.
Set the required environment variables for database. For example:
export ORACLE_BASE=/opt/app/db/ export ORACLE_SID=dbdev export ORACLE_HOME=/opt/app/db/11.2.0
Run Net Configuration Assistant (netca) tool to configure OUD for the database.
On database machine, run:
$ORACLE_HOME/bin/netca
Screenshots for reference:
NetCA creates an ldap.ora file in the $ORACLE_HOME/network/admin directory which stores the connection information details about the directory.
The Database Configuration Assistant (DBCA) tool enables to register the database with OUD.
To register the database with the directory: Start DBCA using the dbca command.
$ORACLE_HOME/bin/dbca
Screenshots for reference:
Once the database is prepared, we need to associate enterprise users to database. Please refer to this post to learn about various user/group mappings available.
This blog post provides an introduction to the Oracle Unified Directory (OUD), Enterprise User Security (EUS), and its integration. An index of all future posts on OUD and EUS integration will be available here.
We can categorize this process into three steps:
Scenarios:
In a series of posts, I will cover detailed steps of both the above-mentioned integration scenarios. Please click the links below for respective blog posts:
Scenario 1: User identities stored in the OUD
Scenario 2: Using the OUD as a proxy server
Database preparation involves configuring and registering the OUD with the database. It can be achieved by using a GUI method or a command line tool.
For the GUI method, click here.
After preparing the OUD and the database for EUS, users from the directory can be authenticated to the database. However, they have to be associated with a schema in the database. This can be achieved by using Database Control or a command line tool called EUSM.
For Database Control or the EUSM tool and various scenarios, refer to this link.
Commonly seen errors during EUS integration are discussed here.
Do you have an EUS OUD integration project? Or, are you migrating from OID to OUD?
If you need consulting help, please reach us at support@ziontech.com.