I found that auditing can be done on oracle schema objects like procedures and views. Is there any possibility to do the auditing on Types?
Oracle Audit on Types
auditoracle
Related Solutions
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.
I have found a way to prevent duplicate rows in FGA audit trail with parallel query
First create a function that return 1 if AUTHENTICATION_METHOD is PQ_SLAVE in USERENV context (coordinator process gets 0)
create or replace function is_pg_slave return integer as
begin
if (sys_context('USERENV', 'AUTHENTICATION_METHOD') = 'PQ_SLAVE') then
return 1;
else
return 0;
end if;
end;
/
Then add policy to table
begin
dbms_fga.add_policy(
object_name => 'table_name',
policy_name => 'noaudit_pq_slave',
audit_condition => 'is_pg_slave = 0',
statement_types => 'SELECT');
end;
/
Now only the query coordinator is audited, so no matter what the parallel degree is, only one row is inserted into fga audit trail.
I have not noticed any performance issues with this approach compared to normal auditing or no auditing.
Related Question
- Oracle – Logging in as Different User and Viewing All Schema Tables
- Oracle Logs – Difference Between Alert Log and Archive Log in Oracle Databases
- Enabling audit in Oracle
- Audit kernel module usage in Oracle
- Oracle – What Happens if sys.aud$ is Full?
- Oracle – How to Get More Information from Audit Trail
- How to determine if Oracle Java classes are being used
- How to Audit All DML Statements on a Schema in Oracle 12.2
Best Answer
Types can be altered, granted/revoked privileges on, so why not?
Example:
See DBA_OBJ_AUDIT_OPTS and AUDIT for details.