Grant EXECUTE to multiple packages using wildcard

Posted on 2006-04-28
Medium Priority
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
  • 2
LVL 19

Accepted Solution

actonwang earned 500 total points
ID: 16566636
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

ID: 16578222
Thanks, Acton!
LVL 19

Expert Comment

ID: 16578354
np :) Let me know if any more questions.

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
Via a live example, show how to take different types of Oracle backups using RMAN.
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.
Suggested Courses

840 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