Audit kernel module usage in Oracle

TL;DR: You cannot audit the roles usage.

You can however use the following views to determine which system privileges, object privileges, and other roles are granted to the roles which are in turn granted to the current user:

• ROLE_ROLE_PRIVS describes the roles granted to other roles. Information is provided only about roles to which the user has access.
• ROLE_SYS_PRIVS describes system privileges granted to roles. Information is provided only about roles to which the user has access.
• ROLE_TAB_PRIVS describes table privileges granted to roles. Information is provided only about roles to which the user has access.

The only complicated thing here is privileges and roles inherited from other roles. If it's not the case for you, and the roles granted to your user have one level of inheritance, i. e. these roles are not children of any other roles, then you can just use the following queries to determine the privileges inherited by a particular user from the roles.

Determine system privileges:

select role, privilege
  from role_sys_privs
  where role in (select granted_role from dba_role_privs 
                   where grantee = 'SPONGEBOB');

      ROLE            PRIVILEGE
----------    -----------------
 INDEX_MGR     CREATE ANY INDEX
 INDEX_MGR       DROP ANY INDEX

Determine object privileges:

select role, owner, table_name, column_name, privilege
  from role_tab_privs
  where role in (select granted_role from dba_role_privs 
                   where grantee = 'SPONGEBOB');

      ROLE     OWNER      TABLE_NAME     PRIVILEGE
----------    ------    ------------    ----------
  HR_QUERY        HR     DEPARTMENTS        SELECT
  HR_QUERY        HR       EMPLOYEES        SELECT

In this example the user SPONGEBOB is assigned two roles INDEX_MGR and HR_QUERY from which he inherits system and object privileges. But what if these roles inherit privileges from other roles? For such case let's implement some more complex model:

APP_USER                  CREATE SESSION
 \
  APP_UNLIM_USER          UNLIMITED TABLESPACE
   \
    INDEX_MGR             CREATE ANY INDEX, DELETE ANY INDEX
            |
        SPONGEBOB
            |
     HR_QUERY             SELECT ON HR.EMPLOYEES, SELECT ON HR.DEPARTMENTS

As you might guess, APP_USER is a role granted to another role APP_UNLIM_USER, which in turn is granted to the role INDEX_MGR. The roles INDEX_MGR and HR_QUERY are granted directly to the user SPONGEBOB. In the diagram, all the privileges granted to the roles are put in front of the roles' names to which they are granted in the corresponding lines, e. g. APP_USER role is granted only CREATE SESSION privilege.

Let's query ROLE_ROLE_PRIVS view described earlier on behalf of the SPONGEBOB user:

select role, granted_role from role_role_privs;

           ROLE       GRANTED_ROLE
---------------    ---------------
      INDEX_MGR     APP_UNLIM_USER
 APP_UNLIM_USER           APP_USER

From the result, you can see that the role inheritance matches the one shown in the diagram. Now, we can use the role names returned by the query to determine what privileges these roles are granted.

First, let's present the roles' names in one column:

select role           from role_role_privs
union
select granted_role   from role_role_privs;

           ROLE
---------------
 APP_UNLIM_USER
       APP_USER
      INDEX_MGR

Then we'll determine which roles are assigned to the user SPONGEBOB directly. As you can see on the diagram above, these are HR_QUERY and INDEX_MGR:

select username, granted_role from user_role_privs;

  USERNAME     GRANTED_ROLE
----------    -------------
 SPONGEBOB         HR_QUERY
 SPONGEBOB        INDEX_MGR

Now, we'll combine the two previous queries together to have all roles' names in one result:

select role         from role_role_privs
union
select granted_role from role_role_privs
union
select granted_role from user_role_privs;

             ROLE
  ---------------
   APP_UNLIM_USER
         APP_USER
         HR_QUERY
        INDEX_MGR

Having obtained all the roles' names, we can now determine which privileges are granted to that roles. (In the examples below I replaced the previous query code with MYROLES for brevity.)

System privileges:

select role, privilege from role_sys_privs where role in (MYROLES);

           ROLE                PRIVILEGE
---------------    ---------------------
      INDEX_MGR           DROP ANY INDEX
       APP_USER           CREATE SESSION
      INDEX_MGR         CREATE ANY INDEX
 APP_UNLIM_USER     UNLIMITED TABLESPACE

Object privileges:

select role, owner, table_name, privilege 
  from role_tab_privs where role in (MYROLES);

     ROLE     OWNER      TABLE_NAME     PRIVILEGE
---------    ------    ------------    ----------
 HR_QUERY        HR     DEPARTMENTS        SELECT
 HR_QUERY        HR       EMPLOYEES        SELECT

Since you now have all the privileges shown in a user-friendly format, you can automatically construct the auditing statements to determine which privileges are actually used. For example, the following query will return you the audit statements which you can use to check what system privileges are used:

select privilege, 'audit ' || privilege || ' by spongebob whenever successful;' stmt
  from role_sys_privs where role in (MYROLES);

                                                                STMT
--------------------------------------------------------------------
              audit DROP ANY INDEX by spongebob whenever successful;
              audit CREATE SESSION by spongebob whenever successful;
            audit CREATE ANY INDEX by spongebob whenever successful;
        audit UNLIMITED TABLESPACE by spongebob whenever successful;

The following query returns the object auditing statements:

select 'audit ' || privilege || ' on ' || owner || '.' || table_name 
       || ' whenever successful;' stmt 
  from role_tab_privs where role in (MYROLES);

                                                STMT
----------------------------------------------------
 audit SELECT on HR.DEPARTMENTS whenever successful;
   audit SELECT on HR.EMPLOYEES whenever successful;

In the same vein, you can construct NOAUDIT statements to stop auditing the privileges.

The audit records are written to the operating system's audit trail defined by the audit_file_dest parameter. You can change the format of to XML by changing the audit_trail parameter but the files will be still kept on the file system. This due to security reasons since DBAs shouldn't be able to access those files if they are being audited. You can play around with external tables or create a shell script for analyzing the files but there is no built in mechanism for analyzing the data.