Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Privileges to Execute Your procedures In another Schema

Privileges to Execute Your procedures In another Schema

From: John Akright <akrighjb_at_ci.richmond.va.us>
Date: 24 Sep 1998 11:55:58 GMT
Message-ID: <01bde7b2$893097a0$a82f090a@hubbard.hubbard>


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.

I.E. SELECT 'grant select on '||TABLE_NAME||' to system;' FROM DBA_TABLES WHERE 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 Received on Thu Sep 24 1998 - 06:55:58 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US