Home » RDBMS Server » Security » proxy users - DBA_AUDIT_TRAIL (11gR2)
proxy users - DBA_AUDIT_TRAIL [message #634043] Tue, 03 March 2015 09:08 Go to next message
candi
Messages: 39
Registered: July 2012
Member
Does anyone have any ideas for a query for audit of proxy users? For audit reasons generic logins no longer allowed. Querying DBA_AUDIT_TRAIL - as in report showing proxy user activity - PROXY_SESSION_ID = SESSION_ID.

not getting results required from joining table to itself i.e.


select A.OS_USERNAME, A.USERNAME,A.TIMESTAMP, A.ACTION, A.ACTION_NAME, A.COMMENT_TEXT, A.SESSIONID, A.PROXY_SESSIONID
FROM DBA_AUDIT_TRAIL A, DBA_AUDIT_TRAIL B
WHERE A.PROXY_SESSIONID = B.SESSIONID




thanks in advance!
Re: proxy users - DBA_AUDIT_TRAIL [message #634044 is a reply to message #634043] Tue, 03 March 2015 09:14 Go to previous messageGo to next message
BlackSwan
Messages: 26733
Registered: January 2009
Location: SoCal
Senior Member
>not getting results required from joining table to itself i.e.

My crystal ball is in the shop for repair.
Give us a clue as to what exactly are your "required results".
Re: proxy users - DBA_AUDIT_TRAIL [message #634046 is a reply to message #634043] Tue, 03 March 2015 09:18 Go to previous messageGo to next message
Michel Cadot
Messages: 67366
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

And explain what is the purpose of "WHERE A.PROXY_SESSIONID = B.SESSIONID"?

I fail to see what you want to get.

Re: proxy users - DBA_AUDIT_TRAIL [message #634047 is a reply to message #634044] Tue, 03 March 2015 09:19 Go to previous messageGo to next message
candi
Messages: 39
Registered: July 2012
Member
BlackSwan wrote on Tue, 03 March 2015 09:14
>not getting results required from joining table to itself i.e.

My crystal ball is in the shop for repair.
Give us a clue as to what exactly are your "required results".



Indeed BS a crystal ball would be great.

Basically I need the proxy user to be the DBA_AUDIT_TRAIL.USERNAME...I don't want the generic user in the query output.
Re: proxy users - DBA_AUDIT_TRAIL [message #634048 is a reply to message #634047] Tue, 03 March 2015 09:22 Go to previous messageGo to next message
Michel Cadot
Messages: 67366
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
Basically I need the proxy user to be the DBA_AUDIT_TRAIL.USERNAME


You have in this column what Oracle puts in it.
If this is not what you want refer to Oracle.

I still fail to see what you want.
Put a CLEAR example.

Re: proxy users - DBA_AUDIT_TRAIL [message #634049 is a reply to message #634046] Tue, 03 March 2015 09:40 Go to previous messageGo to next message
candi
Messages: 39
Registered: July 2012
Member
Michel Cadot wrote on Tue, 03 March 2015 09:18

And explain what is the purpose of "WHERE A.PROXY_SESSIONID = B.SESSIONID"?

I fail to see what you want to get.




The proxy user logs in and is assigned a session id which is equivalent to the proxy_sessionid field: i.e. I have logged in as proxy user, created a table and logged out:


USERNAME	ACTION_NAME	            SESSIONID	PROXY_SESSIONID
PROXYONE	PROXY AUTHENTICATION ONLY   80022050	
GENERIC	        LOGON	                    80022051	80022050
GENERIC	        CREATE TABLE	            80022051	80022050
GENERIC	        CREATE TABLE	            80022051	80022050
GENERIC	        CREATE TABLE	            80022051	80022050
GENERIC	        LOGOFF	                    80022051	80022050



Re: proxy users - DBA_AUDIT_TRAIL [message #634050 is a reply to message #634048] Tue, 03 March 2015 09:42 Go to previous messageGo to next message
candi
Messages: 39
Registered: July 2012
Member
Michel Cadot wrote on Tue, 03 March 2015 09:22

Quote:
Basically I need the proxy user to be the DBA_AUDIT_TRAIL.USERNAME


You have in this column what Oracle puts in it.
If this is not what you want refer to Oracle.

I still fail to see what you want.
Put a CLEAR example.



I don't have an example unfortunately.
A clear report that it is a proxy user that is being audited and not the generic user.
Re: proxy users - DBA_AUDIT_TRAIL [message #634052 is a reply to message #634050] Tue, 03 March 2015 10:26 Go to previous messageGo to next message
candi
Messages: 39
Registered: July 2012
Member
example:


select p.username, g.username as authenicator, g.*
FROM DBA_AUDIT_TRAIL p, DBA_AUDIT_TRAIL g
where p.timestamp > (sysdate -1/24) and g.timestamp > (sysdate -1/24) 
and p.username in ('PROXYONE','GENERIC') and g.username in ('PROXYONE','GENERIC')
and p.sessionid = g.proxy_sessionid
order by p.timestamp ;


Re: proxy users - DBA_AUDIT_TRAIL [message #634054 is a reply to message #634052] Tue, 03 March 2015 10:35 Go to previous messageGo to next message
Michel Cadot
Messages: 67366
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

And from the example you gave, what should be the result?

Re: proxy users - DBA_AUDIT_TRAIL [message #634055 is a reply to message #634054] Tue, 03 March 2015 10:40 Go to previous messageGo to next message
candi
Messages: 39
Registered: July 2012
Member
Michel Cadot wrote on Tue, 03 March 2015 10:35

And from the example you gave, what should be the result?



a report showing clearly that the user was logged in as proxy and not the generic user.
Re: proxy users - DBA_AUDIT_TRAIL [message #634057 is a reply to message #634055] Tue, 03 March 2015 11:20 Go to previous messageGo to next message
Michel Cadot
Messages: 67366
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Tue, 03 March 2015 17:35

And from the example you gave, what should be the result?


SHOW US!

Re: proxy users - DBA_AUDIT_TRAIL [message #634059 is a reply to message #634043] Tue, 03 March 2015 12:49 Go to previous messageGo to next message
John Watson
Messages: 8344
Registered: January 2010
Location: Global Village
Senior Member
You just need to enable te audit of the proxy. For example,

audit select table on emp by scott on behalf of fred;

--update: sorry, I had slightly wrong syntax. You can't audit access to a table, only use of a privilege.
So it would have to be:
audit select table by scott on behalf of fred;


[Updated on: Tue, 03 March 2015 17:12]

Report message to a moderator

Re: proxy users - DBA_AUDIT_TRAIL [message #634250 is a reply to message #634059] Fri, 06 March 2015 08:43 Go to previous message
candi
Messages: 39
Registered: July 2012
Member
John Watson wrote on Tue, 03 March 2015 12:49
You just need to enable te audit of the proxy. For example,

audit select table on emp by scott on behalf of fred;

--update: sorry, I had slightly wrong syntax. You can't audit access to a table, only use of a privilege.
So it would have to be:
audit select table by scott on behalf of fred;




This is not an option as proxy user will be creating tables etc.
I just want to show, in a view on DBA_AUDIT_TRAIL, that the login/actions is a proxy and not the schema name.
The username field of dba_audit_trail just shows the schema name once the proxy has logged in.
So proxy logs in - 2 entries in dba_audit_trail for login and authentication by proxy. Then all subsequent tasks (create/truncate etc.) are shown as the schema (generic) user in dba_audit_trail.
Previous Topic: proxy user - login issue
Next Topic: Change control for PL/SQL
Goto Forum:
  


Current Time: Tue Sep 22 13:06:42 CDT 2020