Re: Privileges to Execute Your procedures In another Schema

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Thu, 24 Sep 1998 13:36:40 GMT
Message-ID: <360e4a9c.67975433@192.86.155.100>

A copy of this was sent to "John Akright" <akrighjb_at_ci.richmond.va.us> (if that email address didn't require changing) On 24 Sep 1998 11:55:58 GMT, you wrote:

>I thought I understood privileges until I tried to create a procedure in
>SYSTEM that would use
>DBMS_SPACE to go out and get unused space and High Water Marks for tables
>in all the schemas of my database.
>
>I gave SYSTEM select on all the tables of all the schemas by generating a
>script to grant the
>SELECT privileges.
>

thats the problem, system doesn't need select, its need to be able to ANALYZE the underlying objects.

From the dbms_space spec:

create or replace package dbms_space is

OVERVIEW --
This package provides segment space information not currently
available through the standard views.
SECURITY --
The execution privilege is granted to PUBLIC. Procedures in this
package run under the caller security. The user must have ANALYZE
privilege on the object.

so, all you need to do is:

SQL> grant analyze any to system;

and then you can revoke all of those SELECT grants and the procedure will execute just fine....

>I.E. SELECT 'grant select on '||TABLE_NAME||' to system;' FROM DBA_TABLES
>WHERE OWNER
> = '<OWNER>';
>
>Anyway, I did this for all the schemas. The procedure is compiled as
>SYSTEM.
>I know I can compile it in all the schemas and execute as SYSTEM, but I
>want one copy owned by SYSTEM.
>
>The program is easy it just creates a cursor that selects all segments from
>DBA_SEGMENTS for an owner and passes arguments to
>
> DBMS_SPACE.UNUSED_SPACE(segment_owner,segment_name,segment_type, ETC
>....)
>
>The error is
>ORA-01031: insufficient privileges
>ORA-06512: at "SYS.DBMS_SPACE", line 34
>ORA-06512: at "SYSTEM.TABLE_SEGMENT_SPACE", line 28 -> THIS is where it
>executes DBMS_SPACE
>
>
>I even gave SYS explicit SELECT on schema objects like I did for SYSTEM.
>
>I know that the Owner of the procedure has to have privileges on the
>objects its executing
>against (and both SYSTEM and SYS have SELECT ).
>
>I've compiled the procedure in every schema and it works but I don't want 5
>copies of the procedure.
>
>What am I missing?
>
>Thanks
>John

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA

--
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. Received on Thu Sep 24 1998 - 08:36:40 CDT