No question at this time
DBA Top 10
1 M. Cadot 20600
2 A. Kavsek 14800
3 M. Hidayathullah ... 10500
4 B. Vroman 8100
5 P. Wisse 5300
6 T. Boles 4200
7 J. Schnackenberg 3800
8 K. Van Nieuwenhov... 2100
9 G. Van Cauter 1400
10 K. Pagadala 1100
About
DBA-Village
Download PLATO
The free tool for auditing and tuning your database
Version 55 now available
Sep 02, 2016
The DBA-Village forum
Forum as RSS
as RSS feed
Site Statistics
Ever registered users48057
Total active users1738
Act. users last 24h12
Act. users last hour0
Registered user hits last week391
Registered user hits last month1277
Go up

Creating 12c AUDIT PROFILES from standard audit setups
Next thread: change parameter filesystemio_options from ASYNCH to SETALL and query goes from 8min to 18hours
Prev thread: Oracle 12.1.0.2 inmemory option

Message Score Author Date
So what I am trying to do is build audit profiles ...... Tim Boles Feb 16, 2017, 04:30
Hi Tim, I don't see how to do this but I have t...... Bruno Vroman Feb 16, 2017, 18:30
Awesome hint....I appreciate it. Working on new s...... Tim Boles Feb 16, 2017, 20:14
Hi Tim, unfortunately finding the "supersets" o...... Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts Bruno Vroman Feb 17, 2017, 12:31
Well thanks Bruno for taking a look. And really i...... Tim Boles Feb 17, 2017, 14:58
Indeed sometimes brute force is the way to go... ...... Bruno Vroman Feb 17, 2017, 15:58

Follow up by mail Click here


Subject: Creating 12c AUDIT PROFILES from standard audit setups
Author: Tim Boles, United States
Date: Feb 16, 2017, 04:30, 245 days ago
Os info: any
Oracle info: 12c
Message: So what I am trying to do is build audit profiles in a 12c database based on standard auditing setups.

Bruno helped me a lot earlier by providing a way to show users that had the same auditing statements through grouping items together.

http://www.dba-village.com/village/dvp_forum.OpenThread?ThreadIdA=85707#233932

This was awesome and helped me greatly to create the following (because from 11g on there is no difference between BY SESSION and BY ACCESS auditing) I simplified what Bruno provided to


CLEAR BREAKS
BREAK ON nn SKIP 1 ON audit_option ON success ON failure
COL nn NOPRINT


WITH all_options_seen AS
( SELECT distinct(d.audit_option) as audit_option
FROM dba_stmt_audit_opts d
ORDER BY 1
)
, numbered AS
( SELECT POWER( 2, rownum - 1 ) n, audit_option
FROM all_options_seen
)
, people AS
( SELECT a.user_name, SUM( n.n ) nn
FROM dba_stmt_audit_opts a
, numbered n
WHERE a.audit_option = n.audit_option
GROUP BY a.user_name
)
, all_sets_of_privs AS
( SELECT nn FROM people GROUP BY nn )
, detail_of_a_set_of_privs AS
( SELECT a.nn
, d.audit_option
FROM dba_stmt_audit_opts d
, all_sets_of_privs a
WHERE d.user_name = ( SELECT p.user_name
FROM people p
WHERE p.nn = a.nn
AND rownum = 1 )
)
SELECT ds.nn, NULL user_name, ds.audit_option
FROM detail_of_a_set_of_privs ds
UNION ALL
SELECT p.nn, p.user_name, NULL
FROM people p
ORDER BY 1, 2 NULLS FIRST, 3


So I can work with this but I got to thinking if one set of users have the following audits.

DELETE ANY TABLE
DELETE TABLE
INSERT ANY TABLE
INSERT TABLE
UPDATE ANY TABLE
UPDATE TABLE

and another set of users have the following statements audited

