Before we start registering databases and creating users and groups in the directory, we must first patch our Oracle 12c database homes. If you followed me since part 1 of this series you already have the necessary binaries to update OPatch and install database patch 19285025. Since I assume most of you are professional DBAs I’m not going into the details of applying the database patch. Unfortunately, the patch read-me does not tell you to re-link the LDAP client binaries which actually is the crucial bit.

cd ${ORACLE_HOME}/ldap/lib
make -f ins_ldap.mk ORACLE_HOME=${ORACLE_HOME} clientonlyinstall

 
Register a database
For the database to be able to communicate with the directory service we must register it with the OUD. The database will be an entity in the directory and is required to authenticate itself as such.

Configure ldap.ora:
This will let the database know where to contact the directory server.

vi ${TNS_ADMIN}/ldap.ora

DIRECTORY_SERVERS=(<OUD_SERVER>:1389:1636)
DIRECTORY_SERVER_TYPE=OID
DEFAULT_ADMIN_CONTEXT="dc=spotonoracle,dc=ch"

Configure sqlnet.ora:
The database will use a wallet to store the credentials used to authenticate itself with the directory service. If the database is already using a wallet for some other feature, you can share it.

mkdir /u01/app/oracle/admin/${ORACLE_SID}/wallet

vi ${TNS_ADMIN}/sqlnet.ora
 
NAMES.DIRECTORY_PATH = (LDAP, TNSNAMES, EZCONNECT)
WALLET_LOCATION =
  (SOURCE =
    (METHOD = FILE)
    (METHOD_DATA =
      (DIRECTORY = /u01/app/oracle/admin/$ORACLE_SID/wallet)
    )
  )

Define how the database will login to OUD:

alter system set ldap_directory_access=password scope=both;

 
Registering the database:
The DBCA (Database Configuration Assistant) is used to register databases with the directory service. The password used by the database for authentication is generated automatically and stored in the wallet. The wallet password is provided by you. If you already have a wallet the DBCA will just add the necessary entry, otherwise it will create a new wallet.

dbca -silent \
  -configureDatabase \
  -sourceDB "${ORACLE_SID}" \
  -registerWithDirService true \
  -dirServiceUserName "cn=diradmin" \
  -dirServicePassword "Complex-1-Password" \
  -walletPassword 'Wallet-1-Password'

You can view the registration password generated by the DBCA:

mkstore -wrl /u01/app/oracle/admin/${ORACLE_SID}/wallet -viewEntry ORACLE.SECURITY.PASSWORD

Verify the dababase is an entity in the directory:

ldapsearch -D "cn=diradmin" -w "Complex-1-Password" -h <OUD_SERVER> -p 1389 -b dc=spotonoracle,dc=ch cn=${ORACLE_SID} -LLL

From now on, as a nice side effect, clients (e.g. SQL*Plus) can use OUD as TNS names resolution service.

Prepare the database EUS user and roles
You’ll most likely going to use shared schemas, so that’s what I’m doing here.
I create a globally identified user. This means, the database will be using the directory service to authenticate client connection request. This user does not get any privileges or roles granted at all.
The newly created global role gets all the privileges and roles that are required for the users in a given functional role.

create user eus_user identified globally;

create role eus_dba_role identified globally;
grant create session to eus_dba_role;
grant dba to eus_dba_role;

 
Prepare directory objects
There are certain tasks that are more easily performed in the ODSM web console, e.g. create users and groups. We want to create the following structure in the GUI:

Create a group named “Users” to hold user entities. Navigate to level: “Root” => “dc=spotonoracle,dc=ch” and create a “Static Group Entry”:

In the group details, enter the common name: Users

Create another static group named “Groups” on the same level as “Users” (dc=spotonoracle,dc=ch):

Create a DBA Group named “DBAdmins” under the “Groups” group. In the left tree, navigate to “Groups” and add a new static group “DBAdmins” below:

Create a user named “admjohn”. In the left tree, navigate to group “Users” and create a “User Entry”:

Fill in the user’s details. Note, the field “User ID” maps to the user name that will be provided in the database connection string. “User Password” is the password that the end user will provide to authenticate with the database.

Important: the user object must have some special attributes. This is what makes the user entity a EUS user from a directory services perspective.
In the tree, click on the user and switch to tab “Attributes”. Add following “Object Classes” to the “Mandatory Attributes”:

