On our production database there is the ability for the users to query the database with some long running, date intensive queries. We came up with a method of having the user kill is't own process if needed. We user roles to allow the users acces to the data. Granted select on V_$MYSTAT AND V_$SESSION to the role. Created a special ID that has the ability to kill the sessions. When needed the application will select the session_id and serial# and pass it on the the special ID and it will kill the session. Works great and the users do not have access to anything except what is granted in the role. Ron
>>> [EMAIL PROTECTED] 09/05/03 04:09AM >>> A good response Arup, gave me food for thought, now for some food for my stomach! Thanks John -----Original Message----- Sent: 01 September 2003 18:14 To: Multiple recipients of list ORACLE-L It's hardly illogical. The purpose of this view is not to provide the SID of the current session, but to show statistics, some of which the DBA may not want a user to know in all cases. Therefore the view is granted to a role SELECT_CATALOG_ROLE, similar to most other dictionary views. Any user with that role can see the view. However, granting the role SELECT_CATALOG_ROLE to all users is not advisable at all. If a user needs it, a very simple "grant select on v_$mystat to <username>" can be issued which will resolve the problem. At a development site, or a site where the security for this view is not quite pronounced, you can issue the follwing which will make everyone see this view. grant select on v_$mystat to public; This will make the view visible to even the users created after this command. The decision to hide this view from normal users is deliberate; but breaking that lock is not hard either using a simple grant as shown. Going back to the discussion on why it has been so hard for a user to know his/her SID - why does a user need to know the SID? To alter the DBA for some monitoring action and seeing the SID will help the DBA see that in V$SESSION? In that case, the proper technique is to set the client identifier using DBMS_APPLICATION_INFOR.SET_IDENTIFIER procedure. The value shows up in v$session, no need to know the SID. What else? In a web app maintain state between pages using SID? this approach is fraight with errors. First, SID is not guaranteed to be unique; SID and SERIAL# combination is. To guarantee a unique identifier, use the function DBMS_SESSION.UNIQUE_SESSION_ID to get an id that is unique in a session and use it to maintain state. If it is necessary that the user must know the SID, then the V$MYSTAT can be queried. Hope this helps. Arup Nanda ----- Original Message ----- To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Monday, September 01, 2003 4:54 AM > It does seem illogical than Oracle provide a view called mystat which tells you about your own statistics, including SID and yet a normal user does not have access to that view unless specifically given. > > John > > -----Original Message----- > Sent: 30 August 2003 15:04 > To: Multiple recipients of list ORACLE-L > > > > But the user need select access to v$mystat > > connect as sysdba and grant select on v_$mystat to <user>; > > HTH > GovindanK > > > Thanks all for the input. > > > > the script is select sid,serial# from v$session where sid=(select sid from > > v$mystat where rownum=1) > > > > But the user need select access to v$mystat > > > > Chuan > > > > -----Original Message----- > > Sent: Friday, 29 August 2003 15:19 > > To: Multiple recipients of list ORACLE-L > > > > > > Hi Chuan, > > > > can v$session help you > > > > this view has information like machine, osuser, username, sid, program, > > and > > others > > > > SQL> desc v$session > > > > SQL> select columns,... from v$session where username = 'SCOTT' and > > machine > > = 'YOUR_HOSTNAME' > > > > if you do telnet you will get 2 rows (if scoot is only use by you) > > > > is not > > > > Sinardy > > > > -----Original Message----- > > Sent: 29 August 2003 12:34 > > To: Multiple recipients of list ORACLE-L > > > > > > DABs, > > > > Is there any way in my connection to get the sid and serial# for my own > > connection? > > > > Suppose I connect to Oracle db by >sqlplus scott/[EMAIL PROTECTED] > > In this connection, > > > > SQL> > > > > What shoud I input to get this sid and serial#? > > > > TIA > > > > Chuan > > Important: This transmission is intended only for the use of the addressee > > and may contain confidential or legally privileged information. If you > > are > > not the intended recipient, you are notified that any use or dissemination > > of this communication is strictly prohibited. If you receive this > > transmission in error please notify the author immediately by telephone > > and > > delete all copies of this transmission together with any attachments. > > > > -- > > Please see the official ORACLE-L FAQ: http://www.orafaq.net > > -- > > Author: > > 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: Sinardy Xing > > 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). > > Important: This transmission is intended only for the use of the addressee > > and may contain confidential or legally privileged information. If you > > are > > not the intended recipient, you are notified that any use or dissemination > > of this communication is strictly prohibited. If you receive this > > transmission in error please notify the author immediately by telephone > > and > > delete all copies of this transmission together with any attachments. > > > > -- > > Please see the official ORACLE-L FAQ: http://www.orafaq.net > > -- > > Author: > > 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: Corniche Park > 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: Hallas, John, Tech Dev > 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: Arup Nanda 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: Hallas, John, Tech Dev 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: Ron Rogers 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).
