Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Getachew.

Asked: October 16, 2005 - 7:39 pm UTC

Answered by: Tom Kyte - Last updated: April 08, 2020 - 3:48 pm UTC

Category: Database - Version: 9.2.0.6

Viewed 50K+ times! This question is

You Asked

Tom,

What I am trying to accomplish is basically, to monotor any one
(mainly DBAs) who connects to the database using "system".
I know that, you have advocated to use "audit connect by system" but,
I want to have more flexibility (for example, not to audit on specific machines). I have created an after logon trigger which, first checks the machine name from a table and, if the name exists in the table, it then checks if the current user is "SYSTEM". If
both criterias are met, it inserts a recod into a table.
The script for trigger is listed below and, my questions are:

a) The trigger fires for every connection correct?
b) What would be the performance implications by doing it this way?
c) If I want to use "audit connect by system", how can I acheive this
without using $AUD table. I really want to avoid using this table.

d) Any thing I want to modify on the trigger?

Here is the after logon trigger script:

create or replace trigger trg_no_sys_logon
after logon
on database
declare
v_machine varchar2(30);
v_count number;
begin
select count(machine) into v_count
from host_machine_list
where SYS_CONTEXT('USERENV','HOST') = MACHINE;
If v_count = 0 then
for check_users in(select username
from v$session
where AUDSID = USERENV('SESSIONID')) loop
if upper(check_users.username) in ('SYSTEM')
then
insert into gdemo.dbas_aud3
(
LOGON_TIME
,TERMINAL
,SESSIONID
,INSTANCE
,ENTRYID
,ISDBA
,CURRENT_USER
,CURRENT_USERID
,SESSION_USER
,SESSION_USERID
,PROXY_USER
,PROXY_USERID
,DB_NAME
,HOST
,OS_USER
,EXTERNAL_NAME
,IP_ADDRESS
,NETWORK_PROTOCOL
,AUTHENTICATION_TYPE)
select
SYSDATE
,SYS_CONTEXT('USERENV','TERMINAL') TERMINAL
,SYS_CONTEXT('USERENV','SESSIONID') SESSIONID
,SYS_CONTEXT('USERENV','INSTANCE') INSTANCE
,SYS_CONTEXT('USERENV','ENTRYID') ENTRYID
,SYS_CONTEXT('USERENV','ISDBA') ISDBA
,SYS_CONTEXT('USERENV','CURRENT_USER') CURRENT_USER
,SYS_CONTEXT('USERENV','CURRENT_USERID') CURRENT_USERID
,SYS_CONTEXT('USERENV','SESSION_USER') SESSION_USER
,SYS_CONTEXT('USERENV','SESSION_USERID') SESSION_USERID
,SYS_CONTEXT('USERENV','PROXY_USER') PROXY_USER
,SYS_CONTEXT('USERENV','PROXY_USERID') PROXY_USERID
,SYS_CONTEXT('USERENV','DB_NAME') DB_NAME
,SYS_CONTEXT('USERENV','HOST') HOST
,SYS_CONTEXT('USERENV','OS_USER') OS_USER
,SYS_CONTEXT('USERENV','EXTERNAL_NAME') EXTERNAL_NAME
,SYS_CONTEXT('USERENV','IP_ADDRESS') IP_ADDRESS
,SYS_CONTEXT('USERENV','NETWORK_PROTOCOL') NETWORK_PROTOCOL
,SYS_CONTEXT('USERENV','AUTHENTICATION_TYPE') AUTHENTICATION_TYPE from dual;
end if;
commit;
end loop;
END IF;
exception
when others then
NULL;
end trg_no_sys_logon_3;
/

show errors;


and we said...

a) you could create it in systems schema, but of course system would see it and system could drop it.

but system can see it anyway, so anyone curious enough would be able to drop it as system....


b) performance hit upon connect, if you connect lots - big hit, connect not lots, small hit.

c) you have to use aud$ with that command, not any way around that.


d) i would look at the username first - that way we can avoid the query when not system (should be the VAST majority of the time right? not system)


don't use audsid, unsafe - can be "zero" - use (select sid from v$mystat where rownum=1) and compare that to sid.

don't use procedural code, just:

if ( user = 'SYSTEM' )
then
insert into audit_table....
select ...
from v$session
where sid = (select sid from v$mystat where rownum = 1 )
and machine in ( select machine from host_machine_list );
end if;

do all of the work in SQL!


and you rated our response

  (30 ratings)

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

Reviews

After logon trigger - Very helpful

October 16, 2005 - 10:16 pm UTC

Reviewer: Getachew Geressu from mn

a) So, who should own this trigger?
The same users I want to audit, can change the auditing functionalty because,
the ones to be audited are DBAs and, DBAs have "SYSDBA" privilege.

b) To avoid performace hit, what if I use a "before log off trigger"? At, lease the trigger won't fire for thousands of "after logon" triggers? What do you think?

c) I agreed. Thank you

d) I want to audit only when the user is "SYSTEM". Correct, the VAST majority of the time it is not "SYSTEM" that logs in. I have modified the script. It works:

