SYS_CONTEXT
Randy, January 24, 2002 - 10:24 am UTC
Your approch of sys_context('userenv','ip_address') is fine as long as I am doing any client-server connection. But if I telnet into the server, sys_context(...) cannot provide the ip of the telnet client. It appears blank.
SQL> select sys_context('userenv','ip_address') from dual;
SYS_CONTEXT('USERENV','IP_ADDRESS')
--------------------------------------------------------------------------------
January 25, 2002 - 8:18 am UTC
Yes, because you are not connecting over net8 -- it only works with network connections, not local connections. If the ip_address is "blank", the ip_address should be obvious -- its the server. You can get your hostname from "select host_name from v$instance" for logging purposes.
A Reader
Rahul Sharma, May 29, 2002 - 3:12 am UTC
Hi Tom
Is there any data dictionary in Oracle where it stores the IP addresses of connected cleint machines?
When users connect in databsae through Java client and I try to find out the name of the machine from where connecttions are coming, it gives me terminal name Unknown.
I fired
SQL> select username,terminal,machine from v$session;
USERNAME TERMINAL MACHINE
------------------------------ ---------------- -----------
SCOTT unknown jdbcclient
SCOTT unknown jdbcclient
USER1 unknown jdbcclient
USER2 unknown jdbcclient
USER2 unknown jdbcclient
From where I can get the TERMINAL/MACHINE or ip address information of connected users.
Thanks.
May 29, 2002 - 7:36 am UTC
You can get that if you AUDIT CONNECT (use auditing)
You could also create a trigger:
create or replace trigger on_logon_trigger
after logon on database
begin
dbms_application_info.set_client_info( 'logon ' || sys_context( 'userenv', 'ip_address' ) );
end;
/
to store that info in v$session....
assign profiles
A reader, October 02, 2002 - 5:33 am UTC
hi
can we user logon trigger to assign user a specific profile?
it seems DDL does not work with logon triggers even executing a procedure which uses autonomous transaction
give error
ORA-04045: errors during recompilation/revalidation of CM.LOGON
ORA-01031: insufficient privileges
set profile
A reader, October 02, 2002 - 1:30 pm UTC
Hi
I tried
alter user username profile XXXX
I first wrote it in the logon trigger but I got error in alert complaining about DDL, I then put the code in a stored procedure with pragma autonomous transaction and call it from trigger then I get the error I posted in my previous post
Just a further question is it possible to change a user's profile per session?
October 02, 2002 - 7:28 pm UTC
no, that would change the users profile period
bad idea -- terrible concept -- rethink what you are trying to do. DO NOT do this .
Vini, June 20, 2003 - 10:46 am UTC
hi tom,
when I executing sql commands (not pl/sql), when error comes, I wants to know that by executing that command, I got an error.
What is the method for that?
Thank you in advance
June 20, 2003 - 5:29 pm UTC
you read the screen?
not sure what you mean or what environment you might be working in at all.
Restricting from local connections.
Reddy, March 16, 2005 - 7:07 am UTC
Hi,
I am trying to restrict access to to my DB from outside users using a logon trigger. But its working fine only for network or remote connections. Anyway to restrict the users connecting from the DB Host as well (other than changing sqlplus.exe to something.exe)?
Thanks
Reddy
March 16, 2005 - 8:32 am UTC
why isn't it working for local connections? that would be a bug in your trigger (or the people have DBA, DBA can always connect even if the trigger fails)
the problem lies in the trigger itself, it is firing -- you just are not killing their session or they are dba's
ON LOGON TRIGGER ' SYS_LOGON '
Cee Gee, April 27, 2005 - 2:06 pm UTC
April 27, 2005 - 2:26 pm UTC
insufficient data there, you seem to show that you have no logon triggers.
The code that is blowing up is happening AFTER you have already logged in (you would not have the sqlplus banner from the database otherwise)
a failed logon trigger would log into your alert log, not into sqlplus.
ops$tkyte@ORA10G> create or replace trigger log_trigger after logon on database
2 begin
3 raise_application_error( -20001, 'NO' );
4 end;
5 /
Trigger created.
and then:
$ sqlplus a/a
SQL*Plus: Release 10.1.0.3.0 - Production on Wed Apr 27 14:22:49 2005
Copyright (c) 1982, 2004, Oracle. All rights reserved.
ERROR:
ORA-00604: error occurred at recursive SQL level 1
ORA-20001: NO
ORA-06512: at line 2
Enter user-name:
you see -- you never get connected, you cannot get the banner out.
AND a dba would not be affected:
Enter user-name: /
Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
ops$tkyte@ORA10G> drop trigger log_trigger;
Trigger dropped.
Time to go as "SAMS" whats up and what have you done to me.
Resolved the error but left with a question
CG, April 27, 2005 - 3:42 pm UTC
I found the 'poor logic' in my code ( a comma after a role name as a literal ) and compiled it. Now when I log in as SAMS I get:
SQL*Plus: Release 9.2.0.6.0 - Production on Wed Apr 27 15:35:38 2005
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production
PL/SQL procedure successfully completed.
SQL>
.... which leaves me with some questions.
0 Why does a runtime error in another schema happen after
logging in as SYSDBA?
0 If logon triggers are not a factor, how does code execute
"arbitrarily" as it did?
0 Can anyfiles other then login.sql and glogin.sql be
executed if it is in the dir listing for environment
variable SQLPATH?
April 27, 2005 - 4:33 pm UTC
you are running some piece of code in your login.sql or glogin.sql
else the plsql procedure .... bit would not be there.
this is NOT a login trigger, this is a sqlplus script.
ipaddress
sreenivas, April 28, 2005 - 5:36 am UTC
Hi Tom,
I have three application server (clusterd) connecting to the database runnig on 10.145.0.4
the application servers as follows.
appl (10.145.0.1)
app2 (10.145.0.2)
app3 (10.145.0.3)
DB should accept connection with only above four IP addresses.
All three application servers connects through a schema,which is having DBA ROLE.
could give me logon trigger for this.
regards,
sreenivas
April 28, 2005 - 8:08 am UTC
ipaddress
sreenivasa rao, April 29, 2005 - 1:53 am UTC
Thanks for the easy and simple solution.
Thin client information
Wael, June 21, 2005 - 3:06 am UTC
When trying to get the client information for connections over the Intranet, you will always get the Application Server information.
Is there any way to get the thin client information, including, IP Address, OS username ..
Thanks.
June 21, 2005 - 4:36 pm UTC
I assume you are speaking of "forms"? If so, suggest you query the discussion forum on otn.oracle.com, I've not used forms in a decade myself.
PROC_ROLE logon trigger
Mark, January 03, 2006 - 10:02 am UTC
Hi Tom,
In 8i, I got this to work. In 9i, I get the following:
CREATE OR REPLACE FUNCTION HT4.PROC_ROLE (p_role IN VARCHAR2)
RETURN NUMBER
IS
l_cnt NUMBER;
BEGIN
SELECT COUNT(*)
INTO l_cnt
FROM dual
WHERE EXISTS(SELECT NULL
FROM sys.user$ u1, sys.sysauth$ sa
WHERE u1.user# = sa.privilege#
AND sa.grantee# = UID
AND u1.name = UPPER(p_role));
RETURN l_cnt;
END;
/
Warning: Function created with compilation errors.
Elapsed: 00:00:00.16
HT4:PHYDB001101443:10:14 - QADB> show errors
Errors for FUNCTION HT4.PROC_ROLE:
LINE/COL ERROR
-------- -----------------------------------------------------------------
6/4 PL/SQL: SQL Statement ignored
10/43 PL/SQL: ORA-00942: table or view does not exist
HT4:PHYDB001101443:10:14 - QADB>
The current schema is HT4, and this function is created witihin that schema. Says it cannot 'see' sys.sysauth$.
I'll admit I'm rather inexperienced at this part of oracle - grants, privileges, etc.
January 03, 2006 - 10:08 am UTC
you are missing grants - but also, why are you querying the sys. tables directly like that?
you would need select granted directly to the owner of that trigger on all of the sys. tables you are attempting to access.
ok
Mark, January 03, 2006 - 11:01 am UTC
Got it tom.
Gave SELECT to the user to sys.user$ and sys.sysauth$ and that worked.
By the way, that code is from this site - I was trying previously to set up the environment for a specific user (namely, CURSOR_SHARING), and that is what we came up with.
Thanks again!