Grant EXECUTE to multiple packages using wildcard

Posted on 2006-04-28
Last Modified: 2008-02-01
I need to use GRANT EXECUTE to give a user execute privilege to multiple packages with package names starting with ABC.  Can I issue the GRANT EXECUTE with a wildcard such as GRANT EXECUTE ON 'ABC%' TO USER1;   ??
Question by:rawilki_9
    LVL 19

    Accepted Solution

    There is no such syntax but you can do it using a simple piece of PL/SQL code as the following:

      for rec in (select object_name from user_objects where object_name like '%ABC%' and object_type = 'PACKAGE') loop
          execute immediate 'grant execute on ' || rec.object_name || '  to user1';
      end loop;

    enjoy :)

    Author Comment

    Thanks, Acton!
    LVL 19

    Expert Comment

    np :) Let me know if any more questions.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
    Introduction A previously published article on Experts Exchange ("Joins in Oracle", makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
    This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
    This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

    731 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now