| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Privilege problem with procedure
A copy of this was sent to kshave_at_health.gov.mb.ca
(if that email address didn't require changing)
On Wed, 17 Feb 1999 15:05:35 GMT, you wrote:
>I've created a procedure that queries dba_tab_privs and dba_objects. I
>initially created it as SYS and it runs fine. However, when I try to create it
>as anybody else (with SELECT access on the two views as well as synonyms set
>up) I get errors.
>
>CREATE OR REPLACE PROCEDURE secrep AS
>
>CURSOR user_curs IS
> SELECT DISTINCT grantee FROM dba_tab_privs;
>
>BEGIN
> null;
>END;
>/
>
>3/8 PLS-00341: declaration of cursor 'USER_CURS' is incomplete or
> malformed
>
>
>What rights does one need to access the sys.dba_tab_privs and sys.dba_object
>views within a procedure?
>
roles are never enabled during the execution of a procedure.
Try this:
SQL> set role none;
SQL> "statement you want to test to see if it'll work in a procedure"
If you can do it in plus with no roles you can do it in a procedure. If you can't, you must have the privelege from a role and hence won't be able to do it in a procedure.
You probably have the privelege to do what you are trying to do in the procedure via a role. Grant the privelege directly to the owner of the procedure and it'll work.
grant SELECT on dba_tab_privs to <OWNER>;
>-Keith
>
>-----------== Posted via Deja News, The Discussion Network ==----------
>http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA
--
http://govt.us.oracle.com/ -- downloadable utilities
Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Wed Feb 17 1999 - 15:56:01 CST
 |
 |