Nice approach!!
Sanjay Raj, November 01, 2001 - 11:04 am UTC
Tom,
Can the YOUR_DBA have all the privileges of the DBA role?
Just my 2 cents. I think if I know the IP addresses that I want to restrict then I can use the following to restrict logon
Create a file named protocol.ora as follows.
tcp.excluded_nodes=(XXX.XY1.XY2.Y, XXX.XY2.XY3.Z)
tcp.validnode_checking=yes
Suppose I want to Disallow connection to a user with an IP addresse of 192.168.11.20 or 192.168.10.12 then I can simply configure the protocol.ora file as follows:
tcp.excluded_nodes=(192.168.11.20, 192.168.10.12)
tcp.validnode_checking=yes
The destination of this file would be same as that of sqlnet.ora and tnsnames.ora. The listener will required to be stopped and restarted.
There are certain bugs in this method, so be sure to check with Oracle support or Metalink for the specific platform and version.
For example, On Unix and Oracle version 8.1.x, I had to rename protocol.ora to .protocol.ora in the network/admin directory.
Thanks for all yr help
November 01, 2001 - 3:49 pm UTC
Yes, your_dba can be just like a dba...
Risky
A reader, November 02, 2001 - 8:03 am UTC
Let's hope nothing goes wrong between 9 and 24 ;-)
Tom Has There Been a Change
A reader, December 12, 2001 - 11:28 pm UTC
Hi Tom,
i am using
Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production
Now the After Logon trigger Fires for even users with DBA Roles Enabled.
What is the change ... The same trigger did not fire for me in 8.1.6
Comments pls....
Regards,
Ganesh R
December 13, 2001 - 8:53 am UTC
The triggers FIRE (always did) for users with DBA roles.
The triggers do not fire for INTERNAL or SYSDBA/SYSOPER
The logon triggers (816 and up) do not FAIL for DBA's -- they will not prevent a DBA from logging on but they EXECUTE.
tkyte@TKYTE816> drop table t;
Table dropped.
tkyte@TKYTE816> create table t ( msg varchar2(50) );
Table created.
tkyte@TKYTE816>
tkyte@TKYTE816> create or replace trigger logon_trigger
2 after logon on database
3 declare
4 pragma autonomous_transaction;
5 begin
6 insert into t values ( 'logon by ' || user || ' at ' ||
7 to_char(sysdate,'dd-mon-yyyy hh24:mi:ss' ) );
8 commit;
9 end;
10 /
Trigger created.
tkyte@TKYTE816> connect tkyte/tkyte
Connected.
tkyte@TKYTE816> select * from t;
MSG
--------------------------------------------------
logon by TKYTE at 13-dec-2001 08:55:37
tkyte@TKYTE816> select * from session_roles;
ROLE
------------------------------
PLUSTRACE
CONNECT
DBA
SELECT_CATALOG_ROLE
HS_ADMIN_ROLE
EXECUTE_CATALOG_ROLE
DELETE_CATALOG_ROLE
EXP_FULL_DATABASE
IMP_FULL_DATABASE
JAVA_ADMIN
10 rows selected.
tkyte@TKYTE816> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle8i Enterprise Edition Release 8.1.6.0.0 - Production
PL/SQL Release 8.1.6.0.0 - Production
CORE 8.1.6.0.0 Production
TNS for 32-bit Windows: Version 8.1.6.0.0 - Production
NLSRTL Version 3.4.1.0.0 - Production
What about this.
A reader, December 14, 2001 - 3:06 am UTC
Hi,
<quote>
This trigger works for every body except the ones with dba role.
And we Said...
This is the correct behavior and is designed to make it so your database doesn't
get into a state whereby NO ONE can log in (815 can get to that state). It is a
feature.
</quote>
U Had said that it is the correct behaviour and do not use the DBA Role but use a Different Role.
Your comments Pls.
Regards,
Ganesh R
December 14, 2001 - 7:22 am UTC
The trigger FIRES. The trigger did not have the desired effect in their environment. The trigger did not prevent DBA's from logging on (people with the DBA role). The trigger executed, the error however does NOT prevent (by design) DBA's from logging on.
In order to NOT have this be the case (to be allowed to prevent DBA's from logging on) my recomendation was to *not use the DBA ROLE*. It gives way too much power anyway. A secure system would not use connect, resource, dba -- they would make their own specialized roles and would have many DBA roles (jr_dba, sr_dba, nightime_dba, etc). It is only when you use a role named DBA that this ignoring of the error takes place.
ora-0604 when logon trigger fires
A reader, January 22, 2002 - 3:17 pm UTC
Hi
we have a logon trigger to restrict number of session with TOAD, however this trigger does not work fully.
We always get these errors
ORA-00604: error occurred at recursive SQL level 1
ORA-20002: 'Maximum connection exceeded'
ORA-06512: at line 19
the code is
create or replace trigger trg_test
after logon on database
declare
l_prog varchar2(36);
l_cnt number;
l_max number := 1;
begin
select substr(program, 1, 4)
into l_prog
from sys.v_$session
where audsid=(select userenv('sessionid') from dual);
if l_prog = 'TOAD'
then
select count(*)
into l_cnt
from sys.v_$session
where program like l_prog||'%';
if l_cnt >= l_max
then
raise_application_error(-20002, 'Maximum connection exceeded');
end if;
end if;
end;
/
it works but why we get three errors? if the user has DBA role then these errors reflect in the alert....
January 22, 2002 - 5:44 pm UTC
You get the error stack, you are not getting three errors, you get one -- with three lines of text.
the actual error is "the recursive SQL failed" the logon trigger.
That would be meaningless to you so we include "Max connection exceeded" (your message).
An on logon trigger in 816 and up will not prevent a DBA account from logging in, the failure will be recorded in the alert log however.
so 604 is not avoidable?
A reader, January 22, 2002 - 5:52 pm UTC
Hi Tom
So we cannot avoid that 604 error?
The problem you see is this, when we want to restrict access of an ODBC application then the ODBC only returs ORA-0604 to the users and not the error -20002 as we intended and they complain that Oracle is not working, so the raise application error message seems useless in this case! Is there any workaround?
January 23, 2002 - 7:08 am UTC
the raise application error is NOT useless.
The problem is this -- you are NOT calling the "logon" trigger directly. If you put that code into a procedure and you called that code -- you would get the 20002. YOU are not calling this code. It is called by the database -- you call CONNECT(), they call this code. This code fails -- thus the connect fails -- thus the connect returns a connect error (along with information regarding the underlying cause of the failure).
There is no way you would get a 20002 from a connect - it is not an error that a connect can return.
DBA Roles
Dilip Dandekar, January 23, 2002 - 4:24 am UTC
Tom,
Your suggestion of jr_dba, sr_dba, night_dba roles is very good.
Can you please suggest sys priviledges to be assigned to these respective roles.
January 23, 2002 - 7:29 am UTC
not really -- thats something YOU need to decide based on YOUR site.
For example, what if I recommended "ALTER USER" for the SR_DBA -- well, that would give them the ability to become ANY user at will, effectively giving them 100% access to anything.
You need to analyze your needs, your level of security and grant appropriately. Depending on your paranoia level -- you would never give a grant without fully understanding the implications.
Giving someone a list of suggested privs lends nothing towards understanding WHY they need these privs or WHAT these privs might imply.
How to stop an IP using logon trigger ?
Randy, January 24, 2002 - 12:16 pm UTC
I know that you can use sys_context('userenv,'ip_address) to find the IP if you are logging to the database from any tcp client application. But it fails to do so if I telnet into the server from a workstaion and try sqlplus. It appears blank. Any suggestion ?
Thanks,
January 25, 2002 - 8:21 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.
Followup on DBA role
Donna Drawe, April 16, 2003 - 6:57 pm UTC
I was surprised to find that when I modified logon and logoff triggers, I WAS able to execute the triggers from IDs with the DBA role (for example, mine).
Is there a difference in 8.1.7.4 (the version I am currently testing on)? Or was it because DBA was not the only role granted to the IDs?
My current trigger is:
CREATE OR REPLACE TRIGGER GASAUDIT.TR_GASLOGONAUDIT
AFTER
LOGON
ON DATABASE
DECLARE
machinename VARCHAR2(64);
osuserid VARCHAR2(30);
programname VARCHAR2(48);
CURSOR c1
IS
SELECT osuser, machine,program
FROM v$session
WHERE audsid = USERENV('sessionid');
begin
OPEN c1;
FETCH c1 INTO osuserid, machinename,programname;
INSERT INTO gasaudit.gaslogonaudit
VALUES('LOGON', SYSDATE, USER, osuserid, machinename,programname);
CLOSE c1;
COMMIT;
END;
BTW, we originially tried to create the triggers for a specific schema but only the owner could execute the triggers?
April 16, 2003 - 7:06 pm UTC
they *fire*
they cannot prevent you from logging it -- even if they fail.
not that you asked BUT
insert into gasaudit.gaslogonaudit
select 'LOGON', sysdate, user, osuserid, machinename, programname
from v$session
where audsid = userenv('sessionid')
would be the proper way to code that -- NEVER use an explicit cursor when an implicit one will do. less code -- less work -- easier.
dont know what you mean by the last question....
clarification...
Donna, April 16, 2003 - 9:20 pm UTC
Tom,
Thanks for the response.
We originally created the trigger to be
...
AFTER
LOGON
ON GAS
...
In this instance only logins / logoffs as 'GAS' were captured. If I logged in as me, there was nothing in the table.
Thanks again for your VERY invaluable help!
April 17, 2003 - 10:09 am UTC
well, that is sort of the definition of what "after logon on gas" is about. You were looking for "after logon on database"
Why Use Logon Trigger ?
Randy, April 17, 2003 - 12:21 pm UTC
I would not use logon trigger for that. It's much more conveient to use the profile for the useres to restrict the logon in certain time.
April 17, 2003 - 3:03 pm UTC
coming in from left field on this one. how would you use a profile to restrict the times during which a user can login exactly?
that's not the purpose...
Donna, April 17, 2003 - 2:17 pm UTC
Randy, My purpose in these triggers is to capture when users logon / logoff the database and which program they are using to access the database (application .exes, msaccess, artisan, etc.).
I have an audit requirement to be able to track user access to the database and provide information on users who are accessing from outside the application code.
This was the method my DBA found doing research on the Oracle site (before I found AskTom).
Tom, thanks for the response... one last followup. Please excuse my lack of knowledge, but I've seen examples where you have logon triggers ON xxxxx.SCHEMA. How would that differ from ON DATABASE and what would you use them for?
Donna
April 17, 2003 - 3:08 pm UTC
hopefully you do not RELY on the programname information.
xcopy msaccess.exe sqlplus.exe
xcopy sqlplus.exe your_custom_name.exe
Hmmm, it is trivial to spoof the name.
AUDIT CONNECT is much much much (like alot) easier still
the best as usual
Gabriel, May 29, 2003 - 4:11 pm UTC
logon trigger not firing
amit poddar, October 09, 2003 - 11:45 am UTC
Hi,
I tried the code you supplied above
Connected to:
Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production
With the Partitioning option
JServer Release 8.1.7.4.0 - Production
SQL> drop table t;
Table dropped.
SQL> create table t ( msg varchar2(50) );
Table created.
SQL> SQL> create or replace trigger logon_trigger
2 after logon on database
3 declare
4 pragma autonomous_transaction;
5 begin
6 insert into t values ( 'logon by ' || user || ' at ' ||
7 to_char(sysdate,'dd-mon-yyyy hh24:mi:ss' ));
8 commit;
9 end;
10 /
Trigger created.
SQL> connect system
Enter password:
Connected.
SQL> select * from t;
no rows selected
SQL> connect apps
Enter password:
Connected.
SQL> connect systemm
Enter password:
Connected.
SQL> select * from t;
no rows selected
SQL>
Why is the trigger not firing ??
Is it a bug.
Thanks in advance
October 09, 2003 - 6:08 pm UTC
look in your alert log on the server --
and make sure _system_trig_enabled is NOT set in your init.ora!
You were right !!
amit poddar, October 10, 2003 - 8:55 am UTC
Hi,
You were right we had _system_trigger_enabled=false in the init.ora file.
Could you please clarify which triggers won't fire because of this parameter. Since I tested all the other triggers and they were firing correctly. By other triggers I mean the DML triggers on the tables.
October 10, 2003 - 9:15 am UTC
only system event triggers won't fire. triggers on various DDL or database events are disabled.
Excellent...But one clarification
Riaz Shahid, October 10, 2003 - 10:14 am UTC
Tom !
I am running Oracle 8.1.7 on windows 2000 server box.
Please refer to first quote by Sanjay Raj
<Quote>
Tom,
Can the YOUR_DBA have all the privileges of the DBA role?
Just my 2 cents. I think if I know the IP addresses that I want to restrict then
I can use the following to restrict logon
Create a file named protocol.ora as follows.
tcp.excluded_nodes=(XXX.XY1.XY2.Y, XXX.XY2.XY3.Z)
tcp.validnode_checking=yes
Suppose I want to Disallow connection to a user with an IP addresse of
192.168.11.20 or 192.168.10.12 then I can simply configure the protocol.ora
file as follows:
tcp.excluded_nodes=(192.168.11.20, 192.168.10.12)
tcp.validnode_checking=yes
The destination of this file would be same as that of sqlnet.ora and
tnsnames.ora. The listener will required to be stopped and restarted.
There are certain bugs in this method, so be sure to check with Oracle support
or Metalink for the specific platform and version.
For example, On Unix and Oracle version 8.1.x, I had to rename protocol.ora to
.protocol.ora in the network/admin directory.
<Quote>
I want to allow specific IPs to access my database (like 196.0.x etc.). How can i do that using above ? I think i will have to add
tcp.included_nodes=(196.0.x)
instead of
tcp.excluded_nodes=(192.168.11.20, 192.168.10.12)
Is that true ?
Moreover, I can't find protocol.ora file in my Oracle home. where it is located ?(documentation says it should be in $$Oracle_Home$$/network/admin directory).
Please suggest ...
Riaz
October 10, 2003 - 10:59 am UTC
the protocol.ora file is an optional file -- if none is there, just create it.
You should see the documentation for all of the proper use techniques. All of the docs are online at otn.oracle.com. This goody is in the net admin guide (along with lots of other good stuff)
restrict logon trigger
Tom fan, February 06, 2004 - 12:41 pm UTC
Hi Tom,
I was searching your site for examples of coding restrict login triggers on various conditions. This was a very useful thread.
I have question regarding suppressing the error stack coming out of the trigger. It is often very confusing to the end users. I am trying to see if there is any other way of coding this, without showing the error stack to the users. I read about setting the application context in 9i / using resource consumption groups etc. Is it possible, instead of failing the trigger block, to let the user connect briefly and then disconnect / disable without allowing him to do anything else, using a null context or a resource group with no resources etc?. I don't know if it is a valid idea. Is it possible and if yes what is your openion. Can you please show in an example how to do it.
Thank you soomuch.
February 07, 2004 - 1:36 pm UTC
ORA-00604: error occurred at recursive SQL level 1
ORA-20002: 'Maximum connection exceeded'
ORA-06512: at line 19
is confusing? to someone that can read?
You get to tell them anything you want in the error text. if you have a custom app -- it can catch this error and do whatever it likes (present a pretty error message).
No, you cannot allow them to connect briefly -- to what end? so they can get "your session was killed" in an error stack instead of something meaningful that you provide them with??
Maximum Roles exceeded
SSR, June 01, 2004 - 3:11 am UTC
Hi Tom,
I'm using Oracle v9.2. I have created lot of roles and it has exceeded more than the default 30. I tried increasing the max roles in init.ora and checked the v$instance view after shutting down and warming up the db afresh. Still, the value is the same (30). Where and how to do this? Now, I am not able to connect to the database using that userid for which roles are assigned.
Please advice
Thanks
SSR
June 01, 2004 - 8:36 am UTC
you are probably using an spfile.
log in as a DBA and issue:
ops$tkyte@ORA9IR2> alter system set max_enabled_roles=50 scope=spfile;
System altered.
and restart.
Maximum Roles exceeded
SSR, June 01, 2004 - 3:27 am UTC
Hi Tom,
In my previous query, I have mistakenly put that I checked the value in V$instance instead of v$parameter where description = 'max number of roles a user can have enabled'
Thanks
SSR
Maximum Enabled Roles
SSR, June 02, 2004 - 3:34 am UTC
Hi Tom,
Your reply was exact and thanks a lot for that. But, i was changing the init.ora under the folder pfile only. Can I know where we have change if we want to alter that using the parameters file? Just for curiosity sake.
Once again thanks for your timely help
SSR
June 02, 2004 - 8:28 am UTC
you would have to issue "startup pfile=filename"
It was very useful but I did a blunder
Sami, July 09, 2004 - 11:27 pm UTC
Tom,
I was following this thread to solve my own problem to prevent user accessing database from Toad etc and I tried to modify the code of the trigger trg_test to as follows:
====
create or replace trigger trg_test
after logon on database
declare
l_prog varchar2(36);
l_cnt number;
l_max number := 1;
cursor get_session is
select substr(program, 1, 4)
into l_prog
from sys.v$session
where audsid=(select userenv('sessionid') from dual);
begin
open get_session;
loop
fetch get_session into l_prog;
if l_prog in ('TOAD','MMOPN32')
then
exit;
end if;
end loop;
end;
=====
As you will notice that I did not close the cursor here after the end loop , it was the bluder I did. I loggin as SYS and created this trigger. It works OK from Toad and I could not access the database, but now I have got this locking problem and I could not even connect to database using SYS. I tried to reboot my database but got no success, after reboot, when I tried to connect using SYS, it just hung.
Before rebooting, I tried dropping the trigger, disabling the trigger and altering the code but got no luck. It always gave me ORA error ( I am sorry I donot remember the ORA #)
Is there a work around to connect to database so that I can drop this trigger or do I have to reinstall my database?
Little knowledge is dangorous thing:)
Thanks for your help.
Sami
July 10, 2004 - 9:13 am UTC
it is not the "not closing the cursor" -- it is that the loop is an infinite loop (no exit when get_session%notfound!)
set
_system_trig_enabled=false
in your init.ora and restart your database.
that trigger should be:
begin
select substr(program, 1, 4)
into l_prog
from sys.v$session
where sid = (select sid from v$mystat where rownum=1);
end;
/
no cursor whatsoever.
Still not be able to connect
Sami, July 10, 2004 - 11:22 am UTC
Tom,
I did not find this parameter in my init.ora file , so I added it ie _system_trig_enabled=false, then restarted the database. I am trying to connect as sys from sqlplus and not be able to connect, it just hung. Here is the protion of my init.ora file :
###########################################
# Miscellaneous
###########################################
aq_tm_processes=1
compatible=9.2.0.0.0
_system_trig_enabled=false
You are right, I would have used sql%notfound clause inside the cursor loop.
Thanks for your help.
July 10, 2004 - 8:53 pm UTC
you are using the wrong init.ora then.
Ok, shutdown your database.
then, as "sysdba" go in.
idle> startup mount
ORACLE instance started.
Total System Global Area 236000356 bytes
Fixed Size 451684 bytes
Variable Size 167772160 bytes
Database Buffers 67108864 bytes
Redo Buffers 667648 bytes
Database mounted.
idle> edit
Wrote file afiedt.buf
1 select x.ksppinm, ksppstvl
2 from x$ksppi x , x$ksppcv y
3 where x.indx = y.indx
4* and ksppinm = '_system_trig_enabled'
idle> /
KSPPINM
----------------------------------------------------------------
KSPPSTVL
-------------------------------------------------------------------------------
_system_trig_enabled
TRUE
idle>
and verify the setting. but at the very least, you should be able to:
o alter database open;
o drop trigger nasty_trigger;
at that point.
still could not connect to db
Sami, July 11, 2004 - 3:00 am UTC
Tom,
First of all, I am really thankful to you that you are replying to my problem even on the weekends.
Just for your information, my db is on windows 2000, 9i Rel2. My all Oracle services are now set to Manual. I started my machine, then I did following:
cmd.exe
d:\9idb\bin\sqlplus /nolog
SQL> connect sys/suh887 as sysdba
ERROR:
ORA-12560: TNS:protocol adapter error
SQL>connect system/y6t455a as sysdba
ERROR:
ORA-12560: TNS:protocol adapter error
I am not sure, why am I getting this ORA-12560 error?
Regarding init.ora file, you are right I added _system_trig_enabled=false to a wrong file ie init.ora.1127200395147. Windows explore was displaying me init.ora, however from command prompt, I can see the whole name. If I donot have init.ora file then how come my database was working OK previously before adding the trigger to sys? I searched my all hard disk and could not find any init.ora file.
Thanks
Sami
July 11, 2004 - 9:48 am UTC
start the service for the database, the service must be running in order to start the database up.
lets not even play with your init.ora ok -- you might be using an spfile, you might be using a pfile and you don't know where to look.
so, get the service running (but don't have the database running, use oradim if you need to turn off the auto-start for this instance).
connect as sysdba
startup
you'll be connected, now drop the trigger.
(the init.ora is named after your instance -- init$ORACLE_SID.ora, or if you have an spfile, it'll be named after that with sp in the filename. it'll be in the database directory on windoze -- under your oracle home)
------------------------------------------------------------------------
ok, i simulated this and here is your shortest "get well path"
a) oradim -edit -sid your_oracle_sid -startmode m
(make the instance "manual" start)
b) reboot windows, to kill the instance and clean up everything
c) IF your service for this database is set to autostart, skip this, but it won't hurt to do it (from a command prompt)
net start OracleServiceyour_oracle_sid
d) C:\Documents and Settings\Administrator>sqlplus "sys/oracle as sysdba"
SQL*Plus: Release 9.2.0.1.0 - Production on Sun Jul 11 10:17:11 2004
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to an idle instance.
idle> startup
ORACLE instance started.
Total System Global Area 135338868 bytes
Fixed Size 453492 bytes
Variable Size 109051904 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
Database mounted.
Database opened.
idle> drop trigger trg_test;
Trigger dropped.
idle>
I can connect but now its locking problem
Sami, July 11, 2004 - 12:13 pm UTC
My services in the windows are set to manual. I have spfile SPFILESA2.ora located at d:\9idb\database.
D:\>cd 9idb\bin
oradim -edit -sid SA2 -startmode m
Then after restarting windows, I performed following:
D:\9iDB\bin>net start OracleServiceSA2
The OracleServiceSA2 service is starting.
The OracleServiceSA2 service was started successfully.
D:\9iDB\bin>sqlplus "sys/suh887 as sysdba"
SQL*Plus: Release 9.2.0.4.0 - Production on Sun Jul 11 11:31:13 2004
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 126951196 bytes
Fixed Size 454428 bytes
Variable Size 109051904 bytes
Database Buffers 16777216 bytes
Redo Buffers 667648 bytes
Database mounted.
Database opened.
SQL> drop trigger trg_test;
drop trigger trg_test
*
ERROR at line 1:
ORA-04021: timeout occurred while waiting to lock object SYS.DATABASE
SQL> select x.ksppinm, ksppstvl
2 from x$ksppi x, x$ksppcv y
3 where x.indx= y.indx
4 and ksppinm = '_system_trig_enabled'
5 ;
KSPPINM
-----------------------------------------------------------
KSPPSTVL
-----------------------------------------------------------
_system_trig_enabled
TRUE
SQL> alter system set "_system_trig_enabled"=FALSE;
System altered.
SQL> select x.ksppinm, ksppstvl
2 from x$ksppi x, x$ksppcv y
3 where x.indx= y.indx
4 and ksppinm = '_system_trig_enabled'
5 ;
KSPPINM
----------------------------------------------------------------
KSPPSTVL
-------------------------------------------------------------------
_system_trig_enabled
FALSE
SQL> drop trigger trg_test;
drop trigger trg_test
*
ERROR at line 1:
ORA-04021: timeout occurred while waiting to lock object SYS.DATABASE
I tried to change the trigger code , but no success
SQL> create or replace trigger trg_test
2 after logon on database
3 begin
4 null;
5 end;
6 /
after logon on database
*
ERROR at line 2:
ORA-04021: timeout occurred while waiting to lock object SYS.DATABASE
SQL> alter trigger trg_test disable;
alter trigger trg_test disable
*
ERROR at line 1:
ORA-04021: timeout occurred while waiting to lock object SYS.DATABASE
July 11, 2004 - 1:34 pm UTC
well, at least now you can log in.
Now, what is the object "database" you have also created in SYS?
(and now hopefully you'll also realize that you should never use SYS to do stuff, sys is magic, sys is special, sys is Oracle's account, you should never use it)
this database object isn't some we create - -what is it?
Sami, July 11, 2004 - 1:38 pm UTC
One thing I forgot to mention is that I can connect database from Toad or from sqlplus etc after I did
alter system set "_system_trig_enabled"=FALSE;
Thanks for all your help.
about database
Sami, July 11, 2004 - 1:57 pm UTC
Well, I have not created this "database" object. I just followed the syntax of writing after logon trigger, which is
create or replace trigger trg_test
after logon on database
Yes, this excercise taught me that I should not use SYS.
Are you suggesting that I should not create AFTER LOGON trigger from SYS? In my situation, if I want to prevent user accessing any schema from ODBC or Toad then it has to be written in SYS, correct me if I am wrong?
Thanks for all your help.
Sami
July 12, 2004 - 11:00 am UTC
someone created an object called database -- select * from dba_objects where owner = 'SYS' and object_name = 'DATABASE', see what it is.
You should create your own account -- grant it the appropriate privs, and create the trigger in that schema -- NOT in sys's schema.
I could not see any object as DATABASE
SAmi, July 12, 2004 - 6:55 pm UTC
I could not see any object name called 'DATABASE', here is the output of my SELECT:
SQL> select * from dba_objects where owner='SYS' and object_name like 'DATABASE';
no rows selected
??
July 12, 2004 - 11:42 pm UTC
Ok, how about just "where object_name = 'DATABASE'"
this too
A reader, July 12, 2004 - 6:56 pm UTC
SQL> select * from dba_objects where owner='SYS' and object_name = 'DATABASE'
2 ;
no rows selected
I tried again
Sami, July 13, 2004 - 8:45 pm UTC
here is the output
SQL*Plus: Release 9.2.0.4.0 - Production on Tue Jul 13 20:46:20 2004
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 126951196 bytes
Fixed Size 454428 bytes
Variable Size 109051904 bytes
Database Buffers 16777216 bytes
Redo Buffers 667648 bytes
Database mounted.
Database opened.
SQL> select * from dba_objects where object_name = 'DATABASE';
no rows selected
SQL>
I donot know what is this DATABASE?
July 14, 2004 - 11:14 am UTC
nor do i, if it is still happening, please open a tar with support.
A reader, July 15, 2004 - 4:33 pm UTC
What is this mean. There is no object called database in Oracle. And I am able to create a table called database and the query works fine
July 15, 2004 - 6:37 pm UTC
did you read the entire thread?
after logon on database
*
ERROR at line 2:
ORA-04021: timeout occurred while waiting to lock object SYS.DATABASE
he had an error trying to lock an object named SYS.DATABASE but says "i don't have an object named database" -- something is "wrong" somewhere.
Clarification
Sal, February 13, 2005 - 10:45 am UTC
Tom,
Somewhere in the middle of this thread you have mentioned this about the effect of setting _system_trig_enabled to false:
Followup:
only system event triggers won't fire. triggers on various DDL or database events are disabled.
Did you mean enabled instead of disabled? In my 9.2.0.3/solaris database currently this parameter is set to false, but a trigger on a table that inserts the same row in another table still works well. Just wanted to confirm this behaviour.
Thanks
- Sal
February 13, 2005 - 10:48 am UTC
"triggers on DDL or database events"
Not DML triggers. Your DML triggers (that trigger on the table) is not affected by this.
I have the same troubles with this object SYS.DATABASE
Hans Wijte, September 12, 2005 - 8:17 am UTC
Hi Tom,
I have the exact same problem with a logon trigger; every now and then this trigger prevents new connections to the database and when I try to disable the trigger I get a timeout while trying to lock the object SYS.DATABASE.
This is an object not created by me and it doesn't exist
in dba_objects.
SQL> ALTER trigger ING_SECURITY.LOGON_VALIDATE_TRIGGER disable;
ALTER trigger ING_SECURITY.LOGON_VALIDATE_TRIGGER disable
*
ERROR in line 1:
.ORA-04021: timeout occurred while waiting to lock object SYS.DATABASE
SQL> select * from dba_objects where owner = 'SYS' and object_name = 'DATABASE';
No rows selected.
SQL> select * from dba_objects where object_name = 'DATABASE';
No rows selected.
SQL>
September 12, 2005 - 8:40 am UTC
what does that trigger do - and do you have any other "alter triggers"
sys.database
Hans Wijte, September 12, 2005 - 9:08 am UTC
This trigger is supposed to check the application with which
the end users logon to the database(I know, I know, it's easy to circumvent this) ; but because it occasionally prevents the end users from logging in to the database
I tried to disable this trigger.
After bouncing the database there is no problem whatsoever
in diabling the trigger.
SQL*Plus: Release 8.1.7.0.0 - Production on Mon Sep 12 14:47:29 2005
(c) Copyright 2000 Oracle Corporation. All rights reserved.
Connected to:
Oracle8i Enterprise Edition Release 8.1.7.3.0 - Production
With the Partitioning option
JServer Release 8.1.7.3.0 - Production
SQL> ALTER trigger ING_SECURITY.LOGON_VALIDATE_TRIGGER disable;
Trigger altered.
SQL> select trigger_name , status from dba_triggers
2 where owner = 'ING_SECURITY';
TRIGGER_NAME STATUS
------------------------------ --------
DML_PROFILS_UTILISATEUR ENABLED
DDL_TRIGGER ENABLED
LOGOFF_AUDIT_TRIGGER ENABLED
LOGON_AUDIT_TRIGGER ENABLED
LOGON_VALIDATE_TRIGGER DISABLED
SQL>
LOGON TRIGGER IS NOT FIRING
Syed, September 28, 2005 - 5:16 pm UTC
Tom, your site is an excellent resource and help for everyone, from beginner to experienced, Sir need your help, desperately, we have 3 different environments, created a LOGON trigger, working fine in our 2 environments but not been fired in our 3rd environment.
For example:
Env1: has logon trigger test_trig ----- fired perfectly at login
Env2: same trigger test_trig à fired perfectly in 2nd too.
Env3: same trigger test_trig à but not firing in this environment.
Can you please assist what exactly is the problem, why its firing in 2 but not in 3rd database.
Need your help, in where to look and what to look for, for firing a trigger, what exactly is the parameter to be enabled or put on to get triggers fired.
As always, your help and assistance will highly be admired
Thanks.
September 28, 2005 - 5:42 pm UTC
_system_trig_enabled
do you have that set in the 3rd environment.
LOGON TRIGGER IS NOT FIRING
Syed, September 28, 2005 - 10:31 pm UTC
Sir first of all thanks for the reply, but can't see this parameter in all of our parameters, we are on Oracle Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production, isn't this parameter is for downgrading.
thanks for your help...
September 29, 2005 - 7:00 am UTC
just make sure it isn't set. Yes, it is for upgrades but you can set it yourself and someone may have
ops$tkyte@ORA9IR2> show parameter trig
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
_system_trig_enabled boolean FALSE
ops$tkyte@ORA9IR2>
show parameter will confirm - so you don't have to dig through parameter files.
Also, are you testing with a DBA account? DBA accounts won't be affected by a logon trigger failing, only a message is written to the alert log. so it could appear that the trigger isn't firing even though it is, check your alert log.
LOGON TRIGGER IS NOT FIRING
Syed, September 29, 2005 - 11:05 am UTC
No we are connecting via a normal user, yes I have just checked in my other environments, this parameter is set to TRUE, but only in this it is not there.
If I set this paremter to TRUE, the LOGON trigger will fired, because we are planning to reboot our machine, and put this parameter to TRUE.
Please confirm, this is fine, thanks.
September 30, 2005 - 8:17 am UTC
if this is set to false (not true), then someone set it false for the default is TRUE.
USER
A reader, February 09, 2007 - 4:00 pm UTC
a) What is the need for codes like
select ... from sys.v_$session
where audsid=(select userenv('sessionid') from dual);
Similary for v$mystat where rownum=1
when you have USER available
b) If there are 2 after log on triggers(even though it may not be a good practice), will both of them fire and in which order ?
Anto
February 12, 2007 - 9:39 am UTC
a) huh??? no idea what you mean.
if you want your v$session row, suggest:
select * from v$session where sid = ( select sid from v$mystat where rownum = 1);
b) both will - in some arbitrary order. If you need to have "some order" then you should have one trigger that calls two procedures - in the proper order.
logon trigger not working
Vishal Kadam, November 06, 2014 - 2:28 am UTC
Hi,
I am using below trigger to restrict connection from toad and SQL developer but its not working .... Please help me.
create or replace TRIGGER block
AFTER LOGON
ON DATABASE
DECLARE
CURSOR user_prog IS SELECT program FROM v$session
WHERE audsid=sys_context('USERENV','SESSIONID') and username not in('ETIQA_CGLS');
user_rec user_prog%ROWTYPE;
BEGIN
OPEN user_prog;
FETCH user_prog INTO user_rec;
IF user_rec.program like 'TOAD%' OR user_rec.program like 'SQL Developer%'
THEN
-- insert into test1_trg values(1);
-- commit;
RAISE_APPLICATION_ERROR(-20001, 'You are not allowed to login with TOAD or SQL Developer');
END IF;
-- insert into test1_trg values(2);
-- commit;
CLOSE user_prog;
END;
/
SERHAT, March 17, 2021 - 1:54 pm UTC
If you want to prohibit connection any user;
1. Build a customized DBA role
2. Exclude below system privilege and roles from your customized dba role
SYSTEM PRIVILEGE
ADMINISTER DATABASE TRIGGER
ROLES
DATAPUMP_IMP_FULL_DATABASE
IMP_FULL_DATABASE
I hope it helps you.
Serhat.