exec gather table stats from procedure

Question

If I create a procedure:

CREATE OR REPLACE PROCEDURE SchameB.PRC_GATHER_STATS IS
BEGIN
    SYS.DBMS_STATS.GATHER_TABLE_STATS( 'SchName', 'TableName', CASCADE => TRUE);
END;

and execute it ;

EXEC SchameB.PRC_GATHER_STATS;

this gives me error ORA-20000: Unable to analyze TABLE "SchameA"."TableName", insufficient privileges or does not exist. But this works:

EXEC SYS.DBMS_STATS.GATHER_TABLE_STATS( 'SchameA', 'TableName', CASCADE => TRUE);

The user who EXECs the procedure and the table are in different schemas.

Why am I getting an error when doing this through a procedure?

Answer 1

To gather statistics on an object in another schema you need the ANALYZE ANY system privilege. I appears that the user that runs your procedure has that privilege, but granted through a role. As the documentation says:

All roles are disabled in any named PL/SQL block (stored procedure, function, or trigger) that executes with definer's rights.

You can either GRANT ANALYZE ANY directly to your user, or create the procedure with invoker's rights, as:

CREATE OR REPLACE PROCEDURE SchameB.PRC_GATHER_STATS
AUTHID CURRENT_USER IS
BEGIN
    SYS.DBMS_STATS.GATHER_TABLE_STATS('SchName', 'TableName', CASCADE => TRUE);
END;
/

When you EXEC the DBMS_STATS procedure directly, it's running as an anonymous block, and those always run with invoker's rights - honouring roles.

Answer 2

If you want the procedure to be able to be ran by a user without the ANALYSE ANY role then you can set the AUTHID to be DEFINER

CREATE OR REPLACE PROCEDURE SchameB.PRC_GATHER_STATS
AUTHID DEFINER IS
BEGIN
    SYS.DBMS_STATS.GATHER_TABLE_STATS('SchName', 'TableName', CASCADE => TRUE);
END;