OraFAQ Forum: Security » proxy users

Home » RDBMS Server » Security » proxy users - DBA_AUDIT_TRAIL (11gR2)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

proxy users - DBA_AUDIT_TRAIL [message #634043]

Tue, 03 March 2015 09:08

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!

Report message to a moderator

Re: proxy users - DBA_AUDIT_TRAIL [message #634044 is a reply to message #634043]

Tue, 03 March 2015 09:14

BlackSwan
Messages: 26766
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".

Report message to a moderator

Re: proxy users - DBA_AUDIT_TRAIL [message #634046 is a reply to message #634043]

Tue, 03 March 2015 09:18

Michel Cadot
Messages: 68415
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.

Report message to a moderator

Re: proxy users - DBA_AUDIT_TRAIL [message #634047 is a reply to message #634044]

Tue, 03 March 2015 09:19

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.

Report message to a moderator

Re: proxy users - DBA_AUDIT_TRAIL [message #634048 is a reply to message #634047]

Tue, 03 March 2015 09:22

Michel Cadot
Messages: 68415
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.

Report message to a moderator

Re: proxy users - DBA_AUDIT_TRAIL [message #634049 is a reply to message #634046]

Tue, 03 March 2015 09:40

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

Report message to a moderator

Re: proxy users - DBA_AUDIT_TRAIL [message #634050 is a reply to message #634048]

Tue, 03 March 2015 09:42

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.

Report message to a moderator

Re: proxy users - DBA_AUDIT_TRAIL [message #634052 is a reply to message #634050]

Tue, 03 March 2015 10:26

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 ;

Report message to a moderator

Re: proxy users - DBA_AUDIT_TRAIL [message #634054 is a reply to message #634052]

Tue, 03 March 2015 10:35

Michel Cadot
Messages: 68415
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

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

Report message to a moderator

Re: proxy users - DBA_AUDIT_TRAIL [message #634055 is a reply to message #634054]

Tue, 03 March 2015 10:40

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.

Report message to a moderator

Re: proxy users - DBA_AUDIT_TRAIL [message #634057 is a reply to message #634055]

Tue, 03 March 2015 11:20

Michel Cadot
Messages: 68415
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!

Report message to a moderator

Re: proxy users - DBA_AUDIT_TRAIL [message #634059 is a reply to message #634043]

Tue, 03 March 2015 12:49

John Watson
Messages: 8803
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

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.

Report message to a moderator

Previous Topic:	proxy user - login issue
Next Topic:	Change control for PL/SQL

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Mar 22 00:27:26 CDT 2023