RE: Follow-up: It's NOT possible to set role in db's logon

That's a great idea--many thanks. I bet I could put up a table of permitted username/client program combinations & just do a SELECT from it & translate the no_data_found exception into a 'connect via <<your program>> verboten!' message...

Thanks again,

-Roy

Roy Pardee
Programmer/Analyst
SWFPAC Lockheed Martin IT
Extension 8487

-----Original Message-----
Sent: Wednesday, January 29, 2003 6:04 AM To: Multiple recipients of list ORACLE-L

Hi Roy,

I don't know if you solved your problem yet but I have a similar situation here. I have an Oracle account used by PHP programs (third party programs) to access some tables. I don't want anyone to log in to the database with this account unless the connection comes from apache and from our web server machine. So what I did is that I created a logon trigger on that schema and if the conditions are not met, then I raise an application error and the connection dies.

As you know, these informations (program, machine, etc...) can be found in v$session and the SID of the current session can be found with "select sid from v$mystat where rownum = 1".

HTH. Louis

At 15:13 2003-01-27 -0800, you wrote:
>In case anyone cares--it looks like it is *not* possible to set a role in
an
>after logon trigger. Had I only looked at metalink:
>
>AFTER LOGON Triggers Don't Allow DBMS_SESSION.SET_ROLE to Keep Roles
Enabled
>http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_data
b
>ase_id=NOT&p_id=106140.1
>
>Bummer, that.
>
>Thanks again to all who responded.
>
>Cheers,
>
>-Roy
>
>Roy Pardee
>Programmer/Analyst
>SWFPAC Lockheed Martin IT
>Extension 8487
>
>-----Original Message-----
>Sent: Monday, January 13, 2003 7:42 AM
>To: 'ORACLE-L'
>
>
>Greetings all,
>
>I'm trying to support a COTS application that is back-end agnostic & makes
>only minimal use of security on the db. In particular, it requires that
>users be granted a default role that has *very* heavy permissions--enough
to
>do some major mischief should they ever figure out how to use odbc or
>sql*plus.
>
>My collegues & I have devised a kludgy method for getting around this
>problem, involving a shill startup program that turns the default-ness of
>the role on & off in conjunction with users opening & closing the client
>program. This works, but is a pain to maintain.
>
>I've recently discovered the v$session.program field & am now wondering
>whether it would be possible to use the new-fangled logon system trigger to
>set the role only for cases where v$session.program = the COTS client.
>
>Can anybody comment as to whether this is a viable approach on an 8.1.6
>database & if not, on a 9i db?
>
>In particular, there are two things I don't know--first, how to select just
>the one row in v$session that corresponds to the current connection. If a
>user was to start up the COTS client & then connect to the same db via
>sql*plus, I would want the role set *only* for the COTS client session. My
>best thought so far here is to use the most recently started connection
>based on v$session.logon_time.
>
>Second, whether the SET ROLE statement is legal in a logon trigger.
>
>All help will be most welcome.
>
>Thanks!
>
>-Roy
>
>Roy Pardee
>Programmer/Analyst
>SWFPAC Lockheed Martin IT
>Extension 8487
>--
>Please see the official ORACLE-L FAQ: http://www.orafaq.net
>--
>Author: Pardee, Roy E
> INET: roy.e.pardee_at_lmco.com
>
>Fat City Network Services -- 858-538-5051 http://www.fatcity.com
>San Diego, California -- Mailing list and web hosting services
>---------------------------------------------------------------------
>To REMOVE yourself from this mailing list, send an E-Mail message
>to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
>the message BODY, include a line containing: UNSUB ORACLE-L
>(or the name of mailing list you want to be removed from). You may
>also send the HELP command for other information (like subscribing).

Louis Brouillette
Analyste en informatique (DBA)
Universite du Quebec a Trois-Rivieres
Tel: (819) 376-5011 ext. 2435
Email: brouille_at_uqtr.ca

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Louis BROUILLETTE
  INET: Louis_Brouillette_at_UQTR.CA

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Pardee, Roy E
  INET: roy.e.pardee_at_lmco.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).