Privilege problem with procedure

[ksh...@health.gov.mb.ca]

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?

-Keith

-----------== Posted via Deja News, The Discussion Network ==----------
http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own

[Thomas Kyte]

A copy of this was sent to ksh...@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
tk...@us.oracle.com
Oracle Service Industries
Reston, VA USA

--
http://govt.us.oracle.com/ -- downloadable utilities

----------------------------------------------------------------------------
Opinions are mine and do not necessarily reflect those of Oracle Corporation

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.

[Rodgers, Tony (CEI-Atlanta)]

Did you grant select access through a role, or directly to the user? In
this case, I believe you'll need to grant directly to the user and not
via a role. And when you say you have synonyms set up, I presume you
mean such that you don't need to prefix the view names with sys. ?

> -----Original Message-----
> From: ksh...@health.gov.mb.ca [SMTP:ksh...@health.gov.mb.ca]
> Posted At: Wednesday, February 17, 1999 10:06 AM
> Posted To: comp.databases.oracle.server
> Conversation: Privilege problem with procedure
> Subject: Privilege problem with procedure
>
> 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?
>

[Rodgers, Tony (CEI-Atlanta)]