?
Solved

i need to find out  on this auditing result which one is  the server name and which one in the username

Posted on 2011-04-19
12
Medium Priority
?
459 Views
Last Modified: 2013-12-18
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
0
Comment
Question by:Cha1tu
  • 5
  • 3
  • 2
  • +2
12 Comments
 
LVL 7

Expert Comment

by:JuanCarniglia
ID: 35425902
Maybe doing something like:

select username + "|" + os_username + "|" + userhost from dba_audit_object;

Greetings
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 35425907
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



0
 
LVL 2

Author Comment

by:Cha1tu
ID: 35426890
@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?
0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
LVL 2

Author Comment

by:Cha1tu
ID: 35426924
==> 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

0
 
LVL 74

Expert Comment

by:sdstuber
ID: 35426955
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
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 35426986
>>> 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?
0
 
LVL 40

Expert Comment

by:mrjoltcola
ID: 35448441
>>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.
0
 
LVL 2

Author Comment

by:Cha1tu
ID: 35448638
@mrjoltcola:

can you tell the script for a logon trigger to grab all the fields from V$SESSION
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 35448738
>>> 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
0
 
LVL 40

Accepted Solution

by:
mrjoltcola earned 1000 total points
ID: 35448804
If you creat this as sys, the grant below isn't needed, but I do it as system, and you need a direct grant for the underlying view for the trigger to work.

-- connect internal
grant select on v_$session to system;

-- Login as system, create your audit table to be the same structure as v$session
create table logon_log as select * from sys.v_$session where 1 = 0;

create or replace trigger logon_audit_trigger
AFTER LOGON ON DATABASE
BEGIN
   insert into logon_log select * from sys.v_$session where audsid = sys_context('USERENV','SESSIONID');
END;
/

Open in new window

0
 
LVL 2

Expert Comment

by:spyresponse
ID: 35453480
COLUMN OF UR USER NAME IS YOUR USER
COLUMN OF UR USER HOST IS YOUR SERVER NAME
0
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 1000 total points
ID: 35453504
spyresonse,  that's the same thing said above.  please read all previous posts to be sure you're not posting duplicate information

but,  "server name" is a bit ambiguous - could be the db server, app server, web server, other server...
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question