{"id":94,"date":"2016-01-22T15:21:47","date_gmt":"2016-01-22T15:21:47","guid":{"rendered":"https:\/\/www.spotonoracle.com\/?p=94"},"modified":"2016-05-10T16:28:40","modified_gmt":"2016-05-10T16:28:40","slug":"enterprise-user-security-part-4","status":"publish","type":"post","link":"https:\/\/www.spotonoracle.com\/?p=94","title":{"rendered":"Enterprise User Security – Part 4"},"content":{"rendered":"

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<\/a> 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.<\/p>\n

\r\ncd ${ORACLE_HOME}\/ldap\/lib\r\nmake -f ins_ldap.mk ORACLE_HOME=${ORACLE_HOME} clientonlyinstall\r\n<\/pre>\n
 
\nRegister a database<\/strong>
\nFor 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.<\/p>\n
Configure ldap.ora:
\nThis will let the database know where to contact the directory server.<\/p>\n
\r\nvi ${TNS_ADMIN}\/ldap.ora\r\n\r\nDIRECTORY_SERVERS=(<OUD_SERVER>:1389:1636)\r\nDIRECTORY_SERVER_TYPE=OID\r\nDEFAULT_ADMIN_CONTEXT="dc=spotonoracle,dc=ch"\r\n<\/pre>\n
Configure sqlnet.ora:
\nThe 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.<\/p>\n
\r\nmkdir \/u01\/app\/oracle\/admin\/${ORACLE_SID}\/wallet\r\n\r\nvi ${TNS_ADMIN}\/sqlnet.ora\r\n \r\nNAMES.DIRECTORY_PATH = (LDAP, TNSNAMES, EZCONNECT)\r\nWALLET_LOCATION =\r\n  (SOURCE =\r\n    (METHOD = FILE)\r\n    (METHOD_DATA =\r\n      (DIRECTORY = \/u01\/app\/oracle\/admin\/$ORACLE_SID\/wallet)\r\n    )\r\n  )\r\n<\/pre>\n
Define how the database will login to OUD:<\/p>\n
\r\nalter system set ldap_directory_access=password scope=both;\r\n<\/pre>\n
 
\nRegistering the database:
\nThe 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.<\/p>\n
\r\ndbca -silent \\\r\n  -configureDatabase \\\r\n  -sourceDB "${ORACLE_SID}" \\\r\n  -registerWithDirService true \\\r\n  -dirServiceUserName "cn=diradmin" \\\r\n  -dirServicePassword "Complex-1-Password" \\\r\n  -walletPassword 'Wallet-1-Password'\r\n<\/pre>\n
You can view the registration password generated by the DBCA:<\/p>\n
\r\nmkstore -wrl \/u01\/app\/oracle\/admin\/${ORACLE_SID}\/wallet -viewEntry ORACLE.SECURITY.PASSWORD\r\n<\/pre>\n
Verify the dababase is an entity in the directory:<\/p>\n
\r\nldapsearch -D "cn=diradmin" -w "Complex-1-Password" -h <OUD_SERVER> -p 1389 -b dc=spotonoracle,dc=ch cn=${ORACLE_SID} -LLL\r\n<\/pre>\n
From now on, as a nice side effect, clients (e.g. SQL*Plus) can use OUD as TNS names resolution service.<\/p>\n
Prepare the database EUS user and roles<\/strong>
\nYou’ll most likely going to use shared schemas, so that’s what I’m doing here.
\nI 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.
\nThe newly created global role gets all the privileges and roles that are required for the users in a given functional role.<\/p>\n
\r\ncreate user eus_user identified globally;\r\n\r\ncreate role eus_dba_role identified globally;\r\ngrant create session to eus_dba_role;\r\ngrant dba to eus_dba_role;\r\n<\/pre>\n
 
\nPrepare directory objects<\/strong>
\nThere 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:
\n\"00-target-structure-odsm\"<\/a><\/p>\n
Create a group named “Users” to hold user entities. Navigate to level: “Root” => “dc=spotonoracle,dc=ch” and create a “Static Group Entry”:
\n\"01-create-group-menu\"<\/a><\/p>\n
In the group details, enter the common name: Users
\n\"02-create-users-group\"<\/a><\/p>\n
Create another static group named “Groups” on the same level as “Users” (dc=spotonoracle,dc=ch):
\n\"03-create-groups-group\"<\/a><\/p>\n
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:
\n\"04-create-dbadmins-group\"<\/a><\/p>\n
Create a user named “admjohn”. In the left tree, navigate to group “Users” and create a “User Entry”:
\n\"05-create-user-menu\"<\/a><\/p>\n
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.
\n\"06-create-admjohn\"<\/a><\/p>\n
Important: the user object must have some special attributes. This is what makes the user entity a EUS user from a directory services perspective.
\nIn the tree, click on the user and switch to tab “Attributes”. Add following “Object Classes” to the “Mandatory Attributes”:<\/p>\n