Trigger force a logout?

List,
To plug a security gap, I'm trying to create a trigger on a certain
username. If the trigger condition is met, the trigger would force a logout.
Is that possible?
Thanks for any suggestions,
Dennis Williams

yes, it is possible. It would be a system trigger.
On Thu, Jun 25, 2009 at 9:15 AM, Dennis Williams <
oracledba.williams@gmail.com> wrote:
> List,
>
> To plug a security gap, I'm trying to create a trigger on a certain
> username. If the trigger condition is met, the trigger would force a logout.
> Is that possible?
>
> Thanks for any suggestions,
> Dennis Williams
>
--
Andrew W. Kerber
'If at first you dont succeed, dont take up skydiving.'

One wonders why one doesn't simply lock the account.
ALTER USER bad_user ACCOUNT LOCK;
seems easier than writing a trigger.
Similarly, you may wish to explore auditing and system/application contexts
so that you could audit who's trying this account.
On Thu, Jun 25, 2009 at 9:25 AM, Andrew Kerber wrote:
> yes, it is possible. It would be a system trigger.
>
>
> On Thu, Jun 25, 2009 at 9:15 AM, Dennis Williams <
> oracledba.williams@gmail.com> wrote:
>
>> List,
>>
>> To plug a security gap, I'm trying to create a trigger on a certain
>> username. If the trigger condition is met, the trigger would force a logout.
>> Is that possible?
>>
>> Thanks for any suggestions,
>> Dennis Williams
>>
>
>
>
> --
> Andrew W. Kerber
>
> 'If at first you dont succeed, dont take up skydiving.'
>
--
Adam Musch
ahmusch@gmail.com

account locking is not feasible when uncer certain conditions (eg source ip
address or timeslots of a day) the username would be allowed for login.
you can simply add a line with
raise_application_error(-2000,'sorry not allowed to login')
in your logon trigger code .
Andre
2009/6/25 Adam Musch
> One wonders why one doesn't simply lock the account.
>
> ALTER USER bad_user ACCOUNT LOCK;
>
> seems easier than writing a trigger.
>
> Similarly, you may wish to explore auditing and system/application contexts
> so that you could audit who's trying this account.
>
> On Thu, Jun 25, 2009 at 9:25 AM, Andrew Kerber wrote:
>
>> yes, it is possible. It would be a system trigger.
>>
>>
>> On Thu, Jun 25, 2009 at 9:15 AM, Dennis Williams <
>> oracledba.williams@gmail.com> wrote:
>>
>>> List,
>>>
>>> To plug a security gap, I'm trying to create a trigger on a certain
>>> username. If the trigger condition is met, the trigger would force a logout.
>>> Is that possible?
>>>
>>> Thanks for any suggestions,
>>> Dennis Williams
>>>
>>
>>
>>
>> --
>> Andrew W. Kerber
>>
>> 'If at first you dont succeed, dont take up skydiving.'
>>
>
>
>
> --
> Adam Musch
> ahmusch@gmail.com
>

What would your conditions be?--this can be tricky. For example, if you try=
to block access from certain programs (e.g. sqlplus.exe) for non-system us=
ers, the program name can be spoofed.=0A=0ABut yes, this can be done.=0A=0A=
Joseph D'Antoni=0ASynthes USA=0A=0A=0A=0A=0A_______________________________=
_=0AFrom: Andrew Kerber =0ATo: oracledba.williams@=
gmail.com=0ACc: oracle-l@freelists.org=0ASent: Thursday, June 25, 2009 10:2=
5:11 AM=0ASubject: Re: Trigger force a logout?=0A=0Ayes, it is possible.=A0=
It would be a system trigger.=0A=0A=0AOn Thu, Jun 25, 2009 at 9:15 AM, Den=
nis Williams wrote:=0A=0AList,=0A>=0A>To plu=
g a security gap, I'm trying to create a trigger on a certain username. If =
the trigger condition is met, the trigger would force a logout. Is that pos=
sible?=0A>=0A>Thanks for any suggestions,=0A>Dennis Williams=0A=0A=0A-- =0A=
Andrew W. Kerber=0A=0A'If at first you dont succeed, dont take up skydiving=
..'=0A=0A=0A=0A

