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: [EMAIL PROTECTED] > >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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Louis BROUILLETTE INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