create or replace trigger sys.trg_no_sys_logon_3
after logon
on database
declare
begin
if ( user = 'SYSTEM' or user = 'SYS')
then
insert into audit_table
(
INSTANCE_NAME -- I can't get this info from v$session
, USERNAME
, COMMAND
, OSUSER
, PROCESS
, MACHINE
, TERMINAL
, PROGRAM
, ACTION
, CLIENT_INFO
, LOGON_TIME
, CLIENT_IDENTIFIER
)
select
i.INSTANCE_NAME
,s.USERNAME
,s.COMMAND
,s.OSUSER
,s.PROCESS
,s.MACHINE
,s.TERMINAL
,s.PROGRAM
,s.ACTION
,s.CLIENT_INFO
,s.LOGON_TIME
,s.CLIENT_IDENTIFIER
from v$session s, v$instance i
where s.sid = (select sid from v$mystat where rownum = 1 )
and machine in (select machine from gdemo.host_machine );
end if;
commit;
exception
when others then
NULL;
end trg_no_sys_logon_3;
/
show errors;

Your thoughts are appreciated!

Tom Kyte

Followup  

October 17, 2005 - 7:17 am UTC

a) no good answer, they obviously can ALL disable/drop/modify this trigger since they have the ANY privs.

Wait -- good answer - don't let people use system! they don't need to. Don't grant people DBA, many do not need it, create roles that give people what they need. Don't grant SYSDBA to the world, give it out to only those that absolutely need it....


b) what is the difference? pay me now or pay me later - but PAY me is what you are saying?

c) see Neils comment below, you could use the OS audit trail as well, yes....

d) my thoughts:

lock SYSTEM, and use O7_DICTIONARY_ACCESSIBILITY=FALSE, so that connects by SYS are rejected (must use AS SYSDBA).

that is, don't use system, don't use sys.

Avoid use of AUD$

October 17, 2005 - 5:50 am UTC

Reviewer: Neil from UK

Tom

not sure if this is what the requestor wants, but could
they not audit to a OS file instead by setting audit_trail=OS ?

Regards

Neil

neil != niall

October 17, 2005 - 7:59 am UTC

Reviewer: A reader

:-)
Tom Kyte

Followup  

October 17, 2005 - 8:12 am UTC

must have fixed that right as you wrote this ;) thanks

Connection benchmark

October 19, 2005 - 1:19 pm UTC

Reviewer: getachew geressu

Tom,
Regarding my question above:
b) To avoid performace hit, what if I use a "before log off trigger"? At, least
the trigger won't fire for thousands of "after logon" triggers? What do you
think?

You replied:
performance hit upon connect, if you connect lots - big hit, connect not
lots, small hit.

As a follouwp to this, how can I test a connection scenario? For example, simulating a 1000 connections to the database with an interval of 2-3 seconds between each connection?

Thank you.

Tom Kyte

Followup  

October 19, 2005 - 3:28 pm UTC

No, I said

"pay me know or pay me later" in response to your b)


specifically I said:

b) what is the difference? pay me now or pay me later - but PAY me is what you
are saying?


what difference would it make? I didn't get it.

After logon trigger connection test

October 22, 2005 - 3:36 pm UTC

Reviewer: Getachew Geressu

I absolutely agree with your point. I have to pay one way or the other. But, my question was if tere was a way to simulate connection time performance by setting up the trigger (after logon or before logoff) and see it's effect by connecting to the database simultaneously (with an interval of 2 to 3 seconds) between connections. That's all. I didn't want open 100 SQL/Plus windows to connect.
Sorry if I didn't put my question clearly.

Thank You!


Tom Kyte

Followup  

October 23, 2005 - 4:42 am UTC

...and see it's effect by
connecting to the database simultaneously...


did not understand that bit? I'm not sure what you are trying to test here?

none

October 23, 2005 - 7:32 am UTC

Reviewer: aman from India

I think sir the original poster wants to test the trigger but he doesnt know how he can simulate 1000 connections connecting with an interval difference of 2/3 seconds.He wants to put 1000 connection load on the database.
regards
aman

Tom Kyte

Followup  

October 23, 2005 - 1:41 pm UTC

the only way to simulate that would be to do something like

10 users (at once)
20 users (at once)
50 users (at once)
100 users (at once)
200 users (at once)

and that might give sufficient data points to start extrapolating - but extrapolation is dangerous.

the only way to simulate concurrency is - concurrency.

After logon trigger connection test

October 23, 2005 - 12:16 pm UTC

Reviewer: Getachew G. from MN

What I wanted to test are two scenarios:

1) No after logon trigger on the database.
Connect 1000 users to the database with an interval of
2 seconds between each connection.

2) Set up an after logon trigger on the database.
Connect 1000 users to the database with an interval of
2 seconds between each connection.

3) Compare the effect of having an after logon tigger in the database ie step1 vs step2.

My question is:
Can I accomplish the above tests by writing a stored procedure which loops 1000 times, sleeps 2 seconds between each loop and, executes a connect statement?

Tom Kyte

Followup  

October 23, 2005 - 1:46 pm UTC

answer to question:

no. stored procedures cannot execute a "connect statement"

I guess You could use a dblink ?

December 12, 2005 - 4:12 am UTC

Reviewer: Kim from Denmark

Just a (untestet) thought:
I think You could use dblinks in a stored procedure to logon to the database...

before logon,

December 15, 2005 - 11:55 am UTC

Reviewer: A reader

I want to caputure the users (like osuser and the schema to which they are trying to connect) who had unsuccessful connection to the database.

