Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Privileges to Execute Your procedures In another Schema
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 itexecutes 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