Home » SQL & PL/SQL » SQL & PL/SQL » Session browser access (10.2.0.4/HPUX)
Session browser access [message #395812] Thu, 02 April 2009 11:28 Go to next message
liamadherne
Messages: 40
Registered: March 2008
Location: Philadelphia
Member
Hello Everybody,

My applications users need to check the session longops (through TOAD) on contineous basis so that they will get to know how much more time the query execution will take.

So I have granted them the SELECT_CATALOG_ROLE privilege and it serves their purpose.

But as the SELECT_CATALOG_ROLE gives access to the dictionary views hence they can check all the sessions even what SYS is doing, which makes me very restless.

Is there any way I can give them the privilege so that they can check the session longops of their session only.
The O7_DICTIONARY_ACCESSIBILITY parameter is set to FALSE.

Any seggestion would be greatly appreciated.
Liam.
Re: Session browser access [message #395813 is a reply to message #395812] Thu, 02 April 2009 11:34 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
Write a PL/SQL procedure which is owned by a suitably privileged user that does the query.
GRANT EXECUTE ON LONGOP_PROC TO DEV1;

This way they can see what they need to see & nothing more.

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Re: Session browser access [message #395815 is a reply to message #395812] Thu, 02 April 2009 11:41 Go to previous messageGo to next message
Michel Cadot
Messages: 64135
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
As SYS create a view that restrict v$session_longops to the current user (for instance) (I didn't check it so do it):
create or replace view my_session_longops as
select l.* 
from v$session_longops l, v$session s
where l.sid = s.sid and l.serial# = s.serial#
  and s.username = user
/
create or replace public synonym v$session_longops for my_session_longops
/
grant select on my_session_longops to public
/

Regards
Michel

Re: Session browser access [message #395955 is a reply to message #395812] Fri, 03 April 2009 05:24 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Unless the SYS user is doing something they shouldn't, why is this going to be a problem?
Re: Session browser access [message #397726 is a reply to message #395812] Mon, 13 April 2009 09:38 Go to previous messageGo to next message
it_me24
Messages: 167
Registered: March 2006
Location: delhi
Senior Member
Hi,

Can you suggest anythin more to restrict the users to view there session details only.


Waiting for your inputs!
Re: Session browser access [message #397728 is a reply to message #397726] Mon, 13 April 2009 09:48 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Quote:
Hi,

Can you suggest anythin more to restrict the users to view there session details only.


Waiting for your inputs!

Why? What exactly is wrong with the suggestions already provided? (It is extremely rude to ignore people you know.)
Re: Session browser access [message #397733 is a reply to message #397726] Mon, 13 April 2009 10:24 Go to previous messageGo to next message
Michel Cadot
Messages: 64135
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
it_me24 wrote on Mon, 13 April 2009 16:38
Hi,

Can you suggest anythin more to restrict the users to view there session details only.


Waiting for your inputs!

Use the view I posted and change it a little bit to replace "user sessions" by "own session".

Regards
Michel

Re: Session browser access [message #397734 is a reply to message #395812] Mon, 13 April 2009 10:28 Go to previous messageGo to next message
it_me24
Messages: 167
Registered: March 2006
Location: delhi
Senior Member
Thanks pablolee for your suggestion!

But I did try the above things, unfortunately nothing seemed to be helping me.


I did run the query which Micheal is give, and after that I got the error while trying to access the SESSION BROWSER through TOAD.

SQL> 
SQL> conn sys as sysdba
Enter password: 
Connected.
SQL> 
SQL> 
SQL> create or replace view my_session_longops as
  2  select l.* 
  3  from v$session_longops l, v$session s
  4  where l.sid = s.sid and l.serial# = s.serial#
  5    and s.username = user
  6  /
create or replace public synonym v$session_longops for my_session_longops
/
grant select on my_session_longops to public
/
View created.

SQL>   2  
Synonym created.

SQL>   2  

Grant succeeded.


The same works fine if I give the user SELECT_CATALOG_ROLE to the user.


Attached is the screenshot for your reference.
Re: Session browser access [message #397735 is a reply to message #397734] Mon, 13 April 2009 11:12 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
The error is quite self-explanatory.
Either provide access to those views, or create restricted views for those, similar to your session_longops view, or stop doing stuff you are not supposed to do.
(I agree with JRowbottom that you should not make a big deal out of the fact that others can see what dbas do)
Previous Topic: please help to optimize my sql
Next Topic: Time spent for the statements inside a procedure
Goto Forum:
  


Current Time: Thu Dec 08 06:04:18 CST 2016

Total time taken to generate the page: 0.09398 seconds