Is there a way to do that?

thanks,

Tom Kyte

Followup  

December 15, 2005 - 12:22 pm UTC

you can use AUDIT CONNECT for that, yes.

tkyte (osuser) tried to login with boo/hoo, it failed with ora-1017


ops$tkyte@ORA10GR2> @printtbl8 'select * from dba_audit_trail';
OS_USERNAME                   : "tkyte"
USERNAME                      : "BOO"
USERHOST                      : "dellpe"
TERMINAL                      : "pts/2"
TIMESTAMP                     : "15-dec-2005 13:17:13"
OWNER                         : ""
OBJ_NAME                      : ""
ACTION                        : "100"
ACTION_NAME                   : "LOGON"
NEW_OWNER                     : ""
NEW_NAME                      : ""
OBJ_PRIVILEGE                 : ""
SYS_PRIVILEGE                 : ""
ADMIN_OPTION                  : ""
GRANTEE                       : ""
AUDIT_OPTION                  : ""
SES_ACTIONS                   : ""
LOGOFF_TIME                   : ""
LOGOFF_LREAD                  : ""
LOGOFF_PREAD                  : ""
LOGOFF_LWRITE                 : ""
LOGOFF_DLOCK                  : ""
COMMENT_TEXT                  : "Authenticated by: OS"
SESSIONID                     : "303"
ENTRYID                       : "1"
STATEMENTID                   : "1"
RETURNCODE                    : "1017"
PRIV_USED                     : ""
CLIENT_ID                     : ""
ECONTEXT_ID                   : ""
SESSION_CPU                   : ""
EXTENDED_TIMESTAMP            : "15-DEC-05 01.17.13.104187 PM -05:00"
PROXY_SESSIONID               : ""
GLOBAL_UID                    : ""
INSTANCE_NUMBER               : "0"
OS_PROCESS                    : "10901"
TRANSACTIONID                 : ""
SCN                           : ""
SQL_BIND                      : ""
SQL_TEXT                      : ""
 

how do you simulate?

December 21, 2005 - 1:11 pm UTC

Reviewer: A reader

Tom,

In this thread you mentioned to simulate 10 users at a time, 20, 30... and so on.

But how do you simulate this? In other words, how do you make 500 users (for example) connecting to the database within a window time of 1 hour?

Thanks,

Tom Kyte

Followup  

December 21, 2005 - 7:53 pm UTC

there are many tools that do this - mercury interactive makes one of the better known ones...

search for

stress tools oracle

on google.

After Logon Trigger and Cursor Sharing

January 10, 2006 - 10:42 am UTC

Reviewer: Michael from Ontario

What would be the best way to alter session set cursor_charing = "force" with an after login trigger. There are a couple of third party applications which have compiled dlls in which there is no access to the source which they have used literals in practically every sql query. The connection is two ODBC with a username of 'XSYS' and 'XDA'. One for Security and the other for schema access of underlying tables. The program which runs this third party application is also known. I am thinking this way in the body of the trigger.

begin
if ora_login_user in ('XSYS','XDA') then
execute immediate 'alter session set cursor_sharing = ''force''';
end if;
end;


Tom Kyte

Followup  

January 10, 2006 - 11:03 am UTC

that looks fine, just make sure the OWNER of the trigger has been granted ALTER SESSION *directly* - not via role.

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

Creating logon trigger

November 29, 2006 - 10:49 am UTC

Reviewer: Guest from India

Hi Tom,

My client requirement is, if any user login to schema A, then his details should be captured into a table in schema B.

So i created a trigger as

CREATE OR REPLACE TRIGGER logon_audit
AFTER LOGON
ON A.SCHEMA
BEGIN

insert into user_log values(
user,
sys_context('USERENV','SESSIONID'),
sys_context('USERENV','HOST'),
sys_context('USERENV','TERMINAL'),
sysdate
);

END;
/

when i create this trigger in B, it gives me an error "table does not exist". If i create this trigger in schema A it works. But i want to create trigger in B, table should exist in B. When ever a user login in A it should insert the values in table B.

Please tell me how can i achieve this

Tom Kyte

Followup  

November 30, 2006 - 9:18 am UTC

ops$tkyte%ORA10GR2> create user b identified by b default tablespace users quota unlimited on users;

User created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> grant create session to a;

Grant succeeded.

ops$tkyte%ORA10GR2> grant
  2    create session
  3  , create table
  4  , create any trigger
  5  , administer database trigger to b;

Grant succeeded.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> connect b/b
Connected.
b%ORA10GR2> create table user_log( msg varchar2(30) );

Table created.

b%ORA10GR2>
b%ORA10GR2> create or replace trigger logon_audit after logon on a.schema
  2  begin
  3     insert into b.user_log values ( 'user ' || user || ' logged' );
  4  end;
  5  /

Trigger created.

b%ORA10GR2>
b%ORA10GR2> connect a/a
Connected.
a%ORA10GR2> connect b/b
Connected.
b%ORA10GR2> select * from user_log;

MSG
------------------------------
user A logged


<b>be wary of those "ANY" and "ADMINISTER" privileges - probably you want to lock the B account after this</b> 

How to capture the program detail with this trigger?

December 01, 2006 - 10:40 am UTC

Reviewer: Syed from India

