Results 1 to 14 of 14
  1. #1
    Join Date
    Jul 2003
    Location
    Hong Kong
    Posts
    56

    Arrow Unanswered: Can't access table through PL/SQL

    The Oracle server has two user, each has a table and a role.

    UserA.TableA
    UserB.TableB

    RoleTmp : select any table

    RoleTmp has only one system privilege which is the one above.

    Now, UserA has been granted the role RoleTmp which means that UserA can select from UserB.TableB. I have logged in the Orable db with UserA and I have no problem issuing the following sql statement in sqlplus:

    SELECT COUNT(*) FROM UserB.Table

    However, if I embed that sql statement within a PL/SQL block. I receive oracle error:

    ORA-00942: table or view does not exist

    I don't know why I can run the sql in sqlplus but not in PL/SQL.
    Could anyone please give me advice?

    Thanks in advance.
    Cheers,
    Shev

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool

    Privileges must be granted directly to user (not though a role) in order to function in a procedure.

  3. #3
    Join Date
    Jul 2003
    Posts
    2,296
    as long as the tables do not have the same name,
    public synonyms would help too.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  4. #4
    Join Date
    Jul 2003
    Location
    Hong Kong
    Posts
    56
    Thanks LKBrn_DBA. Is there any documentation from Oracle that explain that privilege rule?
    Cheers,
    Shev

  5. #5
    Join Date
    Jul 2003
    Location
    Hong Kong
    Posts
    56
    Originally posted by The_Duck
    as long as the tables do not have the same name,
    public synonyms would help too.
    Thanks The_DUCK. Do you mean that if I create public synonyms for the tables and then I will be able to access these public synonyms in PL/SQL block even if the privilege to select these tables is granted through a role?
    Cheers,
    Shev

  6. #6
    Join Date
    Jul 2003
    Location
    Hong Kong
    Posts
    56

    Arrow

    Anyone has other suggestions for me?

    Thanks
    Cheers,
    Shev

  7. #7
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Originally posted by shev
    Thanks LKBrn_DBA. Is there any documentation from Oracle that explain that privilege rule?
    Look here:

    http://technet.oracle.com/docs/produ...privs.htm#1258

    Search for "PL/SQL Blocks and Roles".

  8. #8
    Join Date
    Jul 2003
    Location
    Hong Kong
    Posts
    56
    Thanks andrewst.

    I am still not very clear on the PL/SQL concept.

    1. How to distinguish between anonymous and named PL/SQL blocks?
    2. How to know which right, definer or invoker the PL/SQL block used? Any example?

    Please kindly help me.
    Cheers,
    Shev

  9. #9
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Originally posted by shev
    Thanks andrewst.

    I am still not very clear on the PL/SQL concept.

    1. How to distinguish between anonymous and named PL/SQL blocks?
    2. How to know which right, definer or invoker the PL/SQL block used? Any example?

    Please kindly help me.
    1. A named block is a stored procedure, function or trigger (i.e. something you have to name). An anonymous block is just like:

    DECLARE
    x INTEGER;
    BEGIN
    SELECT empno INTO x FROM emp WHERE ename='KING';
    END;

    It is not stored, it doesn't have a name. If you wanted to store it, you would have to name it as a procedure or function.

    2. As the documentation says, anonymous blocks use invokers rights; named blocks use definer's rights by default, but you can make them use invoker's rights via the AUTHID CURRENT_USER clause.

  10. #10
    Join Date
    Jul 2003
    Location
    Hong Kong
    Posts
    56
    Thanks again andrewst.

    Is it possible to make a stored procedure inside a package to use invoker's rights via the AUTHID CURRENT_USER clause? How?
    Cheers,
    Shev

  11. #11
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Originally posted by shev
    Thanks again andrewst.

    Is it possible to make a stored procedure inside a package to use invoker's rights via the AUTHID CURRENT_USER clause? How?
    From the manual:

    Code:
    PROCEDURE procedure_name[(parameter[, parameter]...)]
       [AUTHID {DEFINER | CURRENT_USER}] {IS | AS}
       [PRAGMA AUTONOMOUS_TRANSACTION;]
       [local declarations]
    BEGIN
       executable statements
    [EXCEPTION
       exception handlers]
    END [name];
    Doesn't matter if the procedure is in a package or not.

  12. #12
    Join Date
    Jul 2003
    Location
    Hong Kong
    Posts
    56
    I receive the error after added the AUTHID clause:

    PLS-00157: AUTHID only allowed on schema-level programs

    My procedure is like this:

    TYPE t_refcursor IS REF CURSOR;

    PROCEDURE TEST1 (p_out OUT t_refcursor)
    AUTHID CURRENT_USER
    IS
    BEGIN
    OPEN p_out
    FOR
    SELECT COUNT(*) FROM b.t;

    END TEST1;

    Any idea? Thanks.
    Cheers,
    Shev

  13. #13
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Originally posted by shev
    I receive the error after added the AUTHID clause:

    PLS-00157: AUTHID only allowed on schema-level programs

    My procedure is like this:

    TYPE t_refcursor IS REF CURSOR;

    PROCEDURE TEST1 (p_out OUT t_refcursor)
    AUTHID CURRENT_USER
    IS
    BEGIN
    OPEN p_out
    FOR
    SELECT COUNT(*) FROM b.t;

    END TEST1;

    Any idea? Thanks.
    Sorry, I misled you there. For packages you declare the AUTHID at package level:

    CREATE [OR REPLACE] PACKAGE package_name
    [AUTHID {CURRENT_USER | DEFINER}]
    {IS | AS}

    i.e. whole package uses either invoker or definer rights - not a mixture.

  14. #14
    Join Date
    Jul 2003
    Location
    Hong Kong
    Posts
    56

    Thumbs up

    Thank you so much!!
    Cheers,
    Shev

Posting Permissions

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