Tag Archives: EUS

Oracle Security EUS Snippets – Setup Proxy User Privileges

Since I’m always short of time for a longer blog post, I’ll just try a short one. Intended as a mini-series, I will show different configuration examples for Oracle Enterprise User Security. Today I’ll start with the configuration of EUS based proxy privileges. The environment I use is DOE, my Docker based Oracle Engineering environment. In particular the EUS configuration. For more information, see the corresponding GitHub repository oehrlis/doe respectively in the folder eus for the EUS specific environment.

Background

Database proxy privileges are used relatively often to give certain users rights to access a different schema. The user authenticates himself with his credentials and becomes a proxy user in the database. Below an example where the user RMAN, gets access to a different schema, specifically an other RMAN catalog schema (see also blog post about SEPS and RMAN).

CREATE USER rman IDENTIFIED BY welcome1;
CREATE USER rman19000 NO AUTHENTICATION QUOTA UNLIMITED ON rman_data;
GRANT RECOVERY_CATALOG_OWNER TO rman19000;
ALTER USER rman19000 GRANT CONNECT THROUGH rman; 
ALTER USER rman19000 DEFAULT TABLESPACE rman_data;

The following users were created

  • RMAN1900 is the schema owner for an Oracle 19c RMAN catalog stored in the tablespace RMAN_DATA. The user is created without any authentication but with a proxy privilege for the user RMAN.
  • RMAN is the user which will be used to connect to the catalog. There are other catalogs as well but not shown in this example
SQL> connect rman[RMAN19000]/welcome1@CATALOG
Connected.
SQL> show user
USER is "RMAN19000"
SQL> SELECT sys_context('userenv','PROXY_USER') PROXY_USER,
sys_context('userenv','SESSION_USER') SESSION_USER from dual;

PROXY_USER SESSION_USER
---------- ---------------
RMAN	   RMAN19000D

With pure database authentication or authorisation, the configuration of proxy users is easy. With Enterprise User Security, proxy privileges are no longer managed in the database but in the directory. Let’s take a look at that.

Database Configuration

For Enterprise User Security based proxy privileges, only ENTERPRISE USERS is specified in the database. The rest is done in the OracleContext of the directory. See also ALTER USER in Oracle® Database SQL Language Reference 19c.

ALTER USER scott GRANT CONNECT THROUGH ENTERPRISE USERS;

Enterprise User Security Configuration

The configuration can be either done via Oracle Enterprise Manager Cloud Control as documented in Oracle® Database Enterprise User Security Administrator’s Guide 19c or with the command line utility eusm. I prefer the command line utility as I often do not have an OEM by hand.

  • Create the proxy permission in the directory.
eusm createProxyPerm proxy_permission="Scott Proxy" \
domain_name="OracleDefaultDomain" \
realm_dn="dc=trivadislabs,dc=com" \
ldap_host=eusoud.trivadislabs.com ldap_port=1389 \
ldap_user_dn=cn=eusadmin,cn=oraclecontext \
ldap_user_password=$(cat /u01/common/etc/eusadmin_pwd.txt)
  • Define a target user for this proxy permission.
eusm addTargetUser proxy_permission="Scott Proxy" \
database_name="TEUS01" \
target_user="SCOTT" dbuser="system" \
dbuser_password=$(cat /u00/app/oracle/admin/TEUS01/etc/TEUS01_password.txt) \
dbconnect_string="eusdb.trivadislabs.com:1521/TEUS01.trivadislabs.com" \
domain_name="OracleDefaultDomain" \
realm_dn="dc=trivadislabs,dc=com" \
ldap_host=eusoud.trivadislabs.com ldap_port=1389 \
ldap_user_dn=cn=eusadmin,cn=oraclecontext \
ldap_user_password=$(cat /u01/common/etc/eusadmin_pwd.txt)
  • Explicit granting of proxy permission to the user KING. Can also be assigned to a group.