Hi Tom,

It is really helpful to know Thanks

In sys_context, is there any option to capture the program details. From the userenv terminal, user, osuser details can be obtained but i want to capture through which software he has logged in?

In v$session, the column program contains this detail but i am not able to use v$session inside the trigger body. it throws me an error table does not exist.

Is there any way to capture this information.

Thanks,
Syed

Tom Kyte

Followup  

December 01, 2006 - 12:39 pm UTC

the "software" can make itself be anything it wants to say it is - "software" in general isn't "signed", no signature. You can look in v$session, there is a program column there - but easy enough to fake it out.

you would get granted direct access to V_$SESSION - then your trigger can access it.

DDL Capture trigger

December 14, 2006 - 6:12 am UTC

Reviewer: Syed from India

Hi Tom,

I have created the DDL trigger as given below
Table in schema A
CREATE TABLE DDL_LOG
(
OPERATION VARCHAR2(60) ,
OBJ_OWNER VARCHAR2(60) ,
OBJECT_NAME VARCHAR2(60) ,
OBJECT_TYPE VARCHAR2(40) ,
ATTEMPT_BY VARCHAR2(60) ,
ATTEMPT_DT DATE ,
OS_USER VARCHAR2(30) ,
USER_HOST VARCHAR2(80) ,
TERMINAL VARCHAR2(80)
)
Trigger in Schema B and C
CREATE OR REPLACE TRIGGER DDL_TRIGGER
BEFORE ALTER OR AUDIT OR CREATE OR DROP OR GRANT OR RENAME OR REVOKE OR DDL
ON SCHEMA
BEGIN
INSERT INTO A.ddl_log SELECT ora_sysevent, ora_dict_obj_owner, ora_dict_obj_name, ora_dict_obj_type,USER,SYSDATE, SYS_CONTEXT('USERENV', 'OS_USER'), SYS_CONTEXT('USERENV', 'HOST'), SYS_CONTEXT('USERENV', 'TERMINAL')
FROM dual;
raise_application_error(-20000, 'You are not authorized to execute this command. Please contact your DBA');
END;

This code works fine in B schema, but it is not capturing the information in C schema.

example: i executed "alter trigger ddl_trigger disable;" in schema B and C.
In DDL_LOG table all the information is captured for B schema where as for C schema data is not inserted.

When i executed the insert statement seperately data gets inserted only when the session is closed (i.e no auto commit)and only Null values inserted for ora_sysevent, ora_dict_obj_owner, ora_dict_obj_name columns.

Is there any schema level parameter setting makes this difference? Both the schema exists in same database.

Please advice

Tom Kyte

Followup  

December 15, 2006 - 8:16 am UTC

search this site for

pragma autonomous_transaction


you need to "commit the insert" but fail the parent transaction.

Autonomous Transaction

December 18, 2006 - 7:57 am UTC

Reviewer: Syed from Indai

Hi Tom,

I modified the trigger with autonomous transaction as given below

CREATE OR REPLACE TRIGGER DDL_LOG_TRIGGER
BEFORE ALTER OR AUDIT OR CREATE OR DROP OR GRANT OR RENAME OR REVOKE
ON SCHEMA
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;

BEGIN INSERT INTO drsdba.ddl_log
SELECT ora_sysevent, ora_dict_obj_owner,
ora_dict_obj_name, ora_dict_obj_type,USER,SYSDATE, SYS_CONTEXT('USERENV', 'OS_USER'),
SYS_CONTEXT('USERENV', 'HOST'),
SYS_CONTEXT('USERENV', 'TERMINAL')
FROM dual;
commit;
raise_application_error(-20000, 'You are not authorized to execute this command. Please contact your DBA');
END;


When i execute a create command, the information is inserted but when i execute 'alter trigger ddl_log_trigger disable' the alter information is not inserted.

Please advice


Tom Kyte

Followup  

December 18, 2006 - 8:51 am UTC

umm, no kidding - what did you expect exactly???????


Guess the advice would be "don't type that disable command"?

After logon trigger and determining client_info

April 24, 2007 - 5:13 pm UTC

Reviewer: Nitin from Atlanta

Is it possible to capture client_info using the After Logon Trigger?

create table test_logon
(username varchar2(30), appid varchar2(30), audsid number)
/

create or replace trigger
logon_trigger
AFTER LOGON ON DATABASE
DECLARE
VAR_CLIENT_INFO VARCHAR2(64);
BEGIN

select client_info
into VAR_CLIENT_INFO
from v$session
where audsid = SYS_CONTEXT('USERENV','SESSIONID');

insert into test_logon values(
user,
VAR_CLIENT_INFO,
SYS_CONTEXT('USERENV','SESSIONID')
);
end;


The above trigger gives a blank output for client_info. It is my understanding that client_info is set by dbms_application_info after the trigger is fired. Please let me know what will be the best approach to determine the client_info before that session can execute any other SQL.

Thanks
Tom Kyte

Followup  

April 24, 2007 - 5:40 pm UTC

client_info has no value until you set it, this trigger fires after you logon, but before your application has a chance to do anything. I don't know what you would expect to find in there at this point?

DBMS_APPLICATION_INFO NOT WORKING IN AFTER LOGON TRIGGER

July 17, 2007 - 8:39 am UTC

Reviewer: Deba from India

