Re: accessing user_users via a procedure in SYS

Patrice,
 I've just tried your idea out and it seems to work well. I tried on Oracle 9.0 but it should be the same on 816 or 817.

Here is the procedure that kills the sessions. It uses ora_login_user to identify the calling username.

create or replace procedure endsession2 (sid_in in number) as
cursor c1 is
select 'alter system kill session '||chr(39)||sid||','||serial#||chr(39) endsession
from v$session
where username = ora_login_user
and sid != sid_in;
begin
for x in c1 loop
 execute immediate x.endsession;
end loop;
end;

The procedure with invoker rights finds the current user's sid

create or replace procedure endsession1
AUTHID CURRENT_USER AS
l_sid number;

begin
select sid
into l_sid
from v$mystat
where rownum = 1;

system.endsession2(l_sid);
end;
/

I created the two procedures in system and granted execute on endsession2 to public

SQL> show user
USER is "SCOTT"
SQL> execute system.endsession1

PL/SQL procedure successfully completed.

Hth,
Chaim

"Boivin, Patrice J" <BoivinP_at_mar.dfo-mpo.gc.ca>@fatcity.com on 08/30/2002 03:08:35 PM

Please respond to ORACLE-L_at_fatcity.com

Sent by: root_at_fatcity.com

To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> cc:

Hi,

I am trying to put together a package that uses the AUTHID current_user directive to decide whether to let people kill a session or not, and I ran into a problem.

One procedure will run as SYS, but I need to double-check the requester's username to ensure he/she isn't trying to kill their neighbour's session.

To do that I build a function that runs with the AUTHID current_user directive and tries to select username from user_users. Hopefully it will be able to pass back the username to the parent proc.

When I try to run a procedure that does a select against user_users using that directive, I get an error:
PLS-00357: Table,View Or Sequence reference 'USER_USERS' not allowed in this
context

I found tech note 162489.1, according to that, shouldn't user_users work as well?

Regards,
Patrice Boivin
Systems Analyst (Oracle Certified DBA)

Systems Admin & Operations | Admin. et Exploit. des systèmes
Technology Services        | Services technologiques
Informatics Branch         | Direction de l'informatique
Maritimes Region, DFO      | Région des Maritimes, MPO

E-Mail: boivinp_at_mar.dfo-mpo.gc.ca

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Boivin, Patrice J
  INET: BoivinP_at_mar.dfo-mpo.gc.ca

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
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.com
-- 
Author: 
  INET: Chaim.Katz_at_Completions.Bombardier.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
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).