eusm grantProxyPerm proxy_permission="Scott Proxy" \
user_dn="cn=Ben King,ou=Senior Management,ou=People,dc=trivadislabs,dc=com" \
domain_name="OracleDefaultDomain" \
realm_dn="dc=trivadislabs,dc=com" \
ldap_host=eusoud.trivadislabs.com ldap_port=1389 \
ldap_user_dn=cn=eusadmin,cn=oraclecontext \
ldap_user_password=$(cat /u01/common/etc/eusadmin_pwd.txt)
  • Display the proxy permissions defined for the EUS default domain.
eusm listProxyPermissions domain_name="OracleDefaultDomain" \
realm_dn="dc=trivadislabs,dc=com" \
ldap_host=eusoud.trivadislabs.com ldap_port=1389 \
ldap_user_dn=cn=eusadmin,cn=oraclecontext \
ldap_user_password=$(cat /u01/common/etc/eusadmin_pwd.txt)
  • Display information for the proxy permission Scott Proxy
eusm listProxyPermissionInfo proxy_permission="Scott Proxy" \
domain_name="OracleDefaultDomain" \
realm_dn="dc=trivadislabs,dc=com" \
ldap_host=eusoud.trivadislabs.com ldap_port=1389 \
ldap_user_dn=cn=eusadmin,cn=oraclecontext \
ldap_user_password=$(cat /u01/common/etc/eusadmin_pwd.txt)
  • Display proxy permissions for the user KING.
eusm listProxyPermissionsOfUser \
user_dn="cn=Ben King,ou=Senior Management,ou=People,dc=trivadislabs,dc=com" \
realm_dn="dc=trivadislabs,dc=com" \
ldap_host=eusoud.trivadislabs.com ldap_port=1389 \
ldap_user_dn=cn=eusadmin,cn=oraclecontext \
ldap_user_password=$(cat /u01/common/etc/eusadmin_pwd.txt)

Using the Proxy Permissions

Let’s test the permissions and connect as user KING.

  • Regular connection to the database as schema owner SCOTT.
SQL> connect SCOTT/tiger@TEUS01
Connected.
SQL> show user
USER is "SCOTT"
SQL> select sys_context('userenv','PROXY_USER') PROXY_USER,
sys_context('userenv','SESSION_USER') SESSION_USER from dual;

PROXY_USER	SESSION_USER
--------------- ---------------
                SCOTT
  • Regular connection to the database as KING.
SQL> connect king/welcome1@TEUS01
Connected.
SQL> show user
USER is "KING"
SQL> select sys_context('userenv','PROXY_USER') PROXY_USER,
sys_context('userenv','SESSION_USER') SESSION_USER from dual;

PROXY_USER	SESSION_USER
--------------- ---------------
                KING
  • Proxy connection to the database
SQL> connect king[SCOTT]/welcome1@TEUS01
Connected.
SQL> show user
USER is "SCOTT"
SQL> select sys_context('userenv','PROXY_USER') PROXY_USER,
sys_context('userenv','SESSION_USER') SESSION_USER from dual;

PROXY_USER	SESSION_USER
--------------- ---------------
KING            SCOTT

Conclusion

Configuration of proxy permissions in connection with Oracle Enterprise User Security is not as complicated as you might think. It is also useful if shared global users need access to certain schemas. For example, a power user is allowed to access the application schema.

Oracle Enterprise User Security with multiple ldap.ora

Recently I came across the situation where I have to configure Enterpriser User Security for a database server with multiple databases for different directories. This is quite tricky when using a shared Oracle Home and a central TNS_ADMIN directory for SQLNet configuration. A common TNS_ADMIN also implies the use of only one ldap.ora file. Several ldap servers can be registered in one ldap.ora, but this is primarily used for failover configuration in a high-availability LDAP server architecture. The use of multiple EUS contexts in different LDAP servers is not supported. At least not in one single file. But there are some workarounds.

Oracle does look for the ldap.ora file in a few different places. The following sequence is maintained:

  1. $LDAP_ADMIN environment variable setting
  2. $ORACLE_HOME/ldap/admin directory
  3. $TNS_ADMIN environment variable setting
  4. $ORACLE_HOME/network/admin directory

Notes on this order can be found at different places in the Oracle documentation e.g Oracle Database Database Net Services Reference 19c Overview of Directory Server Usage File, Oracle Database Security Guide 19c About Using a dsi.ora File or in the Oracle Support Note 363283.1 What Is The Search Order For The LDAP.ORA File?