• orclUser
• orclUserV2

Add user “admjohn” to the “DBAdmins” group. In the tree click on “DBAadmins”, under “Member Information” click “Add” and select “cn=admjohn,cn=Users,dc=spotonoracle,dc=ch”.

OK, let’s get some coffee

If you’re still with me but lost a little track, we should take another look at the bigger picture I showed in part 1.

So far we have covered:

• Green: objects in the database
• Blue: groups of objects in the LDAP directory
• Red: user entities in the LDAP directory

What we’re going to do next is:

• Yellow: enterprise roles in the LDAP directory
• All the mappings (arrows)
• All the grants (arrows)

This will be quite a lengthy post but don’t think it makes much sense to break it up in pieces. I will go on…but this time, using the Oracle EUSM utility (it’s documented in MOS note 1085065.1). Alternatively, you can do the same thing in the Enterprise Manager web console (perhaps more comfortable in a GUI). I like the CLI because it’s self-documenting and scriptable.

Create enterprise roles, mappings, and grants

Set default schema for all our enterprise users in the “Users” group. This will create the mapping for the group “Users” to database schema “EUS_USER”, and inherently all users in that group.

eusm createMapping \
  domain_name="OracleDefaultDomain" \
  map_type="SUBTREE" \
  map_dn="cn=Users,dc=spotonoracle,dc=ch" \
  schema="EUS_USER" \
  realm_dn="dc=spotonoracle,dc=ch" \
  ldap_host=<OUD_SERVER> \
  ldap_port=1389 \
  ldap_user_dn="cn=diradmin" \
  ldap_user_password="Complex-1-Password"

Previously, we have created a global role “EUS_DBA_ROLE” in the database. We need a matching role entity in the directory and call the enterprise role “DBARole”.

eusm createRole \
  enterprise_role="DBARole" \
  domain_name="OracleDefaultDomain" \
  realm_dn="dc=spotonoracle,dc=ch" \
  ldap_host=<OUD_SERVER> \
  ldap_port=1389 \
  ldap_user_dn="cn=diradmin" \
  ldap_user_password="Complex-1-Password"

Create a mapping between the enterprise role and the database role. The enterprise role can be mapped to many databases. For your sanity’s sake I don’t recommend to mix and match a lot.
E.g. Map the “DBARole” enterprise role only to the same role (incl. its definition) on multiple database. In this case the database role should be EUS_DBA_ROLE with the exact same grants on all database. Otherwise don’t map it to “DBARole” and create a separate enterprise role.

eusm addGlobalRole \
  enterprise_role="DBARole" \
  domain_name="OracleDefaultDomain" \
  realm_dn="dc=spotonoracle,dc=ch" \
  database_name="${ORACLE_SID}" \
  global_role="EUS_DBA_ROLE" \
  dbuser="system" \
  dbuser_password="<SYSTEM_PASSWORD>" \
  dbconnect_string="<DB_HOST_NAME>:<LISTENER_PORT>/<DB_SERVICE_NAME>" \
  ldap_host=<OUD_SERVER> \
  ldap_port=1389 \
  ldap_user_dn="cn=diradmin" \
  ldap_user_password="Complex-1-Password"

What’s left missing is the grant. We grant the role “DBARole” to the “DBAdmins” group. All the members of the “DBAdmins” group inherit this role grant.

eusm grantRole \
  enterprise_role="DBARole" \
  domain_name="OracleDefaultDomain" \
  realm_dn="dc=spotonoracle,dc=ch" \
  group_dn="cn=DBAdmins,cn=Groups,dc=spotonoracle,dc=ch" \
  ldap_host=<OUD_SERVER> \
  ldap_port=1389 \
  ldap_user_dn="cn=diradmin" \
  ldap_user_password="Complex-1-Password"

 
Voilà

$ sqlplus admjohn/************@<DB_HOST_NAME>:<LISTENER_PORT>/<DB_SERVICE_NAME>
SQL> select * from session_roles;
ROLE
--------------------------------------------------------------------------------
EUS_DBA_ROLE

 
Summary
With the fourth part of this series I conclude the basic setup of OUD, ODSM, and EUS. If I find the time I’m going to show some other aspects of OUD and EUS (directory replication, custom SSL certificates, directory disaster recovery or some more complex EUS setups). We’ll see…
So long, enjoy EUS.