For Oracle 7.3
A reader, June 22, 2003 - 9:20 am UTC
If i want to run this commands in oracle 7.3?
June 22, 2003 - 1:24 pm UTC
not going to happen, neither of them.
you can get the client ip address if you audit connect, it is in the comments column of the audit trail, you have to parse it out.
Remote login
A reader, June 23, 2003 - 12:07 am UTC
Hi TOM
Thank you very much for your answer it help to resolve an important problem
But you say "Let your best and brightest DBA's have SYSDBA or SYSOPER and don't allow for
remote sysdba/sysoper login and you'll never have a remote SYS login"
How can I do IT ? It was 3 month, I try to looking for a solution for this question
Thank you very much
Have a good day and best Regards
NGUYEN ORACLE DBA
June 23, 2003 - 8:07 am UTC
grant sysdba/sysoper to the dba's that should have it.
they can now
SQL> connect their_user/their_password AS SYSDBA;
they'll be in as sys but using their credentials.
Now, if you don't create a password file with orapwd and don't set up the remote login password file, they won't be able to connect over the net. they'll have to be ON the machine itself. consider:
idle> connect ops$tkyte/xxxx@ora920.us.oracle.com AS SYSDBA;
Connected.
idle> show user
USER is "SYS"
<b>i'm able to remotely log in as SYSDBA (sys) using my credentials</b>
idle> show parameter remote_log
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
remote_login_passwordfile string EXCLUSIVE
idle> alter system set remote_login_passwordfile=NONE scope=spfile;
System altered.
idle> shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.
idle> connect / as sysdba
Connected to an idle instance.
<b>I can still connect as sysdba locally (ops$tkyte is my os authenticated account)</b>
idle> startup
ORACLE instance started.
Total System Global Area 370217796 bytes
Fixed Size 451396 bytes
Variable Size 100663296 bytes
Database Buffers 268435456 bytes
Redo Buffers 667648 bytes
Database mounted.
Database opened.
idle> connect ops$tkyte/xxx@ora920.us.oracle.com AS SYSDBA;
ERROR:
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.
<b>but I cannot connect remotely as sysdba anymore</b>
idle> connect ops$tkyte/xxx@ora920.us.oracle.com
Connected.
idle> show user
USER is "OPS$TKYTE"
idle>
<b>I have to use a local connection to get "SYSDBA"</b>
Oracle 7.3
A reader, June 23, 2003 - 4:39 am UTC
Can you explain in full steps ,how can i get IP address with audit trail option cuz i don;t know what is audit trail(i am new programmer)?
June 23, 2003 - 8:17 am UTC
simple search for
7.3 ip address
on this site......
REMOTE LOGIN PASSWORD
A reader, June 23, 2003 - 3:54 pm UTC
Hi Tom
Thank you very much for your quickly answers.
Following your method 'I need to use OS authentification'
( Remote_login_password=None). But my problem IS :
If an user is SYS then I disallow a remote login and
for a remote client the login should be authentificated by ORACLE normally not by operating system ( Remote_login_password=exclusive).
Could you please help me to find out a solution for this headache problem
Best regards
NGUYEN ORACLE DBA (PARIS FRANCE)
June 24, 2003 - 7:22 am UTC
that is fine, what I've shown you supports that (and I demonstrated that)
I can connect locally:
user/pass as sysdba
I can connect remotely
user/pass@db
I CANNOT remote connect as sysdba
user/pass@db as sysdba <<<=== fails.
You got what you want.
client ip address in psp
A reader, March 20, 2007 - 3:09 pm UTC
sir, how would i get a client ip address who is connect through pl/sql server pages for auditing purpose?
March 20, 2007 - 4:23 pm UTC
the client would be the application server, and sys_context can be used to get that if they (the middle tier) connected over sqlnet.
the browser - you might be able to use owa_util.get_cgi_env('REMOTE_ADDR') - but firewalls and such can obscure the true IP address (eg: my ip address is really 192.168.1.12 but I'm behind a nat enabled router with a firewall so you would never see that)
Getting error while getting IP in domain
Avi, January 06, 2010 - 11:51 pm UTC
Dear Tom,
When I try to get IP address of a particular user then I'm getting below error. Any solution for getting IP address in domain.
11:11:03 > select sid,machine,username, logon_time from v$session where username ='A';
SID MACHINE USERNAME LOGON_TIME
----- ---------------------------------------------------------------- ---------- --------------------------
1092 LGE\IL-MF10-NE0141 A 07-JAN-2010 11:11:18
11:11:31 > select sid from v$session where machine='LGE\IL-MF10-NE0141';
SID
-----
1092
11:16:22 > select SID,USERNAME,MACHINE,utl_inaddr.get_host_address(substr(machine,instr(machine,'\')+1)) IP from v$session where sid in (1092);
select SID,USERNAME,MACHINE,utl_inaddr.get_host_address(substr(machine,instr(machine,'\')+1)) IP from v$session where sid in (1092)
*
ERROR at line 1:
ORA-29257: host IL-MF10-NE0141 unknown
ORA-06512: at "SYS.UTL_INADDR", line 35
ORA-06512: at "SYS.UTL_INADDR", line 40
ORA-06512: at line 1
11:18:56 >
January 11, 2010 - 8:11 am UTC
$ oerr ora 29257
29257, 00000, "host %s unknown"
// *Cause: The specified host was unknown.
// *Action: Check the spelling of the host name or the IP address. Make sure
// that the host name or the IP address is valid.
if you logged onto the server and did:
$ ping IL-MF10-NE0141
you'd likely see the same thing.
the machine name is not able to be resolved into an IP address, that is all, nothing wrong in the database.
help me plz
micheal, May 20, 2010 - 2:16 am UTC
hi how are you my Question someone do bad something in my database in domain so How can I know the ip address of the person Booze
May 24, 2010 - 11:29 am UTC
plz? German postal codes - can you explain what they have to do with this?
booze?
sorry, but unless you are a bit more 'precise' about what you mean by "bad something" - we cannot help
ok ok
michael, May 26, 2010 - 4:44 am UTC
am new dba and i have database so i gev users permission for work and in last week i catch destruction in my database (user)
when i ask him about what happend he say am not do that som one know my user and password , so how can i know who know his user name and password ip address <i work in domain>
May 26, 2010 - 7:57 am UTC
if you have all of the archive redo logs, you can use logminer to mine them to see what users did (very very tedious).
You don't say still what they actually did, so I cannot possibly give you any shortcuts (not that a short cut must exist, but if one does - I cannot tell you what it is because you haven't told us anything useful yet...)
But - and this is important - I fail to see how the user logging in as themselves could do anything disastrous. They can only do what you PERMIT them to do via grant - have you granted users too many privileges - if so, it is not their fault, it is the fault entirely of the person that granted the privileges.
You might want to employ auditing for the future.
(Jordan is very far away from the USA isn't it?)
thx dear tom
michael waked, May 30, 2010 - 3:15 am UTC
i get it and am sorry about location am from jordan nice to meet u too agean good job brother