Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2004
    Posts
    3

    Unanswered: Problem with privileges

    Hello,

    I have 2 schemas in my DB, let's say A and B. I want to update a table in schema B from a procedure in schema A. If I execute the update statement directly in sqlplus (in schema A) than there is no problem with privileges. I can update the table (in schema B).
    But if I put this statement inside a procedure (in schema A) I always get an Insufficient Privileges message.

    How can I solve it?

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Privileges granted through roles are not valid in PL/SQL. So, if you granted UPDATE to a role (and role to the user), it won't work. You'll have to grant required privileges directly to the user.

  3. #3
    Join Date
    Dec 2003
    Location
    Oklahoma, USA
    Posts
    354
    One problem you might be experiencing is in how Oracle handles the execution of procedures, functions, and packages. If you create, for instance, a procedure as such:

    Code:
    CREATE OR REPLACE PROCEDURE my_proc IS 
    ...
    SELECT col1 INTO my_var FROM table1;
    ...anyone granted execute privs on this procedure will execute as if they were the owner of the procedure. In this case, the schema owner of the procedure would have to have to have the correct privileges to select from table1.

    If, however, you create the procedure like this:

    Code:
    CREATE OR REPLACE PROCEDURE my_proc
    AUTHID CURRENT_USER IS
    ...
    SELECT col1 INTO my_var FROM table1;
    In this case, the schema owner would have to have SELECT privs on the table so the procedure could compile, but when the user executes the proc, Oracle would check to see if the user had the correct privs on the table, otherwise the user would get a table not found error.

    The first example is what Oracle calls DEFINER rights, the default (and can be included in the definition as PROCEDURE my_proc AUTHID DEFINER IS...) while the second is called INVOKER rights.

    Hope this explains some things.
    JoeB
    save disk space, use smaller fonts

Posting Permissions

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