Hi,

I am trying to set action column in v$session through "after logon" trigger. But action column is not getting set . I have attached the code. Is there any restriction in setting action column of v$session table through "After logon" trigger ?

create or replace trigger after_logon_ip_trigger
AFTER LOGON ON DATABASE
BEGIN
If user = 'ST_NETWORK' Then
DBMS_APPLICATION_INFO.set_module(module_name =>'d',action_name =>'y');
DBMS_APPLICATION_INFO.set_action(action_name => 'z');
end if;
end;
/

Thanks
Deba
Tom Kyte

Followup  

July 17, 2007 - 1:09 pm UTC

it works - you might not see it in sqlplus as sqlplus tends to set them ITSELF and you see the last value in there.


ops$tkyte%ORA10GR2> create or replace trigger after_logon_ip_trigger
  2  AFTER LOGON ON DATABASE
  3  BEGIN
  4  DBMS_APPLICATION_INFO.set_module(module_name =>'this is my module',action_name =>'this is my action');
  5  DBMS_APPLICATION_INFO.set_client_info('this is my client info');
  6  end;
  7  /

Trigger created.

ops$tkyte%ORA10GR2> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options


[tkyte@tkyte-pc j]$ cat test.java
import java.sql.*;


class test
{

public static void main(String args[])throws Exception
{
    DriverManager.registerDriver
       (new oracle.jdbc.driver.OracleDriver());

        System.out.println( "------------------------" );
    Connection conn=DriverManager.getConnection
        ("jdbc:oracle:thin:@tkyte-pc.us.oracle.com:1521:ora10gr2"
        ,"ops$tkyte","fadf");

        System.out.println( "------------------------" );
    PreparedStatement pstat =
        conn.prepareStatement
    ("select client_info, action, module from v$session where username = user" );

        System.out.println( "------------------------" );
        ResultSet rset = pstat.executeQuery();
        while( rset.next() )
        {
                System.out.println( "ci = " + rset.getString(1) );
                System.out.println( "action = " + rset.getString(2) );
                System.out.println( "module = " + rset.getString(3) );
        }
        rset.close();

}
}


[tkyte@tkyte-pc j]$ javac test.java
[tkyte@tkyte-pc j]$ java test
------------------------
------------------------
------------------------
ci = this is my client info
action = this is my action
module = this is my module
[tkyte@tkyte-pc j]$

dbms_application_info not working through "after logon" trigger

July 18, 2007 - 6:23 am UTC

Reviewer: Deba from India

Hi Tom,

Thanks for the reply.

See I am using the following code through "after logon" trigger. So it means that action column ( since you are sying it is set through "after logon" trigger ) will be set for ST_NETWORK user.As a result of this , if any sql is issued from this session then I will be able to see that value of action column of v$session in action column of v$sqlarea against that sql hash value. But this is not happening. Could you please tell me why ?

create or replace trigger after_logon_ip_trigger
AFTER LOGON ON DATABASE
BEGIN
If user = 'ST_NETWORK' Then
DBMS_APPLICATION_INFO.set_module(module_name =>'d',action_name =>'y');
DBMS_APPLICATION_INFO.set_action(action_name => ora_client_ip_address);
--insert into ymdbaadm.dasx values('das'||ora_client_ip_address);
dbms_application_info.set_client_info('WDA001');
end if;
end;
/

Thanks
Deba
Tom Kyte

Followup  

July 18, 2007 - 10:44 am UTC

sigh

are you using sqlplus to do your testing

because, if you are, sqlplus SETS THESE VALUES ITSELF. as could any application.

this is a bad idea you are doing here, dbms_application_info is easily set by any well written application.

dbms_application_info not working in "after logon " trigger

July 19, 2007 - 7:11 am UTC

Reviewer: Deba from India

Hi Tom,

Thanks fro the reply.

You comment "are you using sqlplus to do your testing because, if you are, sqlplus SETS THESE VALUES ITSELF. as could any application. " --- it is not very clear. I am creating the trigger through sqlplus. That's all.

My objective - I want to track the ip address of each session of user ST_NETWORK who are issuing different ad-hoc sql. Basically my plan is to set the action column of v$session for each session of ST_NETWORK user with IP address so that later, I can track the ip address from action column of v$sqlarea against the hash_value of the sql issued from ST_NETWORK.

Please let me know how to do this.

We use TOAD, Sql developer, sqlplus for issuing such ad-hoc sql from ST_NETWORK user.

Thanks
Deba
Tom Kyte

Followup  

July 19, 2007 - 11:07 am UTC

when you query v$session, what are you using - your program, or sqlplus.


toad, sqldevelop, sqlplus - they ALL set these values. You cannot necessarily stop them from doing that.

and - given that shared sql is - well - shared sql - the ACTION column in v$sql is set by the FIRST SESSION to parse the sql. It would not show you "current" information - it would be set once (days ago, weeks ago perhaps) and remain immutable.


ops$tkyte%ORA10GR2> alter system flush shared_pool;

System altered.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> exec dbms_application_info.set_action( 'foo' );

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> select /* look for me */ * from dual;

D
-
X

ops$tkyte%ORA10GR2> select sql_text, action from v$sql where sql_text like 'select /* look for me */%';

SQL_TEXT                                 ACTION
---------------------------------------- --------
select /* look for me */ * from dual     foo

