Cha1tu
asked on
i need to find out on this auditing result which one is the server name and which one in the username
Hi experts,
Can you please help me out on this issure...when i am executingthe this query in the database on which auditing is enabled.
SQL> select username, os_username, userhost from dba_audit_object;
USERNAME OS_USERNAM USERHOST
------------------ ---------- ------------------
SEAMENROLHANDI DRI DREVIL-VSRV
user1 tss agile9
user1 tss agile9
in the USERNAME column the result is mix of usernames and server names .i want to know which one is the username and servername
Can you please help me out on this issure...when i am executingthe this query in the database on which auditing is enabled.
SQL> select username, os_username, userhost from dba_audit_object;
USERNAME OS_USERNAM USERHOST
------------------ ---------- ------------------
SEAMENROLHANDI DRI DREVIL-VSRV
user1 tss agile9
user1 tss agile9
in the USERNAME column the result is mix of usernames and server names .i want to know which one is the username and servername
username is the Oracle user that issues the command you are auditting.
os_username is the name of the user logged in to the client's machine that connected as the Oracle user.
This might be a generic process id if the user is connecting through an application/web server rather than their own pc
userhost is the client's machine from which the Oracle user connected, this might be an application/web server and not the end-user's pc
none of them is the database server
os_username is the name of the user logged in to the client's machine that connected as the Oracle user.
This might be a generic process id if the user is connecting through an application/web server rather than their own pc
userhost is the client's machine from which the Oracle user connected, this might be an application/web server and not the end-user's pc
none of them is the database server
ASKER
@sdstuber:
no its mix of both server name and username..beacuse when i see the all the auditing results from past 1month..it is mix of both .
i don't y its giving the servername also..beacause i am querying the USERNAME itself...
how can query only server names and only usernames?
no its mix of both server name and username..beacuse when i see the all the auditing results from past 1month..it is mix of both .
i don't y its giving the servername also..beacause i am querying the USERNAME itself...
how can query only server names and only usernames?
ASKER
==> This might be a generic process id if the user is connecting through an application/web server rather than their own pc
userhost is the client's machine from which the Oracle user connected, this might be an application/web server and not the end-user's pc<==
I WANT OT KNOW ABOUT USER INFORMATION....
like you said the user is connecting through an application/web server rather than their own pc
i want to know which users are connecting from their OWN PC or from any other APPLICATION SERVER or WEB SERVER
userhost is the client's machine from which the Oracle user connected, this might be an application/web server and not the end-user's pc<==
I WANT OT KNOW ABOUT USER INFORMATION....
like you said the user is connecting through an application/web server rather than their own pc
i want to know which users are connecting from their OWN PC or from any other APPLICATION SERVER or WEB SERVER
either people are logging in with Oracle usernames that happen to be concatenations of other strings
or, your dba_audit_objects view has been tampered with.
The standard view does not do concatenation, it simply returns the oracle username of the session. Again though, if you have an oracle username
that is sdstubermypc and I login as sdstuber from mypc, that doesn't mean the view put my name and my client machine together, it simply means I logged in with that name
or, your dba_audit_objects view has been tampered with.
The standard view does not do concatenation, it simply returns the oracle username of the session. Again though, if you have an oracle username
that is sdstubermypc and I login as sdstuber from mypc, that doesn't mean the view put my name and my client machine together, it simply means I logged in with that name
>>> i want to know which users are connecting from their OWN PC or from any other APPLICATION SERVER or WEB SERVER
The oracle db doesn't know what you call your client.
If the client machine is a personal pc, or an app server or a web server or any other machine is immaterial.
To oracle, it's simply a client. The username is the oracle user of the session from that client. The standard dba_audit_object view does not do a name/machine concatenation.
Look in dba_users, what are the usernames there?
The oracle db doesn't know what you call your client.
If the client machine is a personal pc, or an app server or a web server or any other machine is immaterial.
To oracle, it's simply a client. The username is the oracle user of the session from that client. The standard dba_audit_object view does not do a name/machine concatenation.
Look in dba_users, what are the usernames there?
>>The oracle db doesn't know what you call your client.
I might be missing something here, but Oracle does know this. USERHOST and TERMINAL fields in dba_audit_object contain a good bit of this.
If you want more, write a logon trigger to grab all the fields from V$SESSION, which include fields like PROGRAM, which will clearly indicate whether users are connecting from sqlplus, Toad, JDBC app server, etc.
I might be missing something here, but Oracle does know this. USERHOST and TERMINAL fields in dba_audit_object contain a good bit of this.
If you want more, write a logon trigger to grab all the fields from V$SESSION, which include fields like PROGRAM, which will clearly indicate whether users are connecting from sqlplus, Toad, JDBC app server, etc.
ASKER
@mrjoltcola:
can you tell the script for a logon trigger to grab all the fields from V$SESSION
can you tell the script for a logon trigger to grab all the fields from V$SESSION
>>> the oracle db doesn't know what you call your client.
sorry, what I meant by that is Oracle doesn't know if you call your client "my pc" or if you call it "my app server" or if you call it "my web server"
my point was, everything is just "a client" to the db
sorry for the confusion
sorry, what I meant by that is Oracle doesn't know if you call your client "my pc" or if you call it "my app server" or if you call it "my web server"
my point was, everything is just "a client" to the db
sorry for the confusion
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
COLUMN OF UR USER NAME IS YOUR USER
COLUMN OF UR USER HOST IS YOUR SERVER NAME
COLUMN OF UR USER HOST IS YOUR SERVER NAME
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
select username + "|" + os_username + "|" + userhost from dba_audit_object;
Greetings