Adam,
As I mentioned, this is to plug a security gap. The password is embedded in
an application we can't alter. I have a way to detect whether the login is
coming from the application or elsewhere. My thought is to create a trigger
that does something if someone tries to login to the account and they aren't
coming from the application.
Not ideal, but then we don't always live in an ideal world.
Thanks for your ideas.
Dennis Williams
On Thu, Jun 25, 2009 at 9:28 AM, Adam Musch wrote:
> One wonders why one doesn't simply lock the account.
>
> ALTER USER bad_user ACCOUNT LOCK;
>
> seems easier than writing a trigger.
>
> Similarly, you may wish to explore auditing and system/application contexts
> so that you could audit who's trying this account.
>
> On Thu, Jun 25, 2009 at 9:25 AM, Andrew Kerber wrote:
>
>> yes, it is possible. It would be a system trigger.
>>
>>
>> On Thu, Jun 25, 2009 at 9:15 AM, Dennis Williams <
>> oracledba.williams@gmail.com> wrote:
>>
>>> List,
>>>
>>> To plug a security gap, I'm trying to create a trigger on a certain
>>> username. If the trigger condition is met, the trigger would force a logout.
>>> Is that possible?
>>>
>>> Thanks for any suggestions,
>>> Dennis Williams
>>>
>>
>>
>>
>> --
>> Andrew W. Kerber
>>
>> 'If at first you dont succeed, dont take up skydiving.'
>>
>
>
>
> --
> Adam Musch
> ahmusch@gmail.com
>

Joe,
Agreed, not bullet-proof, but hopefully much better than the current
situation.
Dennis
On Thu, Jun 25, 2009 at 9:30 AM, Joey D'Antoni wrote:
> What would your conditions be?--this can be tricky. For example, if you
> try to block access from certain programs (e.g. sqlplus.exe) for non-system
> users, the program name can be spoofed.
>
> But yes, this can be done.
>
> Joseph D'Antoni
> Synthes USA
>
> ------------------------------
> *From:* Andrew Kerber
> *To:* oracledba.williams@gmail.com
> *Cc:* oracle-l@freelists.org
> *Sent:* Thursday, June 25, 2009 10:25:11 AM
> *Subject:* Re: Trigger force a logout?
>
> yes, it is possible. It would be a system trigger.
>
> On Thu, Jun 25, 2009 at 9:15 AM, Dennis Williams <
> oracledba.williams@gmail.com> wrote:
>
>> List,
>>
>> To plug a security gap, I'm trying to create a trigger on a certain
>> username. If the trigger condition is met, the trigger would force a logout.
>> Is that possible?
>>
>> Thanks for any suggestions,
>> Dennis Williams
>>
>
>
>
> --
> Andrew W. Kerber
>
> 'If at first you dont succeed, dont take up skydiving.'
>
>

We do this using a database event log on trigger. If the username
follows a certain pattern then it must meet certain criteria for values
in v$session. One of the keys to making this work is not to disclose
what values the routine tests against. You have machine, program, and
the potentially the columns populated by dbms_application_info to use
plus sys_context information such as IP. Use several.
=20
You can spoof the IP via java, you can spoof the program name, but if
the user does not know what is being tested against then spoofing all
the tests will not be that easy. The solution may not be undefeatable,
but it is reasonably solid, IMHO.
=20
-- Mark D Powell --=20
Phone (313) 592-5148=20
=20
________________________________
From: oracle-l-bounce@freelists.org
[mailto:oracle-l-bounce@freelists.org] On Behalf Of Dennis Williams
Sent: Thursday, June 25, 2009 10:43 AM
To: ahmusch@gmail.com
Cc: andrew.kerber@gmail.com; oracle-l@freelists.org
Subject: Re: Trigger force a logout?
=09
=09
Adam,
=20
As I mentioned, this is to plug a security gap. The password is
embedded in an application we can't alter. I have a way to detect
whether the login is coming from the application or elsewhere. My
thought is to create a trigger that does something if someone tries to
login to the account and they aren't coming from the application.
Not ideal, but then we don't always live in an ideal world.
=20
Thanks for your ideas.
Dennis Williams
=09
=09
On Thu, Jun 25, 2009 at 9:28 AM, Adam Musch
wrote:
=09
One wonders why one doesn't simply lock the account. =20
=20
ALTER USER bad_user ACCOUNT LOCK;
=20
seems easier than writing a trigger.
=20
Similarly, you may wish to explore auditing and
system/application contexts so that you could audit who's trying this
account.
=09
=09
On Thu, Jun 25, 2009 at 9:25 AM, Andrew Kerber
wrote:
=09
yes, it is possible. It would be a system
trigger.=20
On Thu, Jun 25, 2009 at 9:15 AM, Dennis Williams
wrote:
=09
List,
=20
To plug a security gap, I'm trying to
create a trigger on a certain username. If the trigger condition is met,
the trigger would force a logout. Is that possible?
=20
Thanks for any suggestions,
Dennis Williams

--=20
Andrew W. Kerber
=09
'If at first you dont succeed, dont take up
skydiving.'
=09

--=20
Adam Musch
ahmusch@gmail.com
=09