Results 1 to 11 of 11
  1. #1
    Join Date
    Jul 2004
    Posts
    10

    Unanswered: V$session is accessed from schema but from procedure not

    Hi ,

    I have a small piece of code which I have used in a stored procedure the code given bellow:

    1 CREATE OR REPLACE PROCEDURE testProc (
    2 result OUT NUMBER,
    3 nrTime IN INTEGER,
    4 trTime IN INTEGER,
    5 bapiName IN VARCHAR )
    6 AS
    7 rcDate CHAR(8);
    8 rcTime CHAR(6);
    9 uName VARCHAR(64);
    10 uNameTr CHAR(12);
    11 BEGIN
    12 /* 1. Get current date and time and setup RowCreateDate */
    13 result := 0;
    14 rcDate := TO_CHAR(SYSDATE,'YYYYMMDD');
    15 rcTime := TO_CHAR(SYSDATE,'HHMMSS');
    16
    17 /* 2. Get the user name and extract 1st 12 characters */
    18 SELECT osuser
    19 INTO uName
    20 FROM v$session
    21 WHERE audsid = USERENV('sessionid');
    22 uNameTr := SUBSTR(uName,1,12); /* only use 1st 12 characters to align to SAP length */
    23
    24
    25 END testProc;
    26 /

    What I find is given bellow:
    Warning: Procedure created with compilation errors.

    SQL> show errors
    Errors for PROCEDURE TESTPROC:

    18/1 PL/SQL: SQL Statement ignored
    20/6 PL/SQL: ORA-00942: table or view does not exist

    but if I run the sql from the sqlplus straight it works fine. Given below.

    1 SELECT osuser FROM v$session
    2 WHERE audsid = USERENV('sessionid');

    rajat

    How can I compile the procedure ? Please help

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    PRIVS acquired via a ROLE do NOT apply within PL/SQL procedures.
    GRANT SELECT ON V$SESSION TO ACTUAL_USER
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    May 2006
    Posts
    132
    To avoid having to grant the privilege directly, you could consider invoker rights and dynamic SQL.

  4. #4
    Join Date
    Jul 2004
    Posts
    10

    V$session is accessed from schema but from procedure not

    I can not access the database as sys user. I have only access as system user.Can you guys please find any other alternative solution which will serve the same purpose? Cause, I believe that as the v$session table is owned by sys user no other user can give grant any privilege on it.

  5. #5
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    If you need to use v$session (a synonym for v_$session), it appears that you'll have to contact your DBA and ask him/her to grant you required privileges.

  6. #6
    Join Date
    May 2006
    Posts
    132
    Quote Originally Posted by rray
    I can not access the database as sys user. I have only access as system user.Can you guys please find any other alternative solution which will serve the same purpose? Cause, I believe that as the v$session table is owned by sys user no other user can give grant any privilege on it.
    I'm confused, you mentioned in your original post that you can query the view outside of the procedure, therefore it's irrelevant who owns the table/view. Two alternatives were already provided, what other solution do you want?

  7. #7
    Join Date
    Jul 2004
    Posts
    10
    If I am an application developer can any of the options help me if I don't know the password of the SYS user ? If yes please tell me the steps.

  8. #8
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    As SYSTEM can you
    GRANT SELECT_CATALOG_ROLE TO SCHEMA_USER?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  9. #9
    Join Date
    Jul 2004
    Posts
    10
    Hi guys,
    I also granted the select_catalogue_role to the schema_owner but still it did not work. But any way I found that after I granted select any dictionary role it worked perfectly. My Procedure could be compilede.

    Regards

  10. #10
    Join Date
    Nov 2006
    Location
    Indianapolis
    Posts
    90
    Why not use sys_context('USERENV','OS_USER') instead of querying v$session?

  11. #11
    Join Date
    Feb 2010
    Posts
    1
    Hi Guys,

    For using v$session in the procedure, grant you the privilege first. I tried, its working.

    grant select on v_$session to SCHEMA_USER;

    dont use, "grant select on v$session to SCHEMA_USER;" since its a synonym of v_$session

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •