-- Thanks for the question regarding "logon trigger", version 8.1.6.3

Submitted on 27-Jun-2001 11:15 Central time zone

Tom's latest followup | Bookmark | Bottom

Last updated 3-Jan-2006 10:08

You Asked

Hi

I am trying to code a small logon trigger and control the machines that can connect to 
the database


create or replace trigger ctrl_host
   after logon on database
declare
   host number := 0;
   dba number := 0;
begin
   select 1 into host from v$session
        where osuser<>'SLE7412';
   select 1 into dba from dba_role_privs
        where grantee= USER and granted_role='DBA';
   if host = 1 and dba = 0 then
      raise_application_error(-1, 'Connection Refused');
   end if;
end;
/


This doesnt work because I would never get a row in the first select, I tried to use 
dbms_lock.sleep(3) and see if after 3 seconds the user would login with no problems but 
it doesnt seem to be feasible since the user is still waiting for this trigger to finish 
it´s *firing time* and get the permission to connect. Is my logic wrong?

the reason of selecting 1 into a variable is to use that variable later in the IF part. 
Any user trying to connect from host SLE7412 will fail but if the user has DBA privilege 
then he should be able to do so.
in my IF if the user is from host SLE7412 and is a normal user an error would be raised, 
if the user is an user with DBA role then he can logon no matter the host


cheers

and we said...

well, your logic does not say that at all.

firstly, you are querying based on osuser, not the host:

   select 1 into host from v$session
        where osuser<>'SLE7412';

secondly, you are querying ALL records in v$session, not this sessions record.

You want to use the IP_ADDRESS for this as the host name would be reported by the client 
and the client can lie to you.  We can get the ip address easily:

scott@ORA8I.WORLD> select sys_context('userenv', 'ip_address' ) from dual;

SYS_CONTEXT('USERENV','IP_ADDRESS')
------------------------------------------------------------------------------------------
-----------------------------------------
138.2.5.51

So, you should do something like:

  if ( sys_context('userenv','ip_address') = 'IP ADDRESS OF BAD HOST' ) AND
       proc_role( 'DBA' ) <> 1 )
  then
        raise_application_error( -20001, 'connection refused' );
  end if;


see

http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:1362403157367

for proc_role -- faster implementation there.

Reviews

SYS_CONTEXT January 24, 2002 - 10am Central time zone

Bookmark | Bottom | Top

Reviewer: Randy from Chicago,IL

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')
--------------------------------------------------------------------------------

Followup January 25, 2002 - 8am Central time zone:

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 May 29, 2002 - 3am Central time zone

Bookmark | Bottom | Top

Reviewer: Rahul Sharma from India

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.

Followup May 29, 2002 - 7am Central time zone:

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 October 2, 2002 - 5am Central time zone

Bookmark | Bottom | Top

Reviewer: A reader

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

Followup October 2, 2002 - 10am Central time zone:

what are you trying to do in the trigger (what "command")

read

http://asktom.oracle.com/~tkyte/Misc/RolesAndProcedures.html

set profile October 2, 2002 - 1pm Central time zone

Bookmark | Bottom | Top

Reviewer: A reader

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?

Followup October 2, 2002 - 7pm Central time zone:

no, that would change the users profile period

bad idea -- terrible concept -- rethink what you are trying to do.  DO NOT do this .

June 20, 2003 - 10am Central time zone

Bookmark | Bottom | Top

Reviewer: Vini

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

Followup June 20, 2003 - 5pm Central time zone:

you read the screen?

not sure what you mean or what environment you might be working in at all.

Restricting from local connections. March 16, 2005 - 7am Central time zone

Bookmark | Bottom | Top

Reviewer: Reddy from INDIA

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

Followup March 16, 2005 - 8am Central time zone:

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 ' April 27, 2005 - 2pm Central time zone

Bookmark | Bottom | Top

Reviewer: Cee Gee from Our State Bird is a Cardinal?

I have an issue explained here:

http://groups-beta.google.com/group/comp.databases.oracle.server/browse_frm/thread/6e506e783cce9ce4/
8c2165575c66b1f1#8c2165575c66b1f1

want to know how much you know about what happens in the trigger named ' SYS_LOGON '?

Followup April 27, 2005 - 2pm Central time zone:

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 April 27, 2005 - 3pm Central time zone

Bookmark | Bottom | Top

Reviewer: CG from Home of Hall of Famer DE Bruce Smith

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?

Followup April 27, 2005 - 4pm Central time zone:

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 April 28, 2005 - 5am Central time zone

Bookmark | Bottom | Top

Reviewer: sreenivas from INDIA

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

Followup April 28, 2005 - 8am Central time zone:

http://download-west.oracle.com/docs/cd/B10501_01/network.920/a96581/sqlnet.htm#496706

sqlnet configuration

ipaddress April 29, 2005 - 1am Central time zone

Bookmark | Bottom | Top

Reviewer: sreenivasa rao from INDIA

Thanks for the easy and simple solution.

Thin client information June 21, 2005 - 3am Central time zone

Bookmark | Bottom | Top

Reviewer: Wael from Saudi Arabia, Riyadh

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.

Followup June 21, 2005 - 4pm Central time zone:

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 January 3, 2006 - 10am Central time zone

Bookmark | Bottom | Top

Reviewer: Mark from Boston, MA

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.

Followup January 3, 2006 - 10am Central time zone:

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 January 3, 2006 - 11am Central time zone

Bookmark | Bottom | Top

Reviewer: Mark from Boston, MA

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!

Write a Review