Preamble

For users creation we, most of the time, use either password authenticated account for normal users (IDENTIFIED BY) or Operating System (OS) authenticated for batch accounts that are running processes directly on database server to avoid putting password clearly in scripts (IDENTIFIED EXTERNALLY). I know since long that a third option exists (IDENTIFIED GLOBALLY):

CREATE USER USE IDENTIFIED GLOBALLY AS 'directory_DN';

But I have never tested it as a bit more complex as it requires an LDAP directory (OID, AD, OVD) to implement it… As we recently implemented Oracle Internet Directory (OID) for directory naming (tnsnames entries are stored in an LDAP directory) I have now opportunity to investigate a bit on this part…

The OID I have is release 11.1.1.2.0 with a 11.2.0.1.0 Oracle database running on HP-UX 11.31 (11iv3) on an Itanium box.

This global authentication is called Enterprise User Security (EUS) and the method I have chosen is password authentication (SSL possible).

Installation with 11g

My 11gR2 test instance is 11.2.0.2.0 running on Red Hat Enterprise Linux Server release 5.5 (Tikanga)

First I created in $TNS_ADMIN below sqlnet.ora file:

NAMES.DEFAULT_DOMAIN = world
NAMES.DIRECTORY_PATH = (tnsnames, ldap, ezconnect)

Remark:
I still have this bad habit to set default domain to world, means that entry that will be added in OID will be for a listener accepting connection for service name called database_sid.world.

And below ldap.ora file (LDAP ports are coming from OID default installation, while official port are more 389 and 636):

DIRECTORY_SERVERS=(oid.domain.com:3060:3131)
DIRECTORY_SERVER_TYPE=OID
DEFAULT_ADMIN_CONTEXT="dc=domain, dc=com"

Then to register the database in OID the tool to use is database configuration assistant (dbca):

After welcome screen choose “Configure Database Options”, if option is grayed make sure your /etc/oratab file contains the correct entries:

Choose the database you want to configure:

De-select Database Console configuration (one thing at a time):

Choose to register the database and supply OID administrative account (cn=orcladmin by default) and Wallet password:

Leave default options:

Confirm:

Supply init.ora file location (if not existing due to spfile, apparently you are obliged to create it), and then in my case databases have not been restarted but dbca added inside ldap_directory_access=PASSWORD:

This can be confirmed with:

SQL> show parameter ldap
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
ldap_directory_access                string      PASSWORD
ldap_directory_sysauth               string      no

And:

[orahp3k@server1 ~]$ tnsping hp3k
 
TNS Ping Utility for Linux: Version 11.2.0.2.0 - Production on 15-MAR-2012 15:32:08
 
Copyright (c) 1997, 2010, Oracle.  All rights reserved.
 
Used parameter files:
/ora_hp3k/software/network/admin/sqlnet.ora
 
Used LDAP adapter to resolve the alias
Attempting to contact (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=server1.domain.com)(PORT=1531))(CONNECT_DATA=(SERVICE_NAME=hp3k.world)))
OK (0 msec)

Remark:
Notice the SERVICE_NAME=hp3k.world, for this to work your listener must obviously answer something like:

[orahp3k@server1 ~]$ lsnrctl status listener_hp3k
 
LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 15-MAR-2012 18:18:36
 
Copyright (c) 1991, 2010, Oracle.  All rights reserved.
 
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=server1.domain.com)(PORT=1531)))
STATUS of the LISTENER
------------------------
Alias                     listener_hp3k
Version                   TNSLSNR for Linux: Version 11.2.0.2.0 - Production
Start Date                15-MAR-2012 18:11:05
Uptime                    0 days 0 hr. 7 min. 31 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /ora_hp3k/software/network/admin/listener.ora
Listener Log File         /ora_hp3k/diag/tnslsnr/server1/listener_hp3k/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=server1.domain.com)(PORT=1531)))
Services Summary...
Service "hp3k.world" has 1 instance(s).
  Instance "hp3k", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

The Wallet has been created in /ora_hp3k/admin/hp3k/wallet/ default directory:

[orahp3k@server1 ~]$ ll /ora_hp3k/admin/hp3k/wallet/
total 8
-rw-r----- 1 orahp3k dba 3845 Mar 15 15:11 cwallet.sso
-rw------- 1 orahp3k dba 3768 Mar 15 10:50 ewallet.p12
 
[orahp3k@server1 admin]$ mkstore -wrl /ora_hp3k/admin/hp3k/wallet/ -list
Oracle Secret Store Tool : Version 11.2.0.2.0 - Production
Copyright (c) 2004, 2010, Oracle and/or its affiliates. All rights reserved.
 
Enter wallet password:
Oracle Secret Store entries:
ORACLE.SECURITY.DN
ORACLE.SECURITY.PASSWORD

