1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

grant execute to newly created user upon user creation

Discussion in 'SQL PL/SQL' started by member.forums@gmail.com, Jun 9, 2009.

  1. member.forums@gmail.com

    member.forums@gmail.com Active Member

    Messages:
    16
    Likes Received:
    0
    Trophy Points:
    80
    Hi

    I have a procedure test in schema of user1.
    I have to grant exec on this procedure to the new user upon creation of the new user.
    like upon
    create user USER1 identified by ....

    then USER1 should be able to execute user1.test after that

    in short every user created should have grant to execute this procedure.

    Please suggest.

    Thanks in Advance
     
  2. tyro

    tyro Forum Genius

    Messages:
    368
    Likes Received:
    20
    Trophy Points:
    260
    Location:
    India
    hi login to oracle as user system and you can simply do this

    Code (SQL):
    GRANT EXECUTE ON user1.TEST TO <new_user>;
    This will grant execute permission on the procedure test to the <new_user>. If however you want that subsequently all users should be able to execute this procedure, you can loop over all existing users and grant them

    Code (SQL):
    BEGIN
      FOR c1_rec IN (SELECT username FROM ALL_USERS WHERE username NOT IN ('SYS','SYSTEM'))
        LOOP
          EXECUTE IMMEDIATE 'GRANT EXECUTE ON user1.test TO ' || c1_rec.username;
        END LOOP;
    END;
    /
    Hope this helps...
     
  3. member.forums@gmail.com

    member.forums@gmail.com Active Member

    Messages:
    16
    Likes Received:
    0
    Trophy Points:
    80
    Nice pic there Tyro

    I did
    Grant execute on <myprocedure> to public
    and it works fine for existing as well as newly created user
    Thanks