Skip to Main Content


Question and Answer

Tom Kyte

Thanks for the question, Joseph.

Asked: March 20, 2003 - 5:41 pm UTC

Answered by: Tom Kyte - Last updated: December 28, 2007 - 3:53 pm UTC

Category: Database - Version:

Viewed 1000+ times

You Asked

Hi Tom,

I have successful configure Oracle enterprise user security where all clients (to be 10-30,000) is centralize manage within the Oracle OID. The web application connect all our client to database via a share schema using JDBC thin driver.

1) For enterprise user that connect through share schema, the sys_context('userenv', 'session_user') does not show the true client user as session_user. The only place that I see the enterprise user audit information is in the comment_text column of the dba_audit_trail table. Also, our project require make use of the Oracle VPD (fined grain access control). Can you please show how to obtain the enterprise user (LDAP user) as session user???

2) As part of the enterprise user configuration, we have to use the Oracle provide Enterprise Security Manager GUI to create LDAP Enterprise Global Role then map the global role to database role then the whole bunch of other steps.... Is it possible to duplicate Enterprise Security Manager functionality within our Web application??? what I means is to implement Enterprise Security manager as part of our web application instead of run it seperately out side of our application.


Joseph Nguyen

and we said...

I asked David Knox, our Chief Engineer in the Oracle Information Assurance Center to look at this and he says:

1) Correct, the session_user is the database schema that you mapped your enterpise user to. Enterprie Users can be seen in the 'external_name' value of the USERENV i.e., sys_context('userenv','external_name') This gives you the distinguished name of the user. Base your VPD policies on this value.
You can easily write a function to format the name into something easier (such as the common name attribute) if you so choose.

CREATE OR REPLACE function application_username (p_external_name in varchar2 default SYS_CONTEXT('USERENV','EXTERNAL_NAME')) return varchar2
return upper(substr(p_external_name,4,instr(p_external_name,',')-4));

Connected to:
Oracle9i Enterprise Edition Release - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release - Production

SQL> select sys_context('userenv','external_name') from dual;


cn=Knox,ou=CONTRACTOR,ou=PKI,ou=DoD,o=U.S. Government, c=US

1 row selected.

SQL> select application_username from dual;



1 row selected.


2) Not sure what you application is supposed to do. First for clarification,
Enterprise Security Manager (ESM) provides the capability to map enterprise roles (stored in OiD) to global roles (stored in the DB's). ESM also assigns enterprise roles to enterprise users. The users get their privileges from the database global roles based on how those roles are mapped to the
enterprise roles assigned for them. So, ESM is an administration tool, and its functionality should probably not be incorporated into your application (Not knowing what your application is trying to do, I can only assume it is not for provisioning new users or assigning authorizations to them.) Your application will just use privileges assigned to the global roles. Please let me know if I
misunderstood what your application is supposed to do.

and you rated our response

  (8 ratings)

Is this answer out of date? If it is, please let us know via a Review


Enterprise User Security

March 21, 2003 - 10:03 am UTC

Reviewer: Joseph Nguyen from Vienna, VA USA

Thanks Tom and great solutions as usual. As for the second question, is it possible for me to configure Enterprise User within my application? The scenerio would be without using the ESM tool.
1) Our web application GUI create new LDAP user within oracle OID.
2) now instead of using the ESM admin to configure this new LDAP user. Is it possible to write a PLSQL package to customize this new enterprise user??? The idea is not to use the ESM but to develop our application so that every new created LDAP user will get properly configure as if ESM is used to configure.

Tom Kyte


March 21, 2003 - 5:22 pm UTC

OID has a rich programming interface ( C, PL/SQL, Java) that allows you to create a user in the directory - which can be leveraged for basic user creation purposes. However, password management for database authentication and other enterprise user security related administration must be done through our tools

Password management

August 18, 2004 - 12:18 pm UTC

Reviewer: Sami


Do you think its a good idea to update user's password using ESU ( in 9i world)? If we have several databases applications with several thousands of OID users then the password management would be a nightmare for the OID administrator. Why should Enterprise User Adminstrator be allowed to update the password of another user? In my view every application must have a way to update the password when password is expiring.

