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

Roy,

I missed the first run of the question, so you've probably had this answer already.

You can set a role inside a procedure if the procedure is declared with invoker
rights (authid current_user) although the procedure cannot then be run in a logon
trigger.

However, in Oracle 9, you could define the role as an application role protected by a package. If COTS attaches to the database using OCI, then you could consider using the PROXY_USER features. In this case,
COTS connects as "itself", then becomes
the end-user, without knowing the end-user password. Your package could then set
the role based on the fact that

    sys_context('userenv','proxy_user')
was 'COTS'.

When the user logs in normally, their
'proxy_user' value will be null. The only way that they could switch on the role
would be to write their own OCI program
that logged on as COTS first - which means they'd have to know the COTS password
anyway, so your data would have been
unprotected anyway.

Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

Coming soon a new one-day tutorial:
Cost Based Optimisation
(see http://www.jlcomp.demon.co.uk/tutorial.html )

____UK_______March
____USA_(FL)_May

Next Seminar dates:
(see http://www.jlcomp.demon.co.uk/seminar.html )

____USA_(CA, TX)_August

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

-----Original Message-----
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> Date: 27 January 2003 23:48
trigger

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 _datab
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

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jonathan Lewis
  INET: jonathan_at_jlcomp.demon.co.uk

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).