ops$tkyte%ORA10GR2> exec dbms_application_info.set_action( 'bar' );

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> select /* look for me */ * from dual;

D
-
X

ops$tkyte%ORA10GR2> select sql_text, action from v$sql where sql_text like 'select /* look for me */%';

SQL_TEXT                                 ACTION
---------------------------------------- --------
select /* look for me */ * from dual     foo



and, and of course, dozens of sessions can be running the same sql at the same time.

setting sqlprompt after logon

July 19, 2007 - 9:05 am UTC

Reviewer: Amar from Bangalore

Hi Tom
Articles out here are worth reading. While reading your book 'Oracle Database Architecture-9i & 10g prog. techniques', while setting the environ, there's a good example to set the sqlprompt with the user@instance_name.
Now, when I want to do it at the logon to an instance on windows, I add the above code to glogin.sql & it works fine till the user feeds in username & password. Also in case we login with /nolog option, it'll show idle (as default).
However, when he changes user can we create a trigger to change the sqlprompt accordingly?
I've tried a couple of ways in the after 'logon on database' trigger:
1. Setting sqlprompt in trigger- throws error, says unrecognized command
2. Executing a dynamic sql to set the prompt - trigger is created but not useful, I mean it does not update the sqlprompt
3. Calling the glogin.sql in trigger - no go, throws an error

the trigger definition is as

Create or replace trigger logon_trig after logon on database
declare
g_name varchar2(20);
begin
-- column g_name new_value gname
select user||'@'|| (select substr(global_name, 1,dot-1)
from (select global_name, instr(global_name, '.') dot from global_name)) into g_name
from dual;
execute immediate 'set sqlprompt '|| ':g_name>' using g_name;
end;

Would appreciate your comments & solution to solve this!
Tom Kyte

Followup  

July 19, 2007 - 11:09 am UTC

you cannot use a trigger to make a client application "do something"

sqlplus is just a program, you could write your own sqlplus. Just as a database trigger cannot make your program do something, you cannot make sqlplus do something via a trigger.


"set" is a sqlplus command - sqlplus does it, it is NOT sql at all.

dbms_application_info not working through "after logon" trigger

July 20, 2007 - 3:57 am UTC

Reviewer: Deba from India

Hi Tom,

Thanks for the reply.

Above,your comment - "given that shared sql is - well - shared sql - the ACTION column in v$sql is set by the FIRST SESSION to parse the sql. It would not show you "current" information - it would be set once (days ago, weeks ago perhaps) and remain immutable. " - This is ok for my case.

Your comment - "when you query v$session, what are you using - your program, or sqlplus. " -- I need to use "after logon" trigger and to create that I have to use either SQLPLUS or TOAD or SQL Developer. After this some people will be logging into ST_NETWORK user through either SQLPLUS or TOAD or SQL Developer. For me , tracking sqls , I will be logging into DEBA user. Now I can not restrain them from using SQLPLUS or TOAD or SQL Developer while ther are logging into ST_NETWORK user. But for me logging into DEBA user , I can use sqlplus ( since I am not logging into ST_NETWORK user ). Now acccording to You,
there is problem while logging into ST_NETWORK user using either SQLPLUS or TOAD or SQL Developer. Am I right ? If this is so, then how can I achieve this objective ?

Thanks
Deba
Tom Kyte

Followup  

July 20, 2007 - 8:30 am UTC

ok, like i said, those tools - they SET THE ACTION column - ok - it is part of their behaviour to set that value. They set it AFTER the logon happened, they set it over your value - so it is not useful for you to continue with this approach.

Also, as stated, the action column would be static, set by whomever did it first, it would not work.

use find grained auditing to capture the SQL of these users if that is the goal perhaps.

how about you state, as simply as possible, what your end goal is (not your approach to achieving some goal - but the GOAL itself) and we can suggest a viable approach.

dbms_application_info not working in "after logon" trigger

July 23, 2007 - 3:48 am UTC

Reviewer: Deba from India

Hi Tom,

Thanks for the reply. My objective is track all sqls issued from ST_NETWORK user along with IP address to track also the machine. This should be populated in such a way ( prfereably in any V$ table ) so that those informations get accumulated in that v$ table and later on that v$ table can be used.

Thanks
Deba
Tom Kyte

Followup  

July 23, 2007 - 8:40 am UTC

ok, i answered that above...

sounds like you want fine grained auditing

dbms_application_info not working in "after logon" trigger

July 23, 2007 - 3:56 am UTC

Reviewer: Deba from India

Hi Tom,

I addition to above, the table may be any table other than v$ table but that table should contain all sql hash values, ip adress etc.

Thanks
Deba

Password command gives error

September 09, 2009 - 8:38 pm UTC

Reviewer: Stuart from Auckland, New Zealand

Hi Tom,

I'm trying to figure out why the 'password' command gives me the error when my database trigger is enabled (am on 10.2.0.4):

TEST: SURE006 > password
Changing password for SURE006
Old password:
New password:
Retype new password:
ERROR:
ORA-00604: error occurred at recursive SQL level 1
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 11

The trigger code is below. Out of interest, the 'alter user xxx identified by yyy' command works fine... just the 'password' command errors.

