Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question.

Asked: June 27, 2001 - 11:15 am UTC

Last updated: January 03, 2006 - 10:08 am UTC

Version: 8.1.6.3

Viewed 10K+ times! This question is

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 Tom 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
</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:1362403157367 <code>

for proc_role -- faster implementation there.

Rating

  (13 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

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

 

Tom Kyte
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.
 

Tom Kyte
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



Tom Kyte
October 02, 2002 - 10:34 am UTC

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

read
</code> http://asktom.oracle.com/Misc/RolesAndProcedures.html <code>


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?

Tom Kyte
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




Tom Kyte
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


Tom Kyte
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

I have an issue explained here:
</code> http://groups-beta.google.com/group/comp.databases.oracle.server/browse_frm/thread/6e506e783cce9ce4/8c2165575c66b1f1#8c2165575c66b1f1 <code>

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

Tom Kyte
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? 

Tom Kyte
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


Tom Kyte
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.

Tom Kyte
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.



Tom Kyte
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!

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library