You can also open the wallet with Oracle Wallet Manager tool (owm):

Then using Oracle Directory Services Manager (ODSM) I created an LDAP user by copy/paste of orcladmin account (to keep all the needed attribute), changed few display properties and added userPassword attribute which is not copied by default:

Then let’s create an account onto database:

SQL> create user yjaquier identified globally as 'cn=yjaquier,cn=Users,dc=domain,dc=com';
 
User created.
 
SQL> grant connect to yjaquier;
 
Grant succeeded.
 
SQL> col EXTERNAL_NAME for a50
SQL> SELECT USERNAME,EXTERNAL_NAME FROM DBA_USERS where username='YJAQUIER';
 
USERNAME                       EXTERNAL_NAME
------------------------------ --------------------------------------------------
YJAQUIER                       cn=yjaquier,cn=Users,dc=domain,dc=com

Then you can test it with:

[orahp3k@server1 ~]$ sqlplus yjaquier
 
SQL*Plus: Release 11.2.0.2.0 Production on Thu Mar 15 15:33:55 2012
 
Copyright (c) 1982, 2010, Oracle.  All rights reserved.
 
Enter password:
 
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
 
SQL>

Obvious added values are account creation for subcontractors and password aging/strength that can be implemented centrally in OID…

Installation with 10g

My 10gR2 test instance is 10.2.0.4.0 running on HP-UX 11iv1 (!!) on a PA RISC server.

Dbca binary in 10g is a little less efficient as it is not asking the location of initialization parameter file… So then file cannot be updated (error message) and when retrying to register I finally got TNS-04409 and TNS-04405 error message:

I anyway tried to connect but failing for ORA-28043 error:

server1{oracle}# sqlplus yjaquier/password
 
SQL*Plus: Release 10.2.0.4.0 - Production on Fri Mar 16 16:48:25 2012
 
Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.
 
ERROR:
ORA-28043: invalid bind credentials for DB-OID connection
 
 
Enter user-name:

But database has been correctly added to OID and Wallet is well created:

server1{oracle}# mkstore -wrl /ora_10g/admin/oracle2/wallet/ -list
Enter wallet password:
 
Oracle Secret Store entries:
ORACLE.SECURITY.DN
ORACLE.SECURITY.PASSWORD
server1{oracle}# tnsping oracle2
 
TNS Ping Utility for HPUX: Version 10.2.0.4.0 - Production on 16-MAR-2012 16:52:33
 
Copyright (c) 1997,  2007, Oracle.  All rights reserved.
 
Used parameter files:
/ora_10g/software/network/admin/sqlnet.ora
 
 
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS= (PROTOCOL=TCP) (HOST=server1.domain.com) (PORT=1531))) (CONNECT_DATA =(SID=oracle2)))
OK (0 msec)

Solution is not so simple and I solved it by using My Oracle Support note 1377901.1. I manually created file $ORACLE_BASE/admin/oracle2/pfile/init.ora by copying the one under $ORACLE_HOME/dbs. In fact dbca simply add ldap_directory_access=PASSWORD in it.

Once init.ora file created in dbca expected directory unregister the database using dbca (quit and relaunch dbca) and manually remove the remaining ghost entry in “cn=OracleDefaultDomain,cn=OracleDBSecurity,cn=Products,cn=OracleContext,dc=domain,dc=com” corresponding to your database (uniquemenber=…) using Oracle Directory Services Manager (ODSM), make a backup before deleting anything.

Then re-launch dbca again (quit and relaunch it) and try to register again your database, it should now work this time. Finally do not forget to copy the ldap_directory_access setting in your real initialization file.

LDAP SYSDBA authentication

Idea is to define an Enterprise role and assign SYSDBA or SYSOPER to it and be able to login with your LDAP account as sysdba. In the following I will create an ERSYSDBA Enterprise role, assign SYSDBA to it and grant it to my LDAP account (yjaquier). Please note that this functionality is available in 11g only.

As stated on My Oracle Support (MOS) the configuration is a mix of Grid Control/Database Control and Oracle Directory Manager (ODM) (or ldif file), see reference MOS note 457083.1. Mainly because I think there is a bug in Database Control that does not allow you to select all roles.

First follow the note to create, for example, ERSYSDBA (enterprise SYSDBA role) and assign to it SYSDBA database role (all with ODSM). Once done go in Grid Control/Database Control in Server tab and then Enterprise User Security, login to OID with cn=orcladmin account and once done choose Manage Enterprise Domains.

You should normally see only one domain called OracleDefaultDomain, configure it and in following screen choose Enterprise Roles, you should see ERSYSDBA LDAP role you created just before:

If you edit it you should see SYSDBA database role assign to it (which you can do with Grid Control/Database Control):

