Return-Path: Delivered-To: 2-oracle-l@orafaq.com Received: (qmail 22942 invoked from network); 7 Jul 2006 13:19:00 -0500 Received: from freelists-180.iquest.net (HELO turing.freelists.org) (206.53.239.180) by 69.64.49.119 with SMTP; 7 Jul 2006 13:18:40 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id DF113393FED; Fri, 7 Jul 2006 14:18:23 -0400 (EDT) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 29819-03; Fri, 7 Jul 2006 14:18:23 -0400 (EDT) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 503EC39373E; Fri, 7 Jul 2006 14:18:23 -0400 (EDT) Received: with ECARTIS (v1.0.0; list oracle-l); Fri, 07 Jul 2006 14:00:22 -0400 (EDT) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id AA59B393FD2 for ; Fri, 7 Jul 2006 14:00:22 -0400 (EDT) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 26223-08 for ; Fri, 7 Jul 2006 14:00:22 -0400 (EDT) Received: from sxexscan2.qg.com (SXEXSCAN2.qg.com [161.49.252.5]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 4FA98393FC2 for ; Fri, 7 Jul 2006 14:00:22 -0400 (EDT) Received: from unknown (HELO sxexsmtp1.qg.com) ([192.168.2.28]) by sxexscan2.qg.com with ESMTP; 07 Jul 2006 13:01:59 -0500 X-IronPort-AV: i="4.06,218,1149483600"; d="scan'208,217"; a="55442678:sNHT26026567" Received: from sxexpub1.qg.com ([172.25.24.6]) by sxexsmtp1.qg.com with Microsoft SMTPSVC(6.0.3790.1830); Fri, 7 Jul 2006 13:00:29 -0500 Received: from QTEX1.qg.com ([172.25.130.31]) by sxexpub1.qg.com with Microsoft SMTPSVC(6.0.3790.1830); Fri, 7 Jul 2006 13:00:28 -0500 X-MimeOLE: Produced By Microsoft Exchange V6.5 Content-class: urn:content-classes:message MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----_=_NextPart_001_01C6A1EF.3ABE9C7D" Subject: RE: Database access using LDAP Authentication Date: Fri, 7 Jul 2006 13:00:28 -0500 Message-ID: In-Reply-To: <644FEC123EADB4409DE7C3E1AE82D9E101B2C4A4@x.acs.utah.edu> X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: Database access using LDAP Authentication Thread-Index: AcZweBm76V38unSPQe+QZZCnIHB1LwucVsAwAFgzN6AFromWilsonTyfanietwilsonacsutahed97cvmlA= From: "Jesse, Rich" To: X-OriginalArrivalTime: 07 Jul 2006 18:00:28.0985 (UTC) FILETIME=[3B3E9290:01C6A1EF] X-archive-position: 36836 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-to: oracle-l-bounce@freelists.org X-original-sender: Rich.Jesse@qg.com Precedence: normal Reply-to: Rich.Jesse@qg.com X-list: oracle-l X-Virus-Scanned: by amavisd-new-20030616-p10 (Debian) at avenirtech.net ------_=_NextPart_001_01C6A1EF.3ABE9C7D Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable Yep, as Mark mentioned, what you want to do isn't cheap in the Oracle World. Perhaps you could start here for a primer: http://download-east.oracle.com/docs/cd/B10501_01/network.920/a96582/gal syste.htm#1017957 While I haven't played with this aspect of LDAP in Oracle (currently implementing network Naming in LDAP), I would think that you wouldn't necessarily need the Wallets, Enterprise Login, and all the SSO stuff. Just the expensive licensing. And, funny, but I don't see the Enterprise Edition as being needed for LDAP naming, but I could be wrong. Also, I believe the LDAP naming is on a user-by-user basis. In other words, you could setup the SYSTEM account on each of your 40 DBs with your standard local passwords, but have a "HR" account in each that uses LDAP authentication, or have some "HR" accounts LDAP and some local. No triggers are needed -- look up the "CREATE USER" command, specifically the "IDENTIFIED GLOBALLY" clause. In a nutshell, you've got quite a task on your hands. Have fun learning! Rich -----Original Message----- From: oracle-l-bounce@freelists.org [mailto:oracle-l-bounce@freelists.org] Sent: Wednesday, July 05, 2006 12:48 PM To: oracle-l Subject: Database access using LDAP Authentication All: Jr. DBA here looking for a little help on a project she's been given. Any thoughts & ideas you have are greatly appreciated. THE SITUATION:=20 After Collaborate06, I suggested to our managers that we use Profiles on our 40+ databases for added security. After some hemming & hawing, security group agreed, and we began to put Profile Plans into motion. At this time the department realized that if they had a direct Database account, they would have to change their password, which meant in some circumstances, on all 40 databases. This caused some grumbling, but it wasn't too bad. At this time the head of Systems said 1 word to the head DBA that would simultaneously make all the profile research instantly trash and my life hell: LDAP. Yes, he wants us to have oracle use LDAP for it's user/schema authentication. SYSTEMS: Oracle 9.2.0.6 LDAP v3 (not Oracle's LDAP) All 40+ databases & 2 LDAPs are on different Unix boxes. EXAMPLE OF WANTED RESULTS: To make sure I am not being difficult, here's the prime example: 1) I open SQLPLUS and type in my Oracle Userid & password (scott/tiger). 2) Oracle then somehow takes the userid & password to a centralized LDAP. 3) LDAP replies with either: "Yep, that's right" or "No, reject session".=20 4) Oracle then allows access (depending on LDAP's response), and uses it's the users role/sys/tab privs to say what that user has access to. RESEARCH: I have seen where you can authenticate through an htmldb app using the DBMS_LDAP package, but we're not going through a 3rd party app, nor do I think a login server is quite what we're looking for here, but maybe this is how it has to be done?=20 I saw the wonderful "LDAP_AUTHENTICATE procedure for Active Directory" from this list, and tried it as a great jumping-off point, but can't figure out quite how to use it in relation to how Oracle logs in its users. =20 Metalink seems to take you into stray paths, and the SSO books around have to do with 10g, if it's even SSO I'm quite looking for. And Google seems to think I'm insane. QUESTIONS: 1) Is it even possible to use LDAP to authenticate Oracle users directly from Oracle? How would I go about doing this?=20 2) If this is possible, via some sort of login trigger or something, then is it possible to exclude specific users? We obviously don't want SYS or OP$ORACLE going through LDAP due to LDAP Failure making the database useless. Thank you in advance for all of your help, and thanks for all the ideas that this list has given me! -- http://www.freelists.org/webpage/oracle-l ------_=_NextPart_001_01C6A1EF.3ABE9C7D Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