You are suggesting that using DBMS_LDAP_UTL PL/SQL Package or C we can create an Enterprise User then why not a passowrd change.

If we donot have 9iAS SSO then the life for the adminsitrator would be miserable, is this a right assumption?



Tom Kyte


August 18, 2004 - 12:25 pm UTC

if you have N databases and N>1
Enterprise users makes sense
database users make sense
end if;

do you like having 10 passwords for 10 databases each of them expiring at different times? I don't.

Password update

August 18, 2004 - 12:45 pm UTC

Reviewer: Sami

We have 7 Oracle database running , one 11i , 6 customs application and they all are running on 3 different boxes. We are in the process of setting OID/EUS to get rid of db users and we donot want to maintain 7 database users for 7 applications. Every user would be assigned to only one OID user/password. Some of our applications are developed in Java, Cold Fusion and so on so and every application has its own administrative module and users are allowed to change their password.

We would like to maintain the same features and using LDAP APIs ( DBMS_LDAP.populate_mod_array), we would like to update the password. Is this not possible by creating a cutom pl/sql or java/c application?

Tom Kyte


August 18, 2004 - 1:12 pm UTC

I'll ask around, sort of outside of my area of expertise.


August 18, 2004 - 1:41 pm UTC

Reviewer: Sami

Tom and if you could also find out on which schema we should create these pl/sql procedures , I mean under ODS or under global's user .


Enterprise User Security

February 15, 2005 - 1:13 pm UTC

Reviewer: Rich from Chicago, IL

Will the following stmt work whether or not we are using

SQL> select sys_context('userenv','external_name') from dual;

We will be using a similar setup but I have no experience
working with LDAP. 

Tom Kyte


February 15, 2005 - 3:45 pm UTC

it'll work.

Global roles on non-Oracle LDAP

May 09, 2005 - 7:04 pm UTC

Reviewer: Rich from Chicago, IL

Hi Tom,

Can we use a non-OID LDAP server for Global roles?
For example:

create role role1 identified globally;

The 9i Admin guide says:

"Global roles are one component of enterprise user

Though I'm not sure if it refers to "enterprise user
management" as a product or a process. Or do we need
Oracle Advanced Security to perform "enterprise user
management" ?

Many thanks for your help.

Tom Kyte


May 10, 2005 - 11:00 am UTC

I asked David Knox, our security expert and he said:

You have to have OiD (Enterprise Users) for global roles. The DB, upon a successful authentication, looks up the user's enterprise roles in the directory. Each enterprise role consists of N number of database roles. The database checks for all global roles for the specific user and itself and enables the global roles automatically.

In Oracle 9i, Enterprise Users was part of the Advanced Security Option. Now, Enterprise Users are bundled with the Identity Management option of the App Server.

enterprise security and the old good security

May 08, 2006 - 3:39 am UTC

Reviewer: A reader


During my entire career (around 8 years) I have always been told a good practice when develop an application in Oracle RDBMS is using database users as application users so we can enforce the security in the database. There is another good practice which I constantly see which is create a application owner in database, an application user a batch user and several read/write users all enforced using roles.

Are these approaches obsolete? With Enterprise Security all these concepts are gone, with a single user we are no longer to use resource managers effectively anymore.

Is this is just another new fashion?

Tom Kyte


May 08, 2006 - 8:13 am UTC

enterprise security "in effect" just lets you manage the users centrally - without having to create a user account in all "50 of your databases". You still have "database users"

December 28, 2007 - 2:52 pm UTC

Reviewer: Alexander

Hi Tom,

Using EUS only works if the application connects from the middle tier using unique ids that represent an end user right? If the application connects using a generic id that all users come in as, that won't work.

Also, putting all my eggs in one basket doesn't seem like a good idea to me. If all my applications and users are managed in one spot like OiD, if anything happened to that server or database wouldn't everyone be down? Seems much too risky to me. I'd rather manage tnsnames files and only have that effect a few applications and not the entire company wouldn't you? Is my understanding of this feature wrong?
Tom Kyte


December 28, 2007 - 3:53 pm UTC

the middle tier would need to identify the real user, YES (obvious I would think?)

An LDAP repository is a distributed replicated beast with no single point of failure - unless you decide to configure it with one. The largest telco's and ISPs use them - for lots of stuff.