Users and Basic Security - Pro Oracle Database 12c Administration - page 150

To see roles granted to your currently connected user, query from the USER_ROLE_PRIVS view:

SQL> select * from user_role_privs;

To revoke a privilege from a role, use the REVOKE command:

SQL> revoke create database link from jr_dba;

Similarly, use the REVOKE command to remove a role from a user:

SQL> revoke jr_dba from lellison;

■ Note Unlike other database objects, roles don't have owners. a role is defined by the privileges assigned to it.

pL/SQL aND rOLeS

if you work with pL/sQL, sometimes you get this error when attempting to compile a procedure or a function:

PL/SQL: ORA-00942: table or view does not exist

What's confusing is that you can describe the table:

SQL> desc app_table;

Why doesn't pL/sQL seem to be able to recognize the table? it's because pL/sQL requires that the owner of the
package, procedure, or function be explicitly granted privileges to any objects referenced in the code. the owner
of the pL/sQL code can't have obtained the grants through a role.

When confronted with this issue, try this as the owner of the pL/sQL code:

SQL> set role none;

now, try to run a sQL statement that accesses the table in question:

SQL> select count(*) from app_table;

if you can no longer access the table, then you've been granted access through a role. to resolve the issue,
explicitly grant access to any tables to the owner of the pL/sQL code (as the owner of the table):

SQL> connect owner/pass
SQL> grant select on app_table to proc_owner;

you should be able to connect as the owner of the pL/sQL code and successfully compile your code.