[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


what grant am i missing?

Posted on 2014-07-16
Medium Priority
Last Modified: 2014-07-16
i have the below procedure that basically execute the audit statement.
the user has the ability to execute this statement outside the procedure.
what grant am i missing here?

create or replace procedure audit_test(in_audit_option varchar2)is
  execute immediate 'audit GRANT SEQUENCE by USER1';

thanks in advance
Question by:eagle_ea
  • 4
  • 3
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40199216
Grants need to be explicitly granted to a user when being called from a stored procedure.  It cannot be granted through a ROLE.

Any reason you are running audit statements from inside a procedure?

Author Comment

ID: 40199231
we are looking to automate the audit policy based on the role given.

for this particular scenario, what grant needs to be provided to the user?
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40199251
It depends on what auditing permissions is needed.

If ALL try:
grant audit any to some_user;

where some_user is the owner of the procedure.
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Author Comment

ID: 40199261
that is already in place. still does not work
LVL 78

Accepted Solution

slightwv (䄆 Netminder) earned 2000 total points
ID: 40199277
grant audit system to some_user;

I just tried it on my 12c database and it worked.

Author Comment

ID: 40199287
excellent ...that seems to work...many thanks for your help

Author Closing Comment

ID: 40199288
slightwv is fantastic and quick in helping
LVL 74

Expert Comment

ID: 40199884
note - in 12c you can grant roles to specific procedures within a package rather than to the package owner

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

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

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Suggested Courses

834 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