Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2004
    Posts
    14

    Unanswered: Confusing permission problem

    We've got two users set up that should have full access to each other's schema. Within SQL it's not an issue to just copy a table from one user to another, but trying to do the identical thing inside a PL/SQL procedure always results in an insufficient privilege error.

    Here's a procedure that always fails with that error:

    Code:
    PROCEDURE TEST_PERMS IS
    BEGIN
        execute immediate 'CREATE TABLE TD_PARENTS AS SELECT * FROM DEPPRD.TD_PARENTS';
    END;
    Oddly, you can just run the SQL like this:
    Code:
    BEGIN
        execute immediate 'CREATE TABLE TD_PARENTS AS SELECT * FROM DEPPRD.TD_PARENTS';
    END;
    ...and not get the error.


    Any ideas?

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    When inside of PL/SQL privs which are acquired via a ROLE do NOT apply/count.

    GRANT SELECT ON TD_PARENTS TO <OTHER_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
    Mar 2004
    Posts
    14
    Thanks! I think that puts me on the right track. It looks like the problem is creating the file in the current user, not the select from the other user. I think they set up this user with role permissions.

Posting Permissions

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