Then go in Grantees tab and add your OID account:

Then modify static parameter ldap_directory_sysauth to have a configuration like:

SQL> show parameter ldap
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
ldap_directory_access                string      PASSWORD
ldap_directory_sysauth               string      yes

Remark:
Then I discovered (better late than never) that whatever database account you use when issuing SQL*Plus directly from database binaries OS owner you are able to log as sysdba.

So better to test it from my desktop with a 11.2.0.3 client:

C:\>sqlplus yjaquier/password@hp3k as sysdba
 
SQL*Plus: Release 11.2.0.3.0 Production on Fri Mar 16 15:33:03 2012
 
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
 
ERROR:
ORA-01017: invalid username/password; logon denied
 
 
Enter user-name:

The famous ORA-01017: invalid username/password; logon denied error, then I found an ArKZoYD blog post (see reference) and a mention of a product limitation that obliges you to issue username in capital letters (!!):

C:\>sqlplus YJAQUIER/password@hp3k as sysdba
 
SQL*Plus: Release 11.2.0.3.0 Production on Fri Mar 16 15:33:07 2012
 
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
 
 
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
 
SQL> show user
USER is "SYS"
SQL> select sys_context('USERENV', 'ENTERPRISE_IDENTITY') DN from dual;
 
DN
--------------------------------------------------------------------------------
cn=yjaquier,cn=Users,dc=domain,dc=com
 
SQL>

Note that there is a command line tool called eusm to manage EUS (Enterprise User Security), as stated by Oracle this tool is not yet documented. Here is few example:

[orahp3k@server1 ~]$ eusm listDomains realm_dn="dc=domain, dc=com" ldap_host="oid.domain.com" ldap_port="3060" ldap_user_dn="cn=orcladmin" ldap_user_password="password"
LIST OF DOMAINS IN REALM: dc=domain, dc=com
-------------------------------------------------
OracleDefaultDomain

[orahp3k@server1 ~]$ eusm listEnterpriseRoles domain_name="OracleDefaultDomain" realm_dn="dc=domain, dc=com" ldap_host="oid.domain.com" ldap_port="3060" ldap_user_dn="cn=orcladmin" ldap_user_password="password"
LIST OF ENTERPRISE ROLES IN DOMAIN: OracleDefaultDomain
-------------------------------------------------
ERSYSDBA

[orahp3k@server1 ~]$ eusm listEnterpriseRolesOfUser user_dn="cn=yjaquier,cn=Users,dc=domain,dc=com" realm_dn="dc=domain, dc=com" ldap_host="oid.domain.com" ldap_port="3060" ldap_user_dn="cn=orcladmin" ldap_user_password="password"
LIST OF ENTERPRISE ROLES FOR USER:
cn=yjaquier,cn=Users,dc=domain,dc=com
-------------------------------------------------
DOMAIN:OracleDefaultDomain ENTERPRISEROLE:ERSYSDBA

[orahp3k@server1 ~]$ eusm listEnterpriseRoleInfo enterprise_role="ERSYSDBA" domain_name="OracleDefaultDomain" realm_dn="dc=domain, dc=com" ldap_host="oid.domain.com" ldap_port="3060" ldap_user_dn="cn=orcladmin" ldap_user_password="password"
INFOMATION FOR ENTERPRISE ROLE: ERSYSDBA
-------------------------------------------------
LIST OF USERS
-----------------
cn=yjaquier,cn=users,dc=domain,dc=com
LIST OF GROUPS
-----------------
LIST OF GLOBAL ROLES
--------------------
DBNAME: hp3k GLOBALROLE :SYSDBA

References

• Master Note For Enterprise User Security [ID 1376365.1]
• DBCA fails to Launch, Errors With UnsatisfiedLinkError Exception Loading Native Library: NLDAPJ11 [ID 786372.1]
• Delete or Configure Database Options Are Disabled In DBCA [ID 579171.1]
• How To Perform Silent DB Register/Unregister In OID Using DBCA [ID 1368970.1]
• Step By Step Guide To Configuring 10g Password Authenticated Enterprise User Security [ID 272196.1]
• Step by Step Guide To Troubleshooting 10g Enterprise User Security (EUS) – Password Authentication [ID 453853.1]
• Registering a Database in OID Fails, Incorrectly Indicating the Database is Already Registered [ID 372297.1]
• DBCA Database Registration To OVD 11g Fails With TNS-04409 And TNS-04405 [ID 1377901.1]
• How To Configure Directory Authentication for Database Administrative Users (SYSDBA and SYSOPER) [ID 457083.1]
• EUSM, Command Line Tool For EUS Administration and Some EUS Good to Knows [ID 1085065.1]
• Apache Directory Studio

About Post Author

Yannick Jaquier

administrator

Find more about me on social media.

See author's posts