No question at this time
DBA Top 10
1 M. Cadot 22100
2 A. Kavsek 15500
3 M. Hidayathullah ... 11000
4 B. Vroman 7900
5 P. Wisse 6000
6 T. Boles 5400
7 J. Schnackenberg 3300
8 G. Lambregts 2200
8 P. Knibbs 2200
8 K. Pagadala 2200
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 users48016
Total active users1849
Act. users last 24h10
Act. users last hour0
Registered user hits last week306
Registered user hits last month1339
Go up

Question on creating a report in SQL
Next thread: pluggeable database compatiability
Prev thread: Release of 12.2 Enterprise Version

Message Score Author Date
This is probably easy but then again. I am trying...... Tim Boles Jan 09, 2017, 22:58
Hello Tim, here is my trial, working "piece by ...... Score: 400 PtsScore: 400 PtsScore: 400 PtsScore: 400 PtsScore: 400 Pts Bruno Vroman Jan 10, 2017, 14:33
Wow...thanks Bruno for putting so much time into c...... Score: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 Pts Tim Boles Jan 10, 2017, 16:25

Follow up by mail Click here


Subject: Question on creating a report in SQL
Author: Tim Boles, United States
Date: Jan 09, 2017, 22:58, 219 days ago
Os info: ANY
Oracle info: ANY
Message: This is probably easy but then again. I am trying to create a report showing individuals that have the same statement audit options set within a database. I am using dba_stmt_audit_opts

desc dba_stmt_audit_opts
Name Null? Type
--------------------------------------------------------- -------- ---------------------------------------
USER_NAME VARCHAR2(30)
PROXY_NAME VARCHAR2(30)
AUDIT_OPTION NOT NULL VARCHAR2(40)
SUCCESS VARCHAR2(10)
FAILURE VARCHAR2(10)


Entries


USER_NAME AUDIT_OPTION SUCCESS FAILURE
---------------------------- ---------------------------------------- ---------- ----------
S1169 DELETE ANY TABLE BY SESSION BY SESSION
S1169 INSERT ANY TABLE BY SESSION BY SESSION
S1169 UPDATE ANY TABLE BY SESSION BY SESSION
S1172 DELETE ANY TABLE BY SESSION BY SESSION
S1172 DELETE TABLE BY SESSION BY SESSION
S1172 INSERT ANY TABLE BY SESSION BY SESSION
S1172 INSERT TABLE BY SESSION BY SESSION
S1172 UPDATE ANY TABLE BY SESSION BY SESSION
S1172 UPDATE TABLE BY SESSION BY SESSION
S1173 DELETE ANY TABLE BY SESSION BY SESSION
S1173 INSERT ANY TABLE BY SESSION BY SESSION
S1173 UPDATE ANY TABLE BY SESSION BY SESSION
S1257 DELETE ANY TABLE BY SESSION BY SESSION
S1257 DELETE TABLE BY SESSION BY SESSION
S1257 INSERT ANY TABLE BY SESSION BY SESSION
S1257 INSERT TABLE BY SESSION BY SESSION
S1257 UPDATE ANY TABLE BY SESSION BY SESSION
S1257 UPDATE TABLE BY SESSION BY SESSION


What I want to see is the users listed based on groups of options.

USER_NAME AUDIT_OPTION SUCCESS FAILURE
---------------------------- ---------------------------------------- ---------- ----------
DELETE ANY TABLE BY SESSION BY SESSION
INSERT ANY TABLE BY SESSION BY SESSION
UPDATE ANY TABLE BY SESSION BY SESSION
S1169
S1173
S1257
DELETE ANY TABLE BY SESSION BY SESSION
DELETE TABLE BY SESSION BY SESSION
INSERT ANY TABLE BY SESSION BY SESSION
INSERT TABLE BY SESSION BY SESSION
UPDATE ANY TABLE BY SESSION BY SESSION
UPDATE TABLE BY SESSION BY SESSION
S1257
S1172
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Question on creating a report in SQL
Author: Bruno Vroman, Belgium
Date: Jan 10, 2017, 14:33, 218 days ago
Score:   Score: 400 PtsScore: 400 PtsScore: 400 PtsScore: 400 PtsScore: 400 Pts
Message: Hello Tim,

here is my trial, working "piece by piece":

all_options_seen: all [audit_option success failure] seen in dba_stmt_audit_opts

numbered: same as previous but each line with a number: 1, 2, 4, 8, ...

people: list of users, each with the SUM of its options (example: first and third option will result in 1+4=5)

all_sets_of_privs: The various values for the "SUM" gives the number of sets in the report (example: 1, 5 and 9 if there are people with first option alone, people with option 1 and 4, people with options 1 and 8)

detail_of_a_set_of_privs: "going back": for one of the possible "numbers" (example: 5), list the corresponding option(s) (example: opt 1 and 4).

final select: UNION ALL between lists of options and list of people having them. order by "the SUM number" to list first all the corresponding options in this "set" and then the list of users having them
CLEAR BREAKS

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

WITH all_options_seen AS
( SELECT d.audit_option, d.success, d.failure
FROM dba_stmt_audit_opts d
GROUP BY d.audit_option, d.success, d.failure
ORDER BY 1, 2, 3
)
, numbered AS
( SELECT POWER( 2, rownum - 1 ) n, audit_option, success, failure
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
AND NVL( n.success, 'A' ) = NVL( a.success, 'A' )
AND NVL( n.failure, 'A' ) = NVL( a.failure, 'A' )
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, d.success, d.failure
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, ds.success, ds.failure
FROM detail_of_a_set_of_privs ds
UNION ALL
SELECT p.nn, p.user_name, NULL, NULL, NULL
FROM people p
ORDER BY 1, 2 NULLS FIRST, 3, 4, 5
;
;-)

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: Question on creating a report in SQL
Author: Tim Boles, United States
Date: Jan 10, 2017, 16:25, 218 days ago
Score:   Score: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 Pts
Message: Wow...thanks Bruno for putting so much time into creating an answer for me.

I really appreciate it.

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