If you don’t trust the documentation, you can also verify the search order with strace. First define a few values for TNS_ADMIN and LDAP_ADMIN to be sure we do not use the default values.

export LDAP_ADMIN="/u01/config"
export TNS_ADMIN="/u00/app/oracle/network/admin"

If we make sure, that LDAP is the first names resolution in sqlnet.ora we could use strace with tsnping.

oracle@eusdb:~/ [TEUS01] grep -i NAMES.DIRECTORY_PATH $TNS_ADMIN/sqlnet.ora
NAMES.DIRECTORY_PATH=(LDAP, TNSNAMES, EZCONNECT )

oracle@eusdb:~/ [TEUS01] strace -o /u01/config/tnsping.txt tnsping TEUS01

TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 28-NOV-2019 20:47:33

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

Used parameter files:
/u00/app/oracle/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = eusdb)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = TEUS01)))
OK (30 msec)

Checking the output does show the different directories

oracle@eusdb:~/ [TEUS01] grep -i ldap.ora /u01/config/tnsping.txt
stat("/u01/config/ldap.ora", 0x7ffd149f6af0) = -1 ENOENT (No such file or directory)
stat("/u00/app/oracle/product/19.0.0.0/ldap/admin/ldap.ora", 0x7ffd149f6af0) = -1 ENOENT (No such file or directory)
stat("/u00/app/oracle/network/admin/ldap.ora", 0x7ffd149f6af0) = -1 ENOENT (No such file or directory)
stat("/u00/app/oracle/product/19.0.0.0/network/admin/ldap.ora", 0x7ffd149f6af0) = -1 ENOENT (No such file or directory)
stat("/u00/app/oracle/product/19.0.0.0/network/admin/ldap.ora", 0x7ffd149f6be0) = -1 ENOENT (No such file or directory)

As you can see the search order does match the documented search order. But how does that help us? It’s actually relatively simple. To use different LDAP server configuration per database, we do have to make sure, that each database has an individual ldap.ora file. This can be ensured by one the following points:

  • Have a dedicated Oracle Home for each database with an individual ldap.ora file in each Oracle Home
  • Define an environment variable for LDAP_ADMIN for each database.

Both methods have their advantages and disadvantages, but aren’t optimal. As often, several paths lead to the goal. The third option uses a static listener configuration for the database with an ENVS parameter. Unfortunately, the information about ENVS has disappeared in the latest version of the Oracle documentation. At least in the Oracle Database Database Net Services Reference 19c. But you can search for ENVS in the Oracle Database Bookshelf. The parameter ENVS can be used to specify environment variables for the listener to set prior to executing (as a child process) a dedicated server program or an executable specified with the PROGRAM parameter. This allows a static listener entry to be defined for each database, where LDAP_ADMIN or TNS_ADMIN is explicitly set. Below you see an excerpt of listener.ora for ORACLE_SID TEUS01.

SID_LIST_LISTENER =
   (SID_LIST =
     (SID_DESC =
       (GLOBAL_DBNAME = TEUS01 )
       (ORACLE_HOME = /u00/app/oracle/product/19.0.0.0)
       (SID_NAME = TEUS01)
       (ENVS="LDAP_ADMIN=/u00/app/oracle/network/TEUS01")
   )))

The database TEUS01 does use a dedicated ldap.ora. If you add the listener entry just for this DB, you can keep the default ldap.ora in the regular TNS_ADMIN directory. Thus one can apply the following principle:

  • Use a generic ldap.ora configuration in the TNS_ADMIN directory. e.g which is valid for most of the database on this server
  • Add a static listener configuration for each database which does have to use a dedicate Oracle Enterprise User Security configuration and therefor a dedicated ldap.ora.

This method is not only helpful when configuring LDAP server or Oracle Enterprise User Security, but also in other SQLNet use cases. In particular the following:

  • Define TNS_ADMIN and use a dedicate sqlnet.ora configuration for Kerberos authentication. e.g. when you want to use or engineer Kerberos authentication for just one database in an shared environment.
  • Define TNS_ADMIN and use dedicate sqlnet.ora configuration for network encryption.
  • Define LDAP_ADMIN for dedicated ldap.ora or dsi.ora files for engineering centrally managed users.
  • And a couple more…

