Did You Know #16 – Package Permissions

When user A creates a procedure and grants execute permissions to user B, user B can execute the procedure (obviously), but he can also see the code (in ALL_SOURCE view).

With packages this scenario is a little bit different. When user A creates a package and grants execute permission to user B, user B will be able to execute the procedures and function in the package( again, obviously), but he won’t be able to see the code. User B will only be able to see the package header in ALL_SOURCE and won’t have access to the package body.
If we wish user B to see the code itself, it has to have debug permissions on the package (grant debug on pkg to b), debug permissions on any package (grant debug any procedure to b) or permissions on DBA_SOURCE (grant select on dba_source to b).