DELETE ANY TABLE
DELETE TABLE
INSERT ANY TABLE
INSERT TABLE
SELECT TABLE
UPDATE ANY TABLE
UPDATE TABLE

The only true difference would be the SELECT TABLE clause. So what do you think.....is this an issue to work but by brute force. I just have to review it myself and build the audit profiles one at a time. Or do you think there could be a way to build a query that would show a stack if a previous group is a subset of the next group something like.

GROUP 1
DELETE ANY TABLE
DELETE TABLE
INSERT ANY TABLE
INSERT TABLE
UPDATE ANY TABLE
UPDATE TABLE

GROUP 2
GROUP 1
SELECT TABLE

GROUP 3
GROUP 1
GROUP 2
TABLE
TRIGGER
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Creating 12c AUDIT PROFILES from standard audit setups
Author: Bruno Vroman, Belgium
Date: Feb 16, 2017, 18:30, 244 days ago
Message: Hi Tim,

I don't see how to do this but I have the feeling that it might be really complex...

Just ideas:
in fact the groups are simply represented by numbers, each number being a set of powers of two, and finding subsets is working at this "sets of powers of 2" level.
Example: we might have groups:
   6: 2 + 4

10: 2 + 8
12: 4 + 8
22: 2 + 4 + 16
30: 2 + 4 + 8 + 16
We have:
 6 is subset of 22 and 30

10 is subset of 30
12 is subset of 30
22 is subset of 30
How to find this and (maybe more complex) how to present this...

Well, my train leaves in 9 minutes ;-)

Best regards,

Bruno Vroman
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Creating 12c AUDIT PROFILES from standard audit setups
Author: Tim Boles, United States
Date: Feb 16, 2017, 20:14, 244 days ago
Message: Awesome hint....I appreciate it. Working on new sql...will post later. Thanks!!!!
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Creating 12c AUDIT PROFILES from standard audit setups
Author: Bruno Vroman, Belgium
Date: Feb 17, 2017, 12:31, 244 days ago
Score:   Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts
Message: Hi Tim,

unfortunately finding the "supersets" of a set is "easy" (6 is a subset of 22 and 30) but we need to work in the opposite directions and the difficulties are coming...
Example:
  6 = 2, 4
  10 = 2, 8
If you have the group "15" (that is: 1 + 2 + 4 + 8), would you say that
  15 is [ group 6 ] + 1 + 8
or that
  15 is [ group 10 ] + 1 + 4

or even "worse": we might say that
  15 is [ group 6 ] + [ group 10 ] + 1
this shows the next "difficulty": overlapping values...
Other example: lets say that you have groups
  14 = 2 + 4 + 8
  22 = 2 + 4 + 16
  62 = 2 + 4 + 8 + 16 + 32
Would we express 62 as [ 14 ] + [ 22 ] + 32? (we "count" 2 and 4 two times)

We have to decide this -and for example work on
  - "express as the addition with the minimal number of terms even if same privileges are counted multiple times"
  - "express as the addition of maximum ONE previous group + extra privileges
(+ anyway: way to decide between 'equivalent solutions respecting the chosen policy')

Looks like funny work but I'm afraid the results might be disappointing...

Best regards,

Bruno.
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Creating 12c AUDIT PROFILES from standard audit setups
Author: Tim Boles, United States
Date: Feb 17, 2017, 14:58, 244 days ago
Message: Well thanks Bruno for taking a look. And really it seems like brute force method is going to be easier at this point rather than automation. If I had 100's of databases that I was wanting to do this comparison for it might be worth while to find a script to do it. But since I don't I will just have to use another tool....like Excel to track and do my analysis on.

Regards
Tim
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Creating 12c AUDIT PROFILES from standard audit setups
Author: Bruno Vroman, Belgium
Date: Feb 17, 2017, 15:58, 244 days ago
Message: Indeed sometimes brute force is the way to go...
  https://xkcd.com/1205/
Best regards,
Bruno
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here