After logon trigger - Very helpful
Getachew Geressu, October 16, 2005 - 10:16 pm UTC
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!
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$
Neil, October 17, 2005 - 5:50 am UTC
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
A reader, October 17, 2005 - 7:59 am UTC
:-)
October 17, 2005 - 8:12 am UTC
must have fixed that right as you wrote this ;) thanks
Connection benchmark
getachew geressu, October 19, 2005 - 1:19 pm UTC
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.
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
Getachew Geressu, October 22, 2005 - 3:36 pm UTC
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!
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
aman, October 23, 2005 - 7:32 am UTC
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
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
Getachew G., October 23, 2005 - 12:16 pm UTC
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?
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 ?
Kim, December 12, 2005 - 4:12 am UTC
Just a (untestet) thought:
I think You could use dblinks in a stored procedure to logon to the database...
before logon,
A reader, December 15, 2005 - 11:55 am UTC
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,
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?
A reader, December 21, 2005 - 1:11 pm UTC
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,
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
Michael, January 10, 2006 - 10:42 am UTC
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;
Creating logon trigger
Guest, November 29, 2006 - 10:49 am UTC
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
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?
Syed, December 01, 2006 - 10:40 am UTC
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
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
Syed, December 14, 2006 - 6:12 am UTC
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
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
Syed, December 18, 2006 - 7:57 am UTC
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
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
Nitin, April 24, 2007 - 5:13 pm UTC
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
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
Deba, July 17, 2007 - 8:39 am UTC
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
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
Deba, July 18, 2007 - 6:23 am UTC
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
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
Deba, July 19, 2007 - 7:11 am UTC
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
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
Amar, July 19, 2007 - 9:05 am UTC
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!
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
Deba, July 20, 2007 - 3:57 am UTC
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
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
Deba, July 23, 2007 - 3:48 am UTC
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
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
Deba, July 23, 2007 - 3:56 am UTC
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
Stuart, September 09, 2009 - 8:38 pm UTC
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;
/
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!!
Stuart, September 14, 2009 - 5:17 pm UTC
Thanks very much.
Before or After Logon
Rupali, February 25, 2011 - 1:39 pm UTC
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.
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 !
Sokrates, February 28, 2011 - 8:30 am UTC
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
P, October 13, 2011 - 2:26 pm UTC
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.
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..
Sujana, December 07, 2011 - 7:01 am UTC
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;
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
Rajeshwaran, Jeyabal, April 07, 2020 - 3:20 pm UTC
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>
April 08, 2020 - 3:48 pm UTC
Good catch, it does seem to be unnecessary now.