Looks like you need a GROUP BY. Your query above done with a GROUP BY
would look like this:
SELECT to_char(auditdate,'YYYY'), count(*)
FROM AuditLog GROUP BY to_char(auditdate,'YYYY');
A similar query can be used for other groupings. For example, here would be a count for each day:
SELECT to_char(auditdate,'MM/DD/YYYY'), count(*)
FROM AuditLog GROUP BY to_char(auditdate,'MM/DD/YYYY');
To do this for each PageTitle, simply add PageTitle like this:
SELECT PageTitle , to_char(auditdate,'MM/DD/YYYY'), count(*)
FROM AuditLog GROUP BY PageTitle, to_char(auditdate,'MM/DD/YYYY');
To pivot the data as you have shown gets a bit more difficult particularly on 10g where there is no PIVOT
. Here is one way to do it on 10g:
SELECT PageTitle
, Sum(DECODE(to_char(AuditDate,'MM/DD/YYYY'),'10/22/2012',1,NULL)) Oct_22
, Sum(DECODE(to_char(AuditDate,'MM/DD/YYYY'),'10/23/2012',1,NULL)) Oct_23
FROM AuditLog GROUP BY PageTitle;
Of course this requires entering all the dates. You can make this a bit easier by generating the query using a query such as this:
SELECT 'SELECT PageTitle ' SQL FROM dual
UNION ALL
SELECT DISTINCT ' , Sum(DECODE(to_char(AuditDate,''MM/DD/YYYY''),'''
|| to_char(AuditDate,'MM/DD/YYYY') || ''',1,NULL)) ' || to_char(AuditDate,'Mon_DD')
FROM AuditLog
UNION ALL
SELECT 'FROM AuditLog GROUP BY PageTitle;' FROM dual;
Here is the sample data I used:
drop table auditlog;
create table AuditLog as (select sysdate auditdate, 'Page 1' PageTitle from dual);
insert into auditlog (select sysdate, 'Page 1' from dual);
insert into auditlog (select sysdate, 'Page 2' from dual);
insert into auditlog (select sysdate, 'Page 3' from dual);
insert into auditlog (select sysdate, 'Page 3' from dual);
insert into auditlog (select sysdate, 'Page 3' from dual);
insert into auditlog (select sysdate+1, 'Page 1' from dual);
insert into auditlog (select sysdate+1, 'Page 1' from dual);
insert into auditlog (select sysdate+1, 'Page 2' from dual);
insert into auditlog (select sysdate+1, 'Page 2' from dual);
insert into auditlog (select sysdate+1, 'Page 3' from dual);
commit;
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.
Best Answer
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:
Determine object privileges:
In this example the user
SPONGEBOB
is assigned two rolesINDEX_MGR
andHR_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:As you might guess,
APP_USER
is a role granted to another roleAPP_UNLIM_USER
, which in turn is granted to the roleINDEX_MGR
. The rolesINDEX_MGR
andHR_QUERY
are granted directly to the userSPONGEBOB
. 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 onlyCREATE SESSION
privilege.Let's query
ROLE_ROLE_PRIVS
view described earlier on behalf of theSPONGEBOB
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:
Then we'll determine which roles are assigned to the user
SPONGEBOB
directly. As you can see on the diagram above, these areHR_QUERY
andINDEX_MGR
:Now, we'll combine the two previous queries together to have all roles' names in one result:
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:
Object privileges:
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:
The following query returns the object auditing statements:
In the same vein, you can construct
NOAUDIT
statements to stop auditing the privileges.