DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: how to grant role in function o procedure ?

  1. #1
    Join Date
    Jul 2005
    Posts
    7

    how to grant role in function o procedure ?

    hi everybody,
    in PL/SQL we can run command' grant role to user'
    but in funtion or procedure can not do that.
    error:ORA-01919
    i want grant role to user in procedure
    can you help me?
    thanks alot.

  2. #2
    Join Date
    Jan 2002
    Location
    Up s**t creek
    Posts
    1,525
    Code:
    EXECUTE IMMEDIATE 'grant role to user';
    HTH
    Jim
    Oracle Certified Professional
    "Build your reputation by helping other people build theirs."

    "Sarcasm may be the lowest form of wit but its still funny"

    Click HERE to vist my website!

  3. #3
    Join Date
    Jul 2005
    Posts
    7

    it's not running in procedure

    in procedure, it not running

  4. #4
    Join Date
    Jul 2005
    Posts
    7
    in PL/SQL ,EXECUTE IMMEDIATE 'grant role to user' running
    but in procedure below, it not run
    create or replace procedure test as

    begin


    EXECUTE IMMEDIATE 'grant csr to lanntthso';


    exception when others then
    raise;
    end;


    error ra-01919 role 'csr'doe not exist
    but in BD there is role'csr'

  5. #5
    Join Date
    Jan 2002
    Location
    Up s**t creek
    Posts
    1,525
    Does the user who owns the procedure have the necessary privs? it works fine for me.
    Code:
    SQL> create role tester;
    
    Role created.
    
    SQL> create or replace procedure test
      2  as
      3  begin
      4    execute immediate 'grant tester to scott';
      5  exception when others then
      6    raise;
      7  end;
      8  /
    
    Procedure created.
    
    SQL> exec test;
    
    PL/SQL procedure successfully completed.
    
    SQL> select granted_role
      2  from dba_role_privs
      3  where grantee = 'SCOTT';
    
    GRANTED_ROLE
    ------------------------------
    TESTER
    CONNECT
    RESOURCE
    
    3 rows selected.
    Jim
    Oracle Certified Professional
    "Build your reputation by helping other people build theirs."

    "Sarcasm may be the lowest form of wit but its still funny"

    Click HERE to vist my website!

  6. #6
    Join Date
    Jul 2005
    Posts
    7
    in my base, there many roles already so i write a proc to grant role to user
    your example works, i've test it
    but many roles in my base, i grant its, proc not work and error ra-01919
    role 'csr' does not exist
    can you explain
    in PL/SQL, i can execute :grant csr to lanntthso
    but in proc, it does't work
    help me!

  7. #7
    Join Date
    Jan 2002
    Location
    Up s**t creek
    Posts
    1,525
    Does the procedure owner have the necessary privileges to grant the role?

    i.e GRANT ANY ROLE, or ADMIN OPTION on the role?
    Jim
    Oracle Certified Professional
    "Build your reputation by helping other people build theirs."

    "Sarcasm may be the lowest form of wit but its still funny"

    Click HERE to vist my website!

  8. #8
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Ah, does the user owning the procedure have the privilege to grant the role directly, or through a role? Confusing, eh? I they have the privilege to grant any role through a role then they can't make use of it in a procedure ... it has to be a privilege granted directly to the user.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  9. #9
    Join Date
    Jan 2004
    Posts
    162
    Unless of course the stored procedure is defined with invoker rights (AUTHID CURRENT_USER) and executed from a call stack executing entirely under invoker rights in which case roles WOULD be enabled and the GRANT ANY ROLE privilege (which was granted by role) would be sufficient to grant a role.

  10. #10
    Join Date
    Jul 2005
    Posts
    7
    user have not privileges to grant the role
    so grant role to user in procedure not work.
    thanks everybody alot

Posting Permissions

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


Click Here to Expand Forum to Full Width