Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

DBMS_STATS-User authentication issue

BalajiChellappaSep 18 2006 — edited Sep 18 2006
Hi,
I am trying to achieve allowing regular Users to perform limited table operations on table's present in another schema.
For that I have created a procedure to analyze any table in the SYSTEM schema.
I have table EMP in the SCOTT schema.
I have different database users say User1, User2 etc.
User1 , User2 are the regular users who has just read access on SCOTT.EMP table.
I want to give them the ability to analyze the SCOTT.EMP table using the Procedure compiled in the System schema.

So I created the following procedure in the SYSTEM Schema and granted the execution privilege to Public.

----------------------------------------------------------------------------------------------
SYSTEM>

CREATE OR REPLACE PROCEDURE analyze_any_table(i_Owner varchar2, i_Table varchar2)
AS
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(
ownname => i_Owner
,tabname => i_Table);
END;
/

GRANT EXECUTE ON analyze_emp TO PUBLIC;
----------------------------------------------------------------------------------------------

Now I logged in as the USER1

USER1> EXEC SYSTEM.analyze_emp;

ERROR at line 1:
ORA-20000: Unable to analyze TABLE "SCOTT"."EMP", insufficient
privileges or does not exist
ORA-06512: at "SYS.DBMS_STATS", line 13046
ORA-06512: at "SYS.DBMS_STATS", line 13076
ORA-06512: at "SYSTEM.ANALYZE_ANY_TABLE", line 3
ORA-06512: at line 1

How to get out of this error.
I don't want to give "GRANT ALL ON SCOTT.EMP TO PUBLIC" to any other database users.

Note: if I compile the above procedure in the SCOTT schema it works. But I don't want to duplicate this analyze_any_schema procedure in multiple schema's which has some table.

Any suggestion would be really appreciated.
Thanks
Balaji
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Oct 16 2006
Added on Sep 18 2006
2 comments
382 views