This discussion is archived
1 2 Previous Next 21 Replies Latest reply: May 10, 2013 2:55 PM by rp0428 RSS

Can't execute a Grant with Dynamic SQL?

jack m Newbie
Currently Being Moderated
Hi,


Any help would be most appreciated.

I am trying to create a stored procedure that will create all my privilege framework. It queries the dictionary and creates dynamic plsql to create grants.
The problem is that when I run this as a script in SQLPLUS it works, but when I run the same code from a the stored procedure it gives that that the table does not exist.

Thanks for your support and insights.

DECLARE
vTable_Name VARCHAR2(30);
vStatement_Command LONG;

BEGIN
vTable_Name := 'employee';
vStatement_Command := 'GRANT INSERT ON MyShema.' || vTable_Name || ' TO "MyUser"';
DBMS_OUTPUT.PUT_LINE (vStatement_Command);
EXECUTE IMMEDIATE vStatement_Command;
END;
/
  • 1. Re: Can't execute a Grant with Dynamic SQL?
    rp0428 Guru
    Currently Being Moderated
    >
    I am trying to create a stored procedure that will create all my privilege framework. It queries the dictionary and creates dynamic plsql to create grants.
    The problem is that when I run this as a script in SQLPLUS it works, but when I run the same code from a the stored procedure it gives that that the table does not exist.
    >
    Roles are disabled in named PL/SQL blocks unless you using INVOKER rights by defining the code with AUTHID CURRENT_USER.

    You need to grant the privileges directly to the user not by using a role.

    See my reply in this thread for a full discussion and the doc links that explain how roles work.
    cursor query retrieves records in anon pl/sql block but no data in store pr
  • 2. Re: Can't execute a Grant with Dynamic SQL?
    sb92075 Guru
    Currently Being Moderated
    user12055696 wrote:
    Hi,


    Any help would be most appreciated.

    I am trying to create a stored procedure that will create all my privilege framework. It queries the dictionary and creates dynamic plsql to create grants.
    The problem is that when I run this as a script in SQLPLUS it works, but when I run the same code from a the stored procedure it gives that that the table does not exist.

    Thanks for your support and insights.

    DECLARE
    vTable_Name VARCHAR2(30);
    vStatement_Command LONG;

    BEGIN
    vTable_Name := 'employee';
    vStatement_Command := 'GRANT INSERT ON MyShema.' || vTable_Name || ' TO "MyUser"';
    DBMS_OUTPUT.PUT_LINE (vStatement_Command);
    EXECUTE IMMEDIATE vStatement_Command;
    END;
    /
    privileges acquired via ROLE do NOT apply within named PL/SQL procedures.

    Direct GRANT is required to avoid error being thrown.
  • 3. Re: Can't execute a Grant with Dynamic SQL?
    jack m Newbie
    Currently Being Moderated
    Hi, thanks for your replies.

    Not sure if I understand correctly. I'm trying to apply grants directly to a user, not a role. And the message that I get is that the table is not visible to the executing user, which it is.

    What am I getting wrong?
  • 4. Re: Can't execute a Grant with Dynamic SQL?
    rp0428 Guru
    Currently Being Moderated
    >
    Not sure if I understand correctly. I'm trying to apply grants directly to a user, not a role. And the message that I get is that the table is not visible to the executing user, which it is
    >
    It has to be visibe to the executing user by means of a direct grant, not a role.

    Post the DDL that grants the executing user privileges on the table and privileges to issue grants on the table to other users.
  • 5. Re: Can't execute a Grant with Dynamic SQL?
    jack m Newbie
    Currently Being Moderated
    Actually I think I got it... sorry for my previous lack of understanding.

    What you guys meant is that privileges assigned to the role of the user that is executing the named block do not work. Correct?

    Thanks for the info
  • 6. Re: Can't execute a Grant with Dynamic SQL?
    rp0428 Guru
    Currently Being Moderated
    Then please mark the question ANSWERED.
  • 7. Re: Can't execute a Grant with Dynamic SQL?
    jack m Newbie
    Currently Being Moderated
    Actually I used the owner of the table to grant select privileges to the executing user but it still didn't work...

    Maybe I got it wrong...
  • 8. Re: Can't execute a Grant with Dynamic SQL?
    sb92075 Guru
    Currently Being Moderated
    user12055696 wrote:
    Actually I used the owner of the table to grant select privileges to the executing user but it still didn't work...

    Maybe I got it wrong...
    Which USER issue the GRANT?

    post complete GRANT statement that throws error
  • 9. Re: Can't execute a Grant with Dynamic SQL?
    rp0428 Guru
    Currently Being Moderated
    >
    Actually I used the owner of the table to grant select privileges to the executing user but it still didn't work...
    >
    You need to use the WITH GRANT option if you want the executing user to be able to grant the privilege to others.

    See 'GRANT' in the SQL Language doc
    http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_9013.htm#i2063861
    >
    WITH GRANT OPTION

    Specify WITH GRANT OPTION to enable the grantee to grant the object privileges to other users and roles.

    Restriction on Granting WITH GRANT OPTION You can specify WITH GRANT OPTION only when granting to a user or to PUBLIC, not when granting to a role.
  • 10. Re: Can't execute a Grant with Dynamic SQL?
    jack m Newbie
    Currently Being Moderated
    Ok. Thanks for your patience.

    Lets see if I can explain this:

    User_Dba is the dba, which is trying to assign privileges to User_Target on a table owned by User_Owner (using a stored proc)

    So User_Owner executes the following statement:

    grant select, insert, delete, update  on  mytable to user_Dba with grant option;

    User_Dba then tries to assign a privilege using a SP

    grant insert on User_Owner.mytable to user_target

    error I get is

    Error at line 1
    ORA-00942: table or view does not exist
    ORA-06512: at "Custom_PRIVILEGE.UTIL", line 91
    ORA-06512: at line 4
  • 11. Re: Can't execute a Grant with Dynamic SQL?
    sb92075 Guru
    Currently Being Moderated
    user12055696 wrote:
    Ok. Thanks for your patience.

    Lets see if I can explain this:

    User_Dba is the dba, which is trying to assign privileges to User_Target on a table owned by User_Owner (using a stored proc)

    So User_Owner executes the following statement:

    grant select, insert, delete, update  on  mytable to user_Dba with grant option;
    USER_DBA must start new session after the GRANT has been issued.

    >
    User_Dba then tries to assign a privilege using a SP

    grant insert on User_Owner.mytable to user_target

    error I get is

    Error at line 1
    ORA-00942: table or view does not exist
    ORA-06512: at "Custom_PRIVILEGE.UTIL", line 91
    ORA-06512: at line 4
  • 12. Re: Can't execute a Grant with Dynamic SQL?
    rp0428 Guru
    Currently Being Moderated
    >
    Error at line 1
    ORA-00942: table or view does not exist
    ORA-06512: at "Custom_PRIVILEGE.UTIL", line 91
    ORA-06512: at line 4
    >
    And what is the code at and around line 91? 91 lines is a lot for a procedure that executes only one command.
  • 13. Re: Can't execute a Grant with Dynamic SQL?
    jack m Newbie
    Currently Being Moderated
    Creating a new session didn't work...

    Worst: I ran the stored proc from User_Owner and it still doesn't work!!! I took the qualification off and nothing...

    When I copy the DBMS_Output into the console and execute the statement it works, no problem... so it is not a syntax error or anything.

    I think grants simply can't work from inside named blocks...
  • 14. Re: Can't execute a Grant with Dynamic SQL?
    rp0428 Guru
    Currently Being Moderated
    >
    I think grants simply can't work from inside named blocks...
    >
    Oracle doesn't agree with you.
    create or replace procedure test_grant as
    vTable_Name VARCHAR2(30);
    vStatement_Command LONG;
    BEGIN
    vTable_Name := 'emp';
    vStatement_Command := 'GRANT INSERT ON ' || vTable_Name || ' TO "HR"';
    DBMS_OUTPUT.PUT_LINE (vStatement_Command);
    EXECUTE IMMEDIATE vStatement_Command;
    END;
    /
    
    SQL> set serveroutput on
    SQL> exec test_grant;
    GRANT INSERT ON emp TO "HR"
    
    PL/SQL procedure successfully completed.
    
    SQL>
    Why are you still using LONG datatypes? Get rid of that.
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points