References

A few links and references related to this blog post

  • Oracle Support Document 728043.1 Use of DIRECTORY_SERVERS in LDAP.ORA & Known issues
  • Oracle Support Document 363283.1 What Is The Search Order For The LDAP.ORA File?
  • Search for ENVS in the Oracle Database Bookshelf
  • Oracle® Database Net Services Reference 11g Release 1 (11.1) ENVS

OUD 12c – SSLHandshakeException with “no cipher suites in common”

Recently I’ve update the java installation of my Oracle Unified Directory (OUD) 12.2.1.0.3 to the latest release. Java 1.8.0 update 202 to be exact (p28916775_180202_Linux-x86-64.zip). Actually a piece of cake, I’ve done this a few times in the past. My Enterprise User Security (EUS) test environment is running in Docker. A container for the database and an other one for the directory server. Updates are usually straight forward. Stop the containers, rebuild the images with the latest software / patches and recreate the containers. But not this time. After restarting OUD, my EUS authentication seems to be broken. When trying to log in, I did get a friendly ORA-01017 error.

 SQL> connect blofeld/******** ERROR: ORA-01017: invalid username/password; logon denied Warning: You are no longer connected to ORACLE.
The control of the OUD access log file did show a cipher error. 
 [21/Feb/2019:06:21:27] CONNECT conn=5 from=172.20.0.3:50376 to=172.20.0.2:1636 protocol=LDAPS [21/Feb/2019:06:21:27] DISCONNECT conn=5 reason="I/O Error" msg="no cipher suites in common"

Groundhog Day? Endless loop? I knew I did fix this before. So I’ve checked again the solution in MOS Note 2397791.1 and 2304757.1. According to my understanding the java.security file did look ok. The required legacy ciphers has been enabled by removing 3DES_EDE_CBC from the list of jdk.tls.disabledAlgorithms.
I finally did several tests with different Java versions (1.8.0 update 192 and 1.8.0 update 202) and different java.security files. In the third attempt, database authentication with EUS and OUD in combination with Java 1.8.0 Update 202 also worked. The solution was rather simple. I did use the java.security file from java 1.8.0 update 192 rather than using the new version and enable 3DES_EDE_CBC. Running diff on both files has uncovered the culprits.

 diff java.security java.security_202_default 645c645 < EC keySize < 224 --- > EC keySize < 224, 3DES_EDE_CBC, anon, NULL 700c700,701 < RC4_128, RC4_40, DES_CBC, DES40_CBC --- > RC4_128, RC4_40, DES_CBC, DES40_CBC, \ > 3DES_EDE_CBC
Or just the lines with jdk.tls.disabledAlgorithms. 
 jdk.tls.disabledAlgorithms=SSLv3, RC4, DES, MD5withRSA, DH keySize < 1024, \ EC keySize < 224, 3DES_EDE_CBC, anon, NULL
A difference due to 3DES_EDE_CBC was to be expected, since I made the comparison to the standard file java.security and there this algorithm was not yet removed. But anon, NULL

is new. The list of disabled algorithms jdk.tls.disabledAlgorithms has been altered in Java 1.8.0 update 202. I could have seen this myself if I had looked through the release notes before installing the software 🙂 . There is a java bug related to this, see JDK-8211883 Disable anon and NULL cipher suites. The problem is now that my EUS is working again, but it will use unsecure and legacy algorithms. A proper fix of this issue has to be implemented in the LDAP / EUS stack of the Oracle database binaries.

Conclusion

First of all do read the release notes before updating production environments 🙂 . As always in IT, do a little change on one side can unexpectedly break something on the other side. The solution presented here can only be a workaround, because we endanger security with legacy algorithms. Oracle should soon update the LDAP / EUS stack in the Oracle binaries.

  • Fix for Java 1.8.0 update 192 and older: Use the solution described in MOS note 2304757.1 update java.security and remove 3DES_EDE_CBC from the jdk.tls.disabledAlgorithms
  • Fix for Java 1.8.0 update 201 and newer: Use either an old java.security which does work for you EUS environment or remove 3DES_EDE_CBC, anon and NULL from the jdk.tls.disabledAlgorithms in your java.security

Links

A few links related to this post:

  • OUD 12c – EUS Integration Failing with Message “no cipher suites in common”[2397791.1]
  • OUD 11g – EUS Authentication Fails with Error Message “no cipher suites in common”[2304757.1]
  • Java 1.8.0 update 201 release notes
  • Java bug JDK-8211883 Disable anon and NULL cipher suites
  • Preview of my Docker compose files to setup an Oracle Enterprise User Security Environment on Docker GitHub oehrlis/docker

WALLET_LOCATION in sqlnet.ora for Container Databases

Recently I’ve setup Oracle Enterprise User Security (EUS) with Oracle Unified Directory (OUD) on my favorite linux test system. Among regular 11.2.0.4 and 12.1.0.2 databases I do also have a 12.1.0.2 Container Database. EUS work like a charm on the regular databases but not on the PDB. 

SQL> conn soe
Enter password: 
ERROR:
ORA-28305: WALLET_LOCATION in sqlnet.ora file for container database is not
supported.


Warning: You are no longer connected to ORACLE.

The error seems to be a bit weird. So fare I’ve explicitly set the wallet location to make sure the wallet it somewhere I decided. I have a shared sqlnet.ora file, where I use $ORACLE_SID in the path for the different instances. An excerpt from my sqlnet.ora file

...
WALLET_LOCATION =
  (SOURCE =
    (METHOD = File)
    (METHOD_DATA = (DIRECTORY = /u00/app/oracle/admin/$ORACLE_SID/wallet)))

ENCRYPTION_WALLET_LOCATION=
 (SOURCE=
  (METHOD=FILE)
   (METHOD_DATA=
    (DIRECTORY=/u00/app/oracle/admin/$ORACLE_SID/tde_wallet/)))
...

The action described for the Oracle Error Message ORA-28305 is clear. Remove WALLET_LOCATION from sqlnet.ora to use EUS also for Container Databases.

SQL> conn soe
Enter password: 
Connected.
SQL> @sousrinf
Database Information
--------------------
- DB_NAME		: TDB12C
- DB_DOMAIN		:
- INSTANCE		: 1
- INSTANCE_NAME 	: TDB12C
- SERVER_HOST		: o-sec
-
Authentification Information
----------------------------
- SESSION_USER		: C##SOE
- PROXY_USER		:
- AUTHENTICATION_METHOD : PASSWORD
- IDENTIFICATION_TYPE	: GLOBAL SHARED
- NETWORK_PROTOCOL	:
- OS_USER		: oracle
- AUTHENTICATED_IDENTITY: SOE
- ENTERPRISE_IDENTITY	: cn=soe,cn=Users,dc=trivadistraining,dc=com
-
Other Information
-----------------
- ISDBA 		: FALSE
- CLIENT_INFO		:
- PROGRAM		: sqlplus@o-sec (TNS V1-V3)
- MODULE		: SQL*Plus
- IP_ADDRESS		:
- SID			: 39
- SERIAL#		: 47117
- SERVER		: DEDICATED
- TERMINAL		: pts/6

PL/SQL procedure successfully completed.

The corresponding Oracle Bug 17758886 has been rejected as “not a Bug”. Oracle® Database Net Services Reference 12c Release 1 (12.1) WALLET_LOCATION does not mention PDB’s. There is only some information in the Oracle® Database Reference 12c Release 1 (12.1) Using LDAP_DIRECTORY_ACCESS with PDBs.

Conclusion

It seems, that with PDB’s it is not possible to explicitly set a wallet location. If the default location is not appropriate for your database environment, you have to use soft links use an alternative location for your wallet.

By the way, the wallet for TDE or for Secure External Password Store (SEPS) is not affected. You may still set WALLET_LOCATION for SEPS or ENCRYPTION_WALLET_LOCATION for TDE.

References

Some links related to this topic.

If time permits, I’ll write a few blog post about setting up and configuring EUS with OUD.