I put the ora_sysevent to filter out 'ALTER USER', but it looks like the 'password' command doesn't use that, as I still get the same error. Do you know what the equivalent sysevent for 'password' is so I can get around this error?

create or replace trigger my_audit_ddl_trg after ddl on database
declare
sql_text ora_name_list_t;
stmt VARCHAR2(4000) := '';
n number;
begin
if (ora_sysevent = 'ALTER USER')
then
null;
else
n:=ora_sql_txt(sql_text);
for i in 1..n
loop
stmt:=substr(stmt||sql_text(i),1,4000);
end loop;
insert into my_audit_ddl(d, osuser,current_user,host,terminal,owner,type,name,sysevent,sql_txt)
values(
sysdate,
sys_context('USERENV','OS_USER') ,
sys_context('USERENV','CURRENT_USER') ,
sys_context('USERENV','HOST') ,
sys_context('USERENV','TERMINAL') ,
ora_dict_obj_owner,
ora_dict_obj_type,
ora_dict_obj_name,
ora_sysevent,
stmt
);
end if;
end;
/

Tom Kyte

Followup  

September 14, 2009 - 10:25 am UTC

I'll bet your line "11" is "for i in 1 .. n"

and N is null

and you didn't check.


ops$tkyte%ORA10GR2> create table my_audit_ddl
  2  as
  3  select
  4        sysdate d,
  5        sys_context('USERENV','OS_USER') osuser,
  6        sys_context('USERENV','CURRENT_USER') current_user,
  7        sys_context('USERENV','HOST') host,
  8        sys_context('USERENV','TERMINAL')terminal ,
  9        ora_dict_obj_owner owner,
 10        ora_dict_obj_type type,
 11        ora_dict_obj_name name,
 12        ora_sysevent sysevent,
 13        rpad('*',4000,'*') sql_txt
 14   from dual where 1=0;

Table created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create or replace trigger my_audit_ddl_trg after ddl on database
  2  declare
  3    sql_text ora_name_list_t;
  4    stmt VARCHAR2(4000) := '';
  5    n number;
  6  begin
  7    if (ora_sysevent = 'ALTER USER')
  8    then
  9      null;
 10    else
 11      n:=ora_sql_txt(sql_text);
 12          if ( n is null )
 13          then
 14                  stmt := 'null statement';
 15          else
 16          for i in 1..n
 17          loop
 18            stmt:=substr(stmt||sql_text(i),1,4000);
 19          end loop;
 20          end if;
 21      insert into my_audit_ddl(d, osuser,current_user,host,terminal,owner,type,name,sysevent,sql_txt)
 22      values(
 23        sysdate,
 24        sys_context('USERENV','OS_USER') ,
 25        sys_context('USERENV','CURRENT_USER') ,
 26        sys_context('USERENV','HOST') ,
 27        sys_context('USERENV','TERMINAL') ,
 28        ora_dict_obj_owner,
 29        ora_dict_obj_type,
 30        ora_dict_obj_name,
 31        ora_sysevent,
 32        stmt
 33      );
 34    end if;
 35  end;
 36  /

Trigger created.

ops$tkyte%ORA10GR2> show errors
No errors.
ops$tkyte%ORA10GR2> connect scott
Enter password: *****
Connected.
scott%ORA10GR2> password
Changing password for SCOTT
Old password: *****
New password: *****
Retype new password: *****
Password changed
scott%ORA10GR2> connect /
Connected.
ops$tkyte%ORA10GR2> exec print_table( 'select * from my_audit_ddl' );
.D                            : 14-sep-2009 09:59:26
.OSUSER                       : tkyte
.CURRENT_USER                 : OPS$TKYTE
.HOST                         : dellpe
.TERMINAL                     : pts/11
.OWNER                        :
.TYPE                         : USER
.NAME                         : SCOTT
.SYSEVENT                     : ALTER
.SQL_TXT                      : null statement
-----------------

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> drop trigger my_audit_ddl_trg;

Trigger dropped.

Thank you!!

September 14, 2009 - 5:17 pm UTC

Reviewer: Stuart from Auckland, New Zealand

Thanks very much.

Before or After Logon

February 25, 2011 - 1:39 pm UTC

Reviewer: Rupali from USA

Hello Tom,

In my application, the requirement is to restrict users to login only via specific application. I mean users should be allowed to access reporting schema only via Cognos tool and login from SQL*PLUS or any other applications should be restricted.

Questions are
1. which type of trigger we should be using for this. Before logon or After logon.
2. Upon setting the trigger, if a particular user does multiple tries to login via SQL*PLUS using wrong passwords will the account get lock-out. If Yes, is there any way of avoiding it.

Basically how the logon trigger works internally. Does it qualifies the credentials first and then fire. Or fire it based on the user name used in the login.

Sorry if the question is not very clear.
Tom Kyte

Followup  

February 28, 2011 - 7:45 am UTC

1)

before logon on schema
*
ERROR at line 2:
ORA-30508: client logon triggers cannot have BEFORE type


chicken and egg problem here - you cannot do anything "before" you are logged on.


2) first - remember, this will only work for non-DBA accounts. Second, remember also it is rather easy to bypass this - to 'spoof' a program - it is not foolproof.

