No privileges to gather table stats

Question

I have an oracle package with a procedure that create table, than it grant priviliges on it.

The code is just similar to this:

Begin
Execute immediate 'create table SU.temp_tbl...';
...
...
Dbms_stats.gather_table_stats('SU', 'TEMP_TBL');
End;

The owner of the procedure is user with DBA role.

This role has

create any table privilege

Such as

Analyze any table privilege

So why when I'm running this procedure, I'm getting an exception of 'no privileges' on the gather table command, But the 'create' just executed ok?

And what is the solution ? Do I need the SU user to grant explicit privilege of analyze to the DBA?

I'm using oracle 11g version.

Thanks.

Answer 1

The privileges have to be granted directly to the owner of the procedure/package, not indirectly by a role.

Of course the EXECUTE ON owner.name_of_procedure is necessary, too.

From the usage notes of the GATHER_TABLE_STATS Procedure

To invoke this procedure you must be owner of the table, or you need the ANALYZE ANY privilege.