Home » SQL & PL/SQL » SQL & PL/SQL » Create trigger and raise application for Invalid login (merged)
Create trigger and raise application for Invalid login (merged) [message #247966] Wed, 27 June 2007 11:52 Go to next message
qasim845
Messages: 95
Registered: March 2007
Location: Philadelphia
Member
Hi,

Is anybody share the code for trigger which can raise application of Access Denied, If any user is trying to login to the database from the table which is called "restricted_table". The "restricted_table" contain the user id's that are not allowed to login into the database. So, whenever the that user will login, raise application of access denied.

thanks in advance
qasim
Re: raise application for Invalid login [message #247970 is a reply to message #247966] Wed, 27 June 2007 11:59 Go to previous messageGo to next message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
Umm why not just disable their database account then ?
Re: raise application for Invalid login [message #247986 is a reply to message #247966] Wed, 27 June 2007 13:46 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
alter user ... account lock;

Sorry Marc, didn't see your answer.

Regards
Michel

[Updated on: Wed, 27 June 2007 13:47]

Report message to a moderator

Re: raise application for Invalid login [message #248017 is a reply to message #247986] Wed, 27 June 2007 15:39 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
If you really need the login trigger thing - it would be similar to this untested code...
CREATE OR REPLACE TRIGGER dba.logon_restrict_trg
after logon on database
declare
 v_cnt number;
begin
  select count(*) into v_cnt from restricted_table where upper(restricted_user) = upper(ora_login_user);
  if v_cnt > 0 then
    raise_application_error (-20501, 'ERROR: Login using '||ora_login_user||' prohibited');
  end if;
end;
/


As far as I remember, login triggers don't fire for users with DBA priv. (in all versions).
Create trigger [message #248049 is a reply to message #247966] Wed, 27 June 2007 19:59 Go to previous messageGo to next message
qasim845
Messages: 95
Registered: March 2007
Location: Philadelphia
Member
HI

I have a table which contain 2 columns one is operating system user id and second is schema. So now i need to create the trigger, if anybody will try to login the database.his/her osuserid and schema must match to my table osuserid and schema.if it does not match it show give raise application error.
Thanks in advance
Re: raise application for Invalid login [message #248064 is a reply to message #248017] Wed, 27 June 2007 23:31 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
More precisely, those with ADMINISTER DATABASE TRIGGER privilege.

Regards
Michel
Re: Create trigger and raise application for Invalid login (merged) [message #248223 is a reply to message #247966] Thu, 28 June 2007 06:03 Go to previous messageGo to next message
qasim845
Messages: 95
Registered: March 2007
Location: Philadelphia
Member
I will brief little bit more about my problem. If the information of that person matches with osuserid and schema of the table then that person can login and send his information to the login audit table. If it does not match then it should raise error of access denied.

Thanks again
Re: Create trigger and raise application for Invalid login (merged) [message #248227 is a reply to message #248223] Thu, 28 June 2007 06:26 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
So Andrew gives you the code.
OS user can be found in v$session.

Regards
Michel
Re: Create trigger and raise application for Invalid login (merged) [message #248230 is a reply to message #247966] Thu, 28 June 2007 06:33 Go to previous messageGo to next message
qasim845
Messages: 95
Registered: March 2007
Location: Philadelphia
Member
thanks micheal and how about schema, can we find it out in v$session.

rgds
Re: Create trigger and raise application for Invalid login (merged) [message #248242 is a reply to message #248230] Thu, 28 June 2007 07:27 Go to previous message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Schema is ora_login_user as Andrew posted but you can also find it in v$session.

Regards
Michel
Previous Topic: Sql Query
Next Topic: Trigger Help
Goto Forum:
  


Current Time: Thu Feb 13 12:09:31 CST 2025