Third and perhaps most important: this is a silly idea. It should not matter at all what application someone uses - they only have privileges to do what they are able to do and no more. Why would it matter if they used Cognos to log in or sql*plus - it would only matter if they were over privileged and you were afraid of them typing something into sql*plus. If that is the case - the fix is to revoke privileges. So, I'm of the opinion you are doing this entirely wrong.


It will not lock the account, you have already logged in - it'll be AFTER the logon.

great exception !

February 28, 2011 - 8:30 am UTC

Reviewer: Sokrates

isn't it ?

a. if client logon triggers can *not* have BEFORE type, which kind of logon triggers *can* have BEFORE type ?

b. "ORA-30509: client logoff triggers cannot have AFTER type" - if client logoff triggers can *not* have AFTER type, which kind of logoff triggers *can* have AFTER type ?

c. "ORA-30500: database open triggers and server error triggers cannot have BEFORE type" - wouldn't it be nice they could ?

d. "ORA-30501: instance shutdown triggers cannot have AFTER type" - ditto

:-)

Eliminate users from AFTER LOGON ON DATABASE trigger

October 13, 2011 - 2:26 pm UTC

Reviewer: P

CREATE OR REPLACE TRIGGER PS_MB_SET_DEFLT_SESSION
AFTER LOGON ON DATABASE WHEN (USER NOT IN ('SYS','SYSTEM','SYSMAN','DBSNMP','DBADMIN','MGMT_VIEW'...)
BEGIN
IF USER = x.USERNAME
THEN
execute immediate 'alter session set current_schema=ABC';
END IF;
END;

In the above trigger is there a better way to eliminate some users with out actually hardcoding the user names.
Tom Kyte

Followup  

October 14, 2011 - 11:06 pm UTC

I don't know - but only because I don't actually know your requirements.

You could of course create a lookup table and do a lookup

begin
  for x in ( select * from dual 
              where user not in (select username from some_exclusion_list) loop
     execute immediate ....


if you want the exclusion to happening in the trigger header - so that the body is only executed for some users, you have to use the list approach.

How to notify error msg using AFTER LOGON ON DATABASE..

December 07, 2011 - 7:01 am UTC

Reviewer: Sujana from India

Hi Tom,
I have a reqmt where in user shouldn't be allowed to connect to more than 3 sessions on a DB. Have used RAISE_APPLICATION_ERROR and re-raised it in EXCEPTION block to notify this exception, but it doesn't work. Any idea how to notify the error message in such cases?

Thanks,
Sujana

Code below :------

CREATE OR REPLACE TRIGGER SESSION_CNT_CHK_TRIGGER
AFTER LOGON ON DATABASE
DECLARE
v_count number(2);
v_user varchar2(100);

BEGIN

select osuser, count(*)
into v_user, v_count
from v$session
where schemaname NOT IN ('SYS', 'SYSTEM', 'DBSNMP', 'TSMSYS', 'OUTLN')
and osuser IS NOT NULL
and osuser NOT IN ('wlssqe', 'wlssqe03')
and osuser = #curr_log_username#
group by osuser;

if (v_count > 3)
then
raise_application_error(-20001, 'You have exceeded max sessions permitted i.e 3');
end if;
EXCEPTION
when others then
raise;
END;

Tom Kyte

Followup  

December 07, 2011 - 1:27 pm UTC

erase all code, use a profile

http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_6010.htm#SQLRF01310

EXCEPTION
  when others then
      raise;
  END;


and stop doing that, that is just a really bad idea. All it can possibly do is make debuging stuff HARDER (it moves the line number of the error)

just say no to "when others" without something useful being done in there (at least it does have a raise!)


After Logon Trigger and Cursor Sharing

April 07, 2020 - 3:20 pm UTC

Reviewer: Rajeshwaran, Jeyabal

in this same page - few followups above at this link
https://asktom.oracle.com/pls/apex/asktom.search?tag=after-logon-trigger-200510#54701922785980

Tom said

... just make sure the OWNER of the trigger has been granted ALTER SESSION *directly* - not via role....


do we need ALTER SESSION privilege really? Here is what i did from 12.2 - without alter session privilege, able to execute the trigger successfully, kindly advice.

demo@PDB1> grant create session,create trigger,create table,select_catalog_role to a
  2  identified by a ;

Grant succeeded.

demo@PDB1> alter user a quota unlimited on users;

User altered.

demo@PDB1> conn a/a@pdb1
Connected.
a@PDB1>
a@PDB1> create table t(x number);

Table created.

a@PDB1> create or replace trigger t_trig
  2  after insert on t
  3  for each row
  4  begin
  5     execute immediate q'# alter session set cursor_sharing=force #';
  6  end;
  7  /

Trigger created.

a@PDB1> show parameter cursor_sharing

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cursor_sharing                       string      EXACT
a@PDB1> insert into t(x) values(1);

1 row created.

a@PDB1> show parameter cursor_sharing

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cursor_sharing                       string      FORCE
a@PDB1> select * from session_privs;

PRIVILEGE
----------------------------------------
CREATE SESSION
CREATE TABLE
CREATE TRIGGER

a@PDB1> select * from session_roles;

ROLE
--------------------------------------------------------------------------------------------------
SELECT_CATALOG_ROLE
HS_ADMIN_SELECT_ROLE

a@PDB1>

Chris Saxon

Followup  

April 08, 2020 - 3:48 pm UTC

Good catch, it does seem to be unnecessary now.