Which rights are Granted PUBLIC

During a schema copy action from an old database to a new one, we ran into problems that certain packages where not compiled successfully.
Some dependencies failed, because the schema user in the new database failed to have execution priviliged on certain SYS packages, like DBMS_LOCK.

The settings of both schema's where identical, so the rights must have been set in the past by using the PUBLIC account.
For those who don't know, the PUBLIC role is a role used for setting database global rights, which every user is allowed to have.


What I needed was a list of GRANT statements for the PUBLIC user that existed in the "old" database but not (yet) in the "new" database.

I started browsing the usual tables like DBA_ROLE_ROLES, DBA_ROLES, DBS_ROLE_PRIVS, but nowhere I could find the right for the PUBLIC role.

After spending almost an hour on Google, I came accross a set of views called KU$_ROGRANT_VIEW, KU_SYSGRANT_VIEW and KU$_OBJGRANT_VIEW

Especially the last one contained the information I was looking for. This table contains columns like 'GRANTEE', 'OBJ_NUM' and the privilege name 'PRIVNAME'.
Joining this table with DBA_OBJECTS, using the below query, resulted in -at least- the list with all EXECUTION grants for the PUBLIC role.
I was looking for these EXECUTION grants, because the uncompilable packages complained about dependencies, not about missing tables.

The statement:


SQL> SELECT    'grant '
       || a.privname
       || ' on '
       || b.owner
       || '.'
       || b.object_name
       || ' to '
       || a.grantee
       || ';'
  FROM KU$_OBJGRANT_VIEW a, dba_objects b
 WHERE     grantee = 'PUBLIC'
       AND a.obj_num = b.object_id
       AND a.privname='EXECUTE'
       AND b.object_name not like '%/%'
       order by b.object_name;

Using this statement on both the "old" and "new" database I was able to create a list of missing GRANT statements (there were 10 of them) that had to be executed on the "new" database.

Afterwards all packages were compiled successfully.