Yep, as Mark mentioned, what you want to do isn't = cheap in the=20 Oracle World.  Perhaps you could start here for a primer:

http://download-east.oracle.com/docs/cd/B10501_= 01/network.920/a96582/galsyste.htm#1017957

While=20 I haven't played with this aspect of LDAP in Oracle (currently = implementing=20 network Naming in LDAP), I would think that you wouldn't necessarily = need the=20 Wallets, Enterprise Login, and all the SSO stuff.  Just the = expensive=20 licensing.  And, funny, but I don't see the Enterprise Edition as = being=20 needed for LDAP naming, but I could be wrong.

Also, I believe the = LDAP=20 naming is on a user-by-user basis.  In other words, you could setup = the=20 SYSTEM account on each of your 40 DBs with your standard local = passwords, but=20 have a "HR" account in each that uses LDAP authentication, or have some = "HR"=20 accounts LDAP and some local.  No triggers are needed -- look = up the=20 "CREATE USER" command, specifically the "IDENTIFIED GLOBALLY" = clause.

In=20 a nutshell, you've got quite a task on your hands.  Have fun=20 learning!

Rich


-----Original Message-----
From:=20 oracle-l-bounce@freelists.org [mailto:oracle-l-bounce@free= lists.org]
Sent:=20 Wednesday, July 05, 2006 12:48 PM
To: oracle-l
Subject: Database = access=20 using LDAP Authentication

All:

Jr. DBA here looking for a = little=20 help on a project she's been given.
Any thoughts & ideas you have = are=20 greatly appreciated.

THE SITUATION: 
After Collaborate06, = I=20 suggested to our managers that we use Profiles on
our 40+ databases = for added=20 security.  After some hemming & hawing,
security group = agreed, and=20 we began to put Profile Plans into motion.
At this time the = department=20 realized that if they had a direct Database
account, they would have = to=20 change their password, which meant in some
circumstances, on all 40=20 databases.  This caused some grumbling, but it
wasn't too = bad.

At=20 this time the head of Systems said 1 word to the head DBA that=20 would
simultaneously make all the profile research instantly trash = and my=20 life
hell:  LDAP.

Yes, he wants us to have oracle use = LDAP for=20 it's user/schema
authentication.

SYSTEMS:
Oracle = 9.2.0.6
LDAP v3=20 (not Oracle's LDAP)
All 40+ databases & 2 LDAPs are on different = Unix=20 boxes.

EXAMPLE OF WANTED RESULTS:
To make sure I am not being=20 difficult, here's the prime example:
1) I open SQLPLUS and type in my = Oracle=20 Userid & password (scott/tiger).

2) Oracle then somehow takes = the=20 userid & password to a centralized
LDAP.
3) LDAP replies with = either:=20 "Yep, that's right" or "No, reject
session". 
4) Oracle then = allows=20 access (depending on LDAP's response), and uses
it's the users = role/sys/tab=20 privs to say what that user has access to.

RESEARCH:
I have = seen where=20 you can authenticate through an htmldb app using the
DBMS_LDAP = package, but=20 we're not going through a 3rd party app, nor do I
think a login = server is=20 quite what we're looking for here, but maybe
this is how it has to be = done? 

I saw the wonderful "LDAP_AUTHENTICATE procedure for = Active=20 Directory"
from this list, and tried it as a great jumping-off point, = but=20 can't
figure out quite how to use it in relation to how Oracle logs = in=20 its
users.  

Metalink seems to take you into stray = paths,=20 and the SSO books around
have to do with 10g, if it's even SSO I'm = quite=20 looking for.  And Google
seems to think I'm=20 insane.

QUESTIONS:
1) Is it even possible to use LDAP to = authenticate=20 Oracle users directly
from Oracle?  How would I go about doing=20 this? 

2) If this is possible, via some sort of login = trigger or=20 something,
then is it possible to exclude specific users?  We = obviously=20 don't want
SYS or OP$ORACLE going through LDAP due to LDAP Failure = making=20 the
database useless.

Thank you in advance for all of your = help, and=20 thanks for all the ideas
that this list has given me!
--
http://www.freelists.o= rg/webpage/oracle-l

------_=_NextPart_001_01C6A1EF.3ABE9C7D-- -- http://www.freelists.org/webpage/oracle-l