Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

Querying Data without Valid User Id

11 posts in General Discussion Last posting was on 2011-02-20 13:33:55.0Z
Jamal Posted on 2011-02-07 02:38:49.0Z
From: "Jamal" <jahmad77@optusnet.com.au>
Newsgroups: sybase.public.ase.general
Subject: Querying Data without Valid User Id
Lines: 32
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5931
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5994
X-RFC2646: Format=Flowed; Original
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4d4f5b39@forums-1-dub>
Date: 6 Feb 2011 18:38:49 -0800
X-Trace: forums-1-dub 1297046329 10.22.241.152 (6 Feb 2011 18:38:49 -0800)
X-Original-Trace: 6 Feb 2011 18:38:49 -0800, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29928
Article PK: 79157

Hi,

I am working on a requirement where I need to lock a user after specified
number of failed login attempts and update user table of my application . It
is also required to give warning message about number if failed login
attempts left after each failed login attempt.

I can configure this by enabling the Auditing in Sybase and limit the Max
Failed Login attempts but I wouldn't know how many failed login attempts are
left. Also when user login is locked, Sybase gives my only generic error
code (4002) which is same of Invalid User/Password.

Solution in my mind is to Create a dedicated user login, hard coded
somewhere in my application and user that connection to get the information
from database i.e. User status from syslogins, Number of available failed
login attempts. This hard coding to user login may not be acceptable to our
clients and I am also a bit reluctant to use it unless there is no other
solution.

I am wondering if anybody has implements this type of requirement and how?

We are using Sybase ASE 15 and PowerBuilder 11.5.

I appreciate any comments.

Thanks


J Posted on 2011-02-07 17:04:35.0Z
From: jtotally_bogus@sbcglobal.net (J)
Newsgroups: sybase.public.ase.general
Subject: Re: Querying Data without Valid User Id
Reply-To: J@bogusemailAddress.com
Message-ID: <4d5025c8.349120859@forums.sybase.com>
References: <4d4f5b39@forums-1-dub>
X-Newsreader: Forte Free Agent 1.21/32.243
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Date: 7 Feb 2011 09:04:35 -0800
X-Trace: forums-1-dub 1297098275 10.22.241.152 (7 Feb 2011 09:04:35 -0800)
X-Original-Trace: 7 Feb 2011 09:04:35 -0800, vip152.sybase.com
Lines: 43
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29929
Article PK: 79158

On 6 Feb 2011 18:38:49 -0800, "Jamal" <jahmad77@optusnet.com.au>
wrote:

I don't understand what you mean by "lock a user after specified
number of failed login attempts and update user table of my
application ", but you could use sp_displaylogin after 4002 errors to
gain information about the current status of the login re. failed
attempts etc.

Jay

>Hi,
>
>I am working on a requirement where I need to lock a user after specified
>number of failed login attempts and update user table of my application . It
>is also required to give warning message about number if failed login
>attempts left after each failed login attempt.
>
>I can configure this by enabling the Auditing in Sybase and limit the Max
>Failed Login attempts but I wouldn't know how many failed login attempts are
>left. Also when user login is locked, Sybase gives my only generic error
>code (4002) which is same of Invalid User/Password.
>
>Solution in my mind is to Create a dedicated user login, hard coded
>somewhere in my application and user that connection to get the information
>from database i.e. User status from syslogins, Number of available failed
>login attempts. This hard coding to user login may not be acceptable to our
>clients and I am also a bit reluctant to use it unless there is no other
>solution.
>
>I am wondering if anybody has implements this type of requirement and how?
>
>We are using Sybase ASE 15 and PowerBuilder 11.5.
>
>I appreciate any comments.
>
>Thanks
>
>
>
>
>
>


Jamal Posted on 2011-02-08 05:00:18.0Z
Sender: 1b96.4d50bfd0.1804289383@sybase.com
From: Jamal
Newsgroups: sybase.public.ase.general
Subject: Re: Querying Data without Valid User Id
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4d50cde2.1d2b.1681692777@sybase.com>
References: <4d5025c8.349120859@forums.sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 7 Feb 2011 21:00:18 -0800
X-Trace: forums-1-dub 1297141218 10.22.241.41 (7 Feb 2011 21:00:18 -0800)
X-Original-Trace: 7 Feb 2011 21:00:18 -0800, 10.22.241.41
Lines: 57
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29931
Article PK: 79161

Hi Jay,

Thanks for your response. TO call sp_displaylogin is also
required valid database connection - isnt?.
What we are doing here is just connecting to the database
using the credentail entered by user on login screen. If
user/password is invalid we get the error and we inform user
that your user/password is wrong.

> On 6 Feb 2011 18:38:49 -0800, "Jamal"
> <jahmad77@optusnet.com.au> wrote:
>
> I don't understand what you mean by "lock a user after
> specified number of failed login attempts and update user
> table of my application ", but you could use
> sp_displaylogin after 4002 errors to gain information
> about the current status of the login re. failed attempts
> etc.
>
> Jay
> >Hi,
> >
> >I am working on a requirement where I need to lock a user
> after specified >number of failed login attempts and
> update user table of my application . It >is also
> required to give warning message about number if failed
> login >attempts left after each failed login attempt.
> >
> >I can configure this by enabling the Auditing in Sybase
> and limit the Max >Failed Login attempts but I wouldn't
> know how many failed login attempts are >left. Also when
> user login is locked, Sybase gives my only generic error
> >code (4002) which is same of Invalid User/Password. >
> >Solution in my mind is to Create a dedicated user login,
> hard coded >somewhere in my application and user that
> connection to get the information >from database i.e.
> User status from syslogins, Number of available failed
> >login attempts. This hard coding to user login may not be
> acceptable to our >clients and I am also a bit reluctant
> to use it unless there is no other >solution.
> >
> >I am wondering if anybody has implements this type of
> requirement and how? >
> >We are using Sybase ASE 15 and PowerBuilder 11.5.
> >
> >I appreciate any comments.
> >
> >Thanks
> >
> >
> >
> >
> >
> >
>


Mark Posted on 2011-02-08 13:10:19.0Z
From: Mark <mlibner@yahoo.com>
Reply-To: mlibner@yahoo.com
Organization: Security Finance
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.2.13) Gecko/20101207 Thunderbird/3.1.7
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Querying Data without Valid User Id
References: <4d5025c8.349120859@forums.sybase.com> <4d50cde2.1d2b.1681692777@sybase.com>
In-Reply-To: <4d50cde2.1d2b.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4d5140bb$1@forums-1-dub>
Date: 8 Feb 2011 05:10:19 -0800
X-Trace: forums-1-dub 1297170619 10.22.241.152 (8 Feb 2011 05:10:19 -0800)
X-Original-Trace: 8 Feb 2011 05:10:19 -0800, vip152.sybase.com
Lines: 67
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29932
Article PK: 79162

Hi Jamal,

I use an instance variable on my uo_login to track the number of login
attempts. If the attempts exceed the number allowed, which can be stored
in a config file, I shut down the app. If you want to log this failure
in the db you could write a simple stored proc that writes to a message
table. You could create a basic user id to connect to the db that only
has rights to execute that proc.

hth,
Mark

On 2/8/2011 12:00 AM, Jamal wrote:
> Hi Jay,
>
> Thanks for your response. TO call sp_displaylogin is also
> required valid database connection - isnt?.
> What we are doing here is just connecting to the database
> using the credentail entered by user on login screen. If
> user/password is invalid we get the error and we inform user
> that your user/password is wrong.
>
>
>
>> On 6 Feb 2011 18:38:49 -0800, "Jamal"
>> <jahmad77@optusnet.com.au> wrote:
>>
>> I don't understand what you mean by "lock a user after
>> specified number of failed login attempts and update user
>> table of my application ", but you could use
>> sp_displaylogin after 4002 errors to gain information
>> about the current status of the login re. failed attempts
>> etc.
>>
>> Jay
>>> Hi,
>>>
>>> I am working on a requirement where I need to lock a user
>> after specified>number of failed login attempts and
>> update user table of my application . It>is also
>> required to give warning message about number if failed
>> login>attempts left after each failed login attempt.
>>> I can configure this by enabling the Auditing in Sybase
>> and limit the Max>Failed Login attempts but I wouldn't
>> know how many failed login attempts are>left. Also when
>> user login is locked, Sybase gives my only generic error
>>> code (4002) which is same of Invalid User/Password.>
>>> Solution in my mind is to Create a dedicated user login,
>> hard coded>somewhere in my application and user that
>> connection to get the information>from database i.e.
>> User status from syslogins, Number of available failed
>>> login attempts. This hard coding to user login may not be
>> acceptable to our>clients and I am also a bit reluctant
>> to use it unless there is no other>solution.
>>> I am wondering if anybody has implements this type of
>> requirement and how?>
>>> We are using Sybase ASE 15 and PowerBuilder 11.5.
>>>
>>> I appreciate any comments.
>>>
>>> Thanks
>>>
>>>
>>>
>>>
>>>
>>>


J Posted on 2011-02-08 16:50:07.0Z
From: jtotally_bogus@sbcglobal.net (J)
Newsgroups: sybase.public.ase.general
Subject: Re: Querying Data without Valid User Id
Reply-To: J@bogusemailAddress.com
Message-ID: <4d517327.434463171@forums.sybase.com>
References: <4d5025c8.349120859@forums.sybase.com> <4d50cde2.1d2b.1681692777@sybase.com>
X-Newsreader: Forte Free Agent 1.21/32.243
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Date: 8 Feb 2011 08:50:07 -0800
X-Trace: forums-1-dub 1297183807 10.22.241.152 (8 Feb 2011 08:50:07 -0800)
X-Original-Trace: 8 Feb 2011 08:50:07 -0800, vip152.sybase.com
Lines: 76
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29934
Article PK: 79163

On 7 Feb 2011 21:00:18 -0800, Jamal wrote:

Yes you would need a valid connection to the dataserver to gain any
more information about why this current user could not login.

I think you could do some of the simple things in your code to prevent
problems:

(1) check if "cap lock" was on if you got 4002 after a login attempt
(2) check if the same user issues the identical password as was used
in a previous recent attempt.
(3) with a separate connection use sp_displaylogin or directly query
syslogins to look at some specific problems. I expect this might be
iterative as you could maybe initially check for login attempts
exceeded and add more as you go.

Jay

>Hi Jay,
>
>Thanks for your response. TO call sp_displaylogin is also
>required valid database connection - isnt?.
>What we are doing here is just connecting to the database
>using the credentail entered by user on login screen. If
>user/password is invalid we get the error and we inform user
>that your user/password is wrong.
>
>
>
>> On 6 Feb 2011 18:38:49 -0800, "Jamal"
>> <jahmad77@optusnet.com.au> wrote:
>>
>> I don't understand what you mean by "lock a user after
>> specified number of failed login attempts and update user
>> table of my application ", but you could use
>> sp_displaylogin after 4002 errors to gain information
>> about the current status of the login re. failed attempts
>> etc.
>>
>> Jay
>> >Hi,
>> >
>> >I am working on a requirement where I need to lock a user
>> after specified >number of failed login attempts and
>> update user table of my application . It >is also
>> required to give warning message about number if failed
>> login >attempts left after each failed login attempt.
>> >
>> >I can configure this by enabling the Auditing in Sybase
>> and limit the Max >Failed Login attempts but I wouldn't
>> know how many failed login attempts are >left. Also when
>> user login is locked, Sybase gives my only generic error
>> >code (4002) which is same of Invalid User/Password. >
>> >Solution in my mind is to Create a dedicated user login,
>> hard coded >somewhere in my application and user that
>> connection to get the information >from database i.e.
>> User status from syslogins, Number of available failed
>> >login attempts. This hard coding to user login may not be
>> acceptable to our >clients and I am also a bit reluctant
>> to use it unless there is no other >solution.
>> >
>> >I am wondering if anybody has implements this type of
>> requirement and how? >
>> >We are using Sybase ASE 15 and PowerBuilder 11.5.
>> >
>> >I appreciate any comments.
>> >
>> >Thanks
>> >
>> >
>> >
>> >
>> >
>> >
>>


Bret Halford Posted on 2011-02-08 18:49:40.0Z
From: Bret Halford <bret@sybase.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.2.13) Gecko/20101207 Thunderbird/3.1.7
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Querying Data without Valid User Id
References: <4d4f5b39@forums-1-dub>
In-Reply-To: <4d4f5b39@forums-1-dub>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4d519044@forums-1-dub>
Date: 8 Feb 2011 10:49:40 -0800
X-Trace: forums-1-dub 1297190980 10.22.241.152 (8 Feb 2011 10:49:40 -0800)
X-Original-Trace: 8 Feb 2011 10:49:40 -0800, vip152.sybase.com
Lines: 52
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29936
Article PK: 79165


On 2/6/2011 7:38 PM, Jamal wrote:
> Hi,
>
> I am working on a requirement where I need to lock a user after specified
> number of failed login attempts and update user table of my application . It
> is also required to give warning message about number if failed login
> attempts left after each failed login attempt.
>
> I can configure this by enabling the Auditing in Sybase and limit the Max
> Failed Login attempts but I wouldn't know how many failed login attempts are
> left. Also when user login is locked, Sybase gives my only generic error
> code (4002) which is same of Invalid User/Password.
>
> Solution in my mind is to Create a dedicated user login, hard coded
> somewhere in my application and user that connection to get the information
> from database i.e. User status from syslogins, Number of available failed
> login attempts. This hard coding to user login may not be acceptable to our
> clients and I am also a bit reluctant to use it unless there is no other
> solution.
>
> I am wondering if anybody has implements this type of requirement and how?
>
> We are using Sybase ASE 15 and PowerBuilder 11.5.
>
> I appreciate any comments.
>
> Thanks

The philosophy behind only providing a generic error is
that there is no sense in giving hackers more information
than necessary. Valid users will presumably be able to contact
someone in authority (helpdesk, sa, etc.) who can get them
the details on why their login is failing, reset the password,
unlock the account, etc.

So you might revisit with your clients how important it really
is to them to have a message letting hackers know how many
more attempts they can make on this account before they
should give up on it and start trying another account. (Otherwise,
god forbid, the hacker might waste valuable time continuing
to uselessly bang away at an account that had already been locked).

I'd recommend focusing effort on having the system
alert administrators when accounts are generating invalid
logins. (for example, a cron job might run every 15 minutes
and report any newly locked logins).

-bret


Manish Negandhi [TeamSybase] Posted on 2011-02-09 12:30:16.0Z
From: "Manish Negandhi [TeamSybase]" <negandhi.manish.nospam@gmail.com>
Newsgroups: sybase.public.ase.general
References: <4d4f5b39@forums-1-dub> <4d519044@forums-1-dub>
Subject: Re: Querying Data without Valid User Id
Lines: 58
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
X-RFC2646: Format=Flowed; Response
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4d5288d8@forums-1-dub>
Date: 9 Feb 2011 04:30:16 -0800
X-Trace: forums-1-dub 1297254616 10.22.241.152 (9 Feb 2011 04:30:16 -0800)
X-Original-Trace: 9 Feb 2011 04:30:16 -0800, vip152.sybase.com
X-Authenticated-User: teamsybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29942
Article PK: 79169


> On 2/6/2011 7:38 PM, Jamal wrote:
>> Hi,
>>
>> I am working on a requirement where I need to lock a user after specified
>> number of failed login attempts and update user table of my application .
>> It
>> is also required to give warning message about number if failed login
>> attempts left after each failed login attempt.
>>
>> I can configure this by enabling the Auditing in Sybase and limit the Max
>> Failed Login attempts but I wouldn't know how many failed login attempts
>> are
>> left. Also when user login is locked, Sybase gives my only generic error
>> code (4002) which is same of Invalid User/Password.
>>
>> Solution in my mind is to Create a dedicated user login, hard coded
>> somewhere in my application and user that connection to get the
>> information
>> from database i.e. User status from syslogins, Number of available failed
>> login attempts. This hard coding to user login may not be acceptable to
>> our
>> clients and I am also a bit reluctant to use it unless there is no other
>> solution.
>>
>> I am wondering if anybody has implements this type of requirement and
>> how?
>>
>> We are using Sybase ASE 15 and PowerBuilder 11.5.
>>
>> I appreciate any comments.
>>

If you are using ASE 15.0.2 or later version you can make use of new option
included in sp_modifylogin proc. Setting value of option "max failed_logins"
to -1
will update logincount column of syslogins table for each failed login
attempt for the respective login id. However doing so will not actually lock
any logins and therefore
you might want to code the logic in a login trigger, update any user defined
table to set a flag that the login has reached max no of failed attempt and
can be locked

sp_modifylogin account, "max failed_logins", -1


-HTH
Manish Negandhi
[TeamSybase]







sp_modifylogin account, "max failed_logins", -1


Jamal Posted on 2011-02-19 02:49:31.0Z
From: "Jamal" <jahmad77@optusnet.com.au>
Newsgroups: sybase.public.ase.general
References: <4d4f5b39@forums-1-dub> <4d519044@forums-1-dub> <4d5288d8@forums-1-dub>
Subject: Re: Querying Data without Valid User Id
Lines: 70
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5931
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5994
X-RFC2646: Format=Flowed; Response
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4d5f2fbb@forums-1-dub>
Date: 18 Feb 2011 18:49:31 -0800
X-Trace: forums-1-dub 1298083771 10.22.241.152 (18 Feb 2011 18:49:31 -0800)
X-Original-Trace: 18 Feb 2011 18:49:31 -0800, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29990
Article PK: 79219

Thanks Manish.
As far I know Login triggers execute only after successful login. Is there
anything we need to set to run them in case if invalid login?

"Manish Negandhi [TeamSybase]" <negandhi.manish.nospam@gmail.com> wrote in
message news:4d5288d8@forums-1-dub...
>> On 2/6/2011 7:38 PM, Jamal wrote:
>>> Hi,
>>>
>>> I am working on a requirement where I need to lock a user after
>>> specified
>>> number of failed login attempts and update user table of my application
>>> . It
>>> is also required to give warning message about number if failed login
>>> attempts left after each failed login attempt.
>>>
>>> I can configure this by enabling the Auditing in Sybase and limit the
>>> Max
>>> Failed Login attempts but I wouldn't know how many failed login attempts
>>> are
>>> left. Also when user login is locked, Sybase gives my only generic error
>>> code (4002) which is same of Invalid User/Password.
>>>
>>> Solution in my mind is to Create a dedicated user login, hard coded
>>> somewhere in my application and user that connection to get the
>>> information
>>> from database i.e. User status from syslogins, Number of available
>>> failed
>>> login attempts. This hard coding to user login may not be acceptable to
>>> our
>>> clients and I am also a bit reluctant to use it unless there is no other
>>> solution.
>>>
>>> I am wondering if anybody has implements this type of requirement and
>>> how?
>>>
>>> We are using Sybase ASE 15 and PowerBuilder 11.5.
>>>
>>> I appreciate any comments.
>>>
>
> If you are using ASE 15.0.2 or later version you can make use of new
> option included in sp_modifylogin proc. Setting value of option "max
> failed_logins" to -1
> will update logincount column of syslogins table for each failed login
> attempt for the respective login id. However doing so will not actually
> lock any logins and therefore
> you might want to code the logic in a login trigger, update any user
> defined table to set a flag that the login has reached max no of failed
> attempt and can be locked
>
> sp_modifylogin account, "max failed_logins", -1
>
>
> -HTH
> Manish Negandhi
> [TeamSybase]
>
>
>
>
>
>
>
> sp_modifylogin account, "max failed_logins", -1
>
>


"Mark A. Parsons" <iron_horse Posted on 2011-02-19 15:16:49.0Z
From: "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.2.13) Gecko/20101207 Thunderbird/3.1.7
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Querying Data without Valid User Id
References: <4d4f5b39@forums-1-dub> <4d519044@forums-1-dub> <4d5288d8@forums-1-dub> <4d5f2fbb@forums-1-dub>
In-Reply-To: <4d5f2fbb@forums-1-dub>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4d5fdee1$1@forums-1-dub>
Date: 19 Feb 2011 07:16:49 -0800
X-Trace: forums-1-dub 1298128609 10.22.241.152 (19 Feb 2011 07:16:49 -0800)
X-Original-Trace: 19 Feb 2011 07:16:49 -0800, vip152.sybase.com
Lines: 157
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29991
Article PK: 79220

If a login is locked then you are correct, a login trigger will not fire.

If a login's password has expired, a login trigger will not fire.

If invalid login credentials (eg, wrong login id or password) are provided, a login trigger will not fire.

If the login is not locked and valid login credentials are supplied then the login trigger will fire.

Instead of actually locking a login you could a) leave the login unlocked and b) let the login trigger disallow a login
session based on whatever criteria you wish. Is essence the login trigger could be coded to simulate a locked login.

------

As you've (probably) discovered ...

- attempting to login into a locked login, or providing invalid login credentials, merely sends an error message (eg,
Msg 4002) back to the client; this obviously doesn't meet your design criteria

- login triggers can't pass any information back to the client application; the output from a SELECT statement is
silently suppressed; the output from PRINT/RAISERROR statements are dumped to the dataserver's errorlog

... so getting a message back to the client is going to require you design/code your own solution.

- accessing the dataserver errorlog (eg, to retrieve login trigger PRINT/RAISERROR output) is problematic (ie, probably
don't want to setup application access to the errorlog; would have to parse/sort the errorlog - which could be huge -
for a message that may not exist)

- using XP server to dump login details to an OS file (accessible by the application) would probably work, but you'll
need to address potential security holes in using XP server; you'll also need to configure your network so that the
application and XP server have access to the same directory where the detail file(s) reside

- you could dump the login details to a proxy table that points at an OS directory or file; this should be fairly secure
from a dataserver perspective as long as you're just writing to the proxy table; you'll still need to work out the
networking details to allow the application access to the same directory/file referenced by the proxy table; and
depending on your ASE version you may need to get an additional license to support the proxy-table-to-OS-directory/file
feature

- limiting a secondary login's authority to a specific database and specific data access could lead to holes in security
if not implemented correctly, especially if you can't secure the secondary login's password

- replicating (via repserver, replicator, home-grown CIS method) login details to another dataserver (where a secondary
login could obtain login details) would probably work but would require the setup/maintenance of the 2nd dataserver and
replication components ... not to mention any additional costs for licensing issues

------

I've saved a somewhat convoluted, but fairly secure and easy to implement idea for last ...

- create a secondary login

- expire the password for the secondary login; with 15.0.2 this is pretty easy with 'sp_passwordpolicy "expire login
passwords",<login>'

NOTE: With ASE 12.5.4/15.0.2 the DBA has the option of calling a DBA-specified stored proc to perform extra password
checks whenever a password is created or modified. This feature is implemented via the creation of a stored proc named
'sp_extrapwdchecks' in the master database.

- in the master database 'create procedure sp_extrapwdchecks ....'; when suser_name()=<secondary_login> you perform the
desired login/password processing that you're looking for; assume the second input parameter (@new_password) contains
the name of the login you wish to get password details for; once you've sent the desired details back to the client
application (via SELECT/PRINT/RAISERROR), perform a 'select syb_quit()' - this will leave the secondary login's password
as EXPIRED; for all other suser_name()!=<secondary_login> perform whatever additional (if any) password checks you want

- when the application login fails, reconnect to the dataserver with the secondary login; when the secondary login's
attempt to connect generates a 7742 (password expired, must change password), submit 'sp_password
<secondary_login_current_password>,<login_you_want_to_lookup>' to the dataserver; the sp_extrpwdchecks stored proc then
uses @new_password (aka <login_you_want_to_lookup>) to perform your special coding because suser_name()=<secondary_login>

NOTE: obviously if the application login fails with a 7742 (password expired, must change password), you'll want to
process this just like you would normally process an expired password; because you've compartmentalized your special
login/password logic to the <secondary_login> login, said logic won't get applied when the application login (ie,
suser_name()!=<secondary_login>) triggers a call of the sp_extrapwdchecks stored proc

As long as the secondary login has an expired password we can trigger the ASE's special handling of expired passwords,
ie, allow a login but limit access to running the sp_password stored proc.

NOTE: While we could modify the logic in sp_password, it's actually easier/safer if we hijack the ASE's extra password
processing capability (via special coding in the sp_extrapwdchecks stored proc).

Since there are no limits on what sp_extrapwdchecks can send back to the client, we can use SELECT/PRINT/RAISERROR to
send any details we want back to the client application.

Because we're terminating the secondary login's connection (via 'select syb_quit()') before its password is actually
reset, we don't have to worry about someone finding the secondary login's password. Any attempt to login with the
secondary login's password will trigger the 7742 (password expired, must change password). And if a call is made to
sp_password our special logic in sp_extrapwdchecks will, in essence, disable the changing of the (expired) password for
the secondary login and automatically disconnect the session.

Obviously there's always the chance for mischief if someone obtains the secondary login's password *and* one of the
following occurs:

a - secondary login is 'unexpired'

b - secondary login is expired but sp_extrapwdchecks is dropped or modified so that the special secondary login logic
disappears

On 02/18/2011 21:49, Jamal wrote:
> Thanks Manish.
> As far I know Login triggers execute only after successful login. Is there
> anything we need to set to run them in case if invalid login?
>
>
> "Manish Negandhi [TeamSybase]"<negandhi.manish.nospam@gmail.com> wrote in
> message news:4d5288d8@forums-1-dub...
>>> On 2/6/2011 7:38 PM, Jamal wrote:
>>>> Hi,
>>>>
>>>> I am working on a requirement where I need to lock a user after
>>>> specified
>>>> number of failed login attempts and update user table of my application
>>>> . It
>>>> is also required to give warning message about number if failed login
>>>> attempts left after each failed login attempt.
>>>>
>>>> I can configure this by enabling the Auditing in Sybase and limit the
>>>> Max
>>>> Failed Login attempts but I wouldn't know how many failed login attempts
>>>> are
>>>> left. Also when user login is locked, Sybase gives my only generic error
>>>> code (4002) which is same of Invalid User/Password.
>>>>
>>>> Solution in my mind is to Create a dedicated user login, hard coded
>>>> somewhere in my application and user that connection to get the
>>>> information
>>>> from database i.e. User status from syslogins, Number of available
>>>> failed
>>>> login attempts. This hard coding to user login may not be acceptable to
>>>> our
>>>> clients and I am also a bit reluctant to use it unless there is no other
>>>> solution.
>>>>
>>>> I am wondering if anybody has implements this type of requirement and
>>>> how?
>>>>
>>>> We are using Sybase ASE 15 and PowerBuilder 11.5.
>>>>
>>>> I appreciate any comments.
>>>>
>>
>> If you are using ASE 15.0.2 or later version you can make use of new
>> option included in sp_modifylogin proc. Setting value of option "max
>> failed_logins" to -1
>> will update logincount column of syslogins table for each failed login
>> attempt for the respective login id. However doing so will not actually
>> lock any logins and therefore
>> you might want to code the logic in a login trigger, update any user
>> defined table to set a flag that the login has reached max no of failed
>> attempt and can be locked
>>
>> sp_modifylogin account, "max failed_logins", -1
>>
>>
>> -HTH
>> Manish Negandhi
>> [TeamSybase]


Jamal Posted on 2011-02-20 13:07:18.0Z
From: "Jamal" <jahmad77@optusnet.com.au>
Newsgroups: sybase.public.ase.general
References: <4d4f5b39@forums-1-dub> <4d519044@forums-1-dub> <4d5288d8@forums-1-dub> <4d5f2fbb@forums-1-dub> <4d5fdee1$1@forums-1-dub>
Subject: Re: Querying Data without Valid User Id
Lines: 219
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5931
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5994
X-RFC2646: Format=Flowed; Response
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4d611206$1@forums-1-dub>
Date: 20 Feb 2011 05:07:18 -0800
X-Trace: forums-1-dub 1298207238 10.22.241.152 (20 Feb 2011 05:07:18 -0800)
X-Original-Trace: 20 Feb 2011 05:07:18 -0800, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29996
Article PK: 79224

Thanks Mark. This is really a good solution.
I am already creating sp_extrapwdchecks to maintain password history and
restrict to reuse last 5 password.

One thing how I am gonna read information from application's database as
sp_extrapwdchecks will stored in master database. I can't hardcode the
database name. I haven't tried this solution yet may be I need to store
information in master database.

Any way thanks for this. I really appreciate your effort.

"Mark A. Parsons" <iron_horse@no_spamola.compuserve.com> wrote in message
news:4d5fdee1$1@forums-1-dub...
> If a login is locked then you are correct, a login trigger will not fire.
>
> If a login's password has expired, a login trigger will not fire.
>
> If invalid login credentials (eg, wrong login id or password) are
> provided, a login trigger will not fire.
>
> If the login is not locked and valid login credentials are supplied then
> the login trigger will fire.
>
> Instead of actually locking a login you could a) leave the login unlocked
> and b) let the login trigger disallow a login session based on whatever
> criteria you wish. Is essence the login trigger could be coded to
> simulate a locked login.
>
> ------
>
> As you've (probably) discovered ...
>
> - attempting to login into a locked login, or providing invalid login
> credentials, merely sends an error message (eg, Msg 4002) back to the
> client; this obviously doesn't meet your design criteria
>
> - login triggers can't pass any information back to the client
> application; the output from a SELECT statement is silently suppressed;
> the output from PRINT/RAISERROR statements are dumped to the dataserver's
> errorlog
>
> ... so getting a message back to the client is going to require you
> design/code your own solution.
>
> - accessing the dataserver errorlog (eg, to retrieve login trigger
> PRINT/RAISERROR output) is problematic (ie, probably don't want to setup
> application access to the errorlog; would have to parse/sort the
> errorlog - which could be huge - for a message that may not exist)
>
> - using XP server to dump login details to an OS file (accessible by the
> application) would probably work, but you'll need to address potential
> security holes in using XP server; you'll also need to configure your
> network so that the application and XP server have access to the same
> directory where the detail file(s) reside
>
> - you could dump the login details to a proxy table that points at an OS
> directory or file; this should be fairly secure from a dataserver
> perspective as long as you're just writing to the proxy table; you'll
> still need to work out the networking details to allow the application
> access to the same directory/file referenced by the proxy table; and
> depending on your ASE version you may need to get an additional license to
> support the proxy-table-to-OS-directory/file feature
>
> - limiting a secondary login's authority to a specific database and
> specific data access could lead to holes in security if not implemented
> correctly, especially if you can't secure the secondary login's password
>
> - replicating (via repserver, replicator, home-grown CIS method) login
> details to another dataserver (where a secondary login could obtain login
> details) would probably work but would require the setup/maintenance of
> the 2nd dataserver and replication components ... not to mention any
> additional costs for licensing issues
>
> ------
>
> I've saved a somewhat convoluted, but fairly secure and easy to implement
> idea for last ...
>
> - create a secondary login
>
> - expire the password for the secondary login; with 15.0.2 this is pretty
> easy with 'sp_passwordpolicy "expire login passwords",<login>'
>
> NOTE: With ASE 12.5.4/15.0.2 the DBA has the option of calling a
> DBA-specified stored proc to perform extra password checks whenever a
> password is created or modified. This feature is implemented via the
> creation of a stored proc named 'sp_extrapwdchecks' in the master
> database.
>
> - in the master database 'create procedure sp_extrapwdchecks ....'; when
> suser_name()=<secondary_login> you perform the desired login/password
> processing that you're looking for; assume the second input parameter
> (@new_password) contains the name of the login you wish to get password
> details for; once you've sent the desired details back to the client
> application (via SELECT/PRINT/RAISERROR), perform a 'select syb_quit()' -
> this will leave the secondary login's password as EXPIRED; for all other
> suser_name()!=<secondary_login> perform whatever additional (if any)
> password checks you want
>
> - when the application login fails, reconnect to the dataserver with the
> secondary login; when the secondary login's attempt to connect generates a
> 7742 (password expired, must change password), submit 'sp_password
> <secondary_login_current_password>,<login_you_want_to_lookup>' to the
> dataserver; the sp_extrpwdchecks stored proc then uses @new_password (aka
> <login_you_want_to_lookup>) to perform your special coding because
> suser_name()=<secondary_login>
>
> NOTE: obviously if the application login fails with a 7742 (password
> expired, must change password), you'll want to process this just like you
> would normally process an expired password; because you've
> compartmentalized your special login/password logic to the
> <secondary_login> login, said logic won't get applied when the application
> login (ie, suser_name()!=<secondary_login>) triggers a call of the
> sp_extrapwdchecks stored proc
>
> As long as the secondary login has an expired password we can trigger the
> ASE's special handling of expired passwords, ie, allow a login but limit
> access to running the sp_password stored proc.
>
> NOTE: While we could modify the logic in sp_password, it's actually
> easier/safer if we hijack the ASE's extra password processing capability
> (via special coding in the sp_extrapwdchecks stored proc).
>
> Since there are no limits on what sp_extrapwdchecks can send back to the
> client, we can use SELECT/PRINT/RAISERROR to send any details we want back
> to the client application.
>
> Because we're terminating the secondary login's connection (via 'select
> syb_quit()') before its password is actually reset, we don't have to worry
> about someone finding the secondary login's password. Any attempt to
> login with the secondary login's password will trigger the 7742 (password
> expired, must change password). And if a call is made to sp_password our
> special logic in sp_extrapwdchecks will, in essence, disable the changing
> of the (expired) password for the secondary login and automatically
> disconnect the session.
>
> Obviously there's always the chance for mischief if someone obtains the
> secondary login's password *and* one of the following occurs:
>
> a - secondary login is 'unexpired'
>
> b - secondary login is expired but sp_extrapwdchecks is dropped or
> modified so that the special secondary login logic disappears
>
>
>
> On 02/18/2011 21:49, Jamal wrote:
>> Thanks Manish.
>> As far I know Login triggers execute only after successful login. Is
>> there
>> anything we need to set to run them in case if invalid login?
>>
>>
>> "Manish Negandhi [TeamSybase]"<negandhi.manish.nospam@gmail.com> wrote
>> in
>> message news:4d5288d8@forums-1-dub...
>>>> On 2/6/2011 7:38 PM, Jamal wrote:
>>>>> Hi,
>>>>>
>>>>> I am working on a requirement where I need to lock a user after
>>>>> specified
>>>>> number of failed login attempts and update user table of my
>>>>> application
>>>>> . It
>>>>> is also required to give warning message about number if failed login
>>>>> attempts left after each failed login attempt.
>>>>>
>>>>> I can configure this by enabling the Auditing in Sybase and limit the
>>>>> Max
>>>>> Failed Login attempts but I wouldn't know how many failed login
>>>>> attempts
>>>>> are
>>>>> left. Also when user login is locked, Sybase gives my only generic
>>>>> error
>>>>> code (4002) which is same of Invalid User/Password.
>>>>>
>>>>> Solution in my mind is to Create a dedicated user login, hard coded
>>>>> somewhere in my application and user that connection to get the
>>>>> information
>>>>> from database i.e. User status from syslogins, Number of available
>>>>> failed
>>>>> login attempts. This hard coding to user login may not be acceptable
>>>>> to
>>>>> our
>>>>> clients and I am also a bit reluctant to use it unless there is no
>>>>> other
>>>>> solution.
>>>>>
>>>>> I am wondering if anybody has implements this type of requirement and
>>>>> how?
>>>>>
>>>>> We are using Sybase ASE 15 and PowerBuilder 11.5.
>>>>>
>>>>> I appreciate any comments.
>>>>>
>>>
>>> If you are using ASE 15.0.2 or later version you can make use of new
>>> option included in sp_modifylogin proc. Setting value of option "max
>>> failed_logins" to -1
>>> will update logincount column of syslogins table for each failed login
>>> attempt for the respective login id. However doing so will not actually
>>> lock any logins and therefore
>>> you might want to code the logic in a login trigger, update any user
>>> defined table to set a flag that the login has reached max no of failed
>>> attempt and can be locked
>>>
>>> sp_modifylogin account, "max failed_logins", -1
>>>
>>>
>>> -HTH
>>> Manish Negandhi
>>> [TeamSybase]


"Mark A. Parsons" <iron_horse Posted on 2011-02-20 13:33:55.0Z
From: "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.2.13) Gecko/20101207 Thunderbird/3.1.7
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Querying Data without Valid User Id
References: <4d4f5b39@forums-1-dub> <4d519044@forums-1-dub> <4d5288d8@forums-1-dub> <4d5f2fbb@forums-1-dub> <4d5fdee1$1@forums-1-dub> <4d611206$1@forums-1-dub>
In-Reply-To: <4d611206$1@forums-1-dub>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4d611843$1@forums-1-dub>
Date: 20 Feb 2011 05:33:55 -0800
X-Trace: forums-1-dub 1298208835 10.22.241.152 (20 Feb 2011 05:33:55 -0800)
X-Original-Trace: 20 Feb 2011 05:33:55 -0800, vip152.sybase.com
Lines: 290
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29997
Article PK: 79228

I'd probably stay away from storing application data in the master database. Sure, it can be done, but you really don't
want to bog down the master database with application data (and logging).

How many application databases do you need to access? Will the application database(s) that you need to access vary
based on the login you're processing.

If the names of the databases won't vary, just code them in your sp_extrapwdchecks stored proc, eg, "select <stuff> from
app_db1..source_data_table where ....".

If the database name will vary based on the login name, you have a couple options:

1 - build the desired SQL query and submit to the dataserver via the exec immediate capability, eg:

========================
declare @cmd varchar(5000)

-- build SELECT statement from login-specific details

select @cmd = 'select <stuff> from ' + source_db_name +
'..' + source_tab_name +

-- add whatever WHERE clauses you need to
-- obtain the desired data

' where login = "' + @new_password + '"'

-- assume source database/table names are mapped to individual logins
-- and stored in the resource..login_details table

from resource..login_details
where login = @new_password

-- submit query to dataserver

exec (@cmd)
========================

2 - create a proc in each application database which will perform the desired queries; then call the appropriate proc
based on the database you need to access, eg:

========================
declare @dbname varchar(30), @full_proc_reference varchar(300)

-- assume source database name is stored in a table (db: resource;
-- table: login_details) and accessed by the name of the login
-- (aka @new_password) you're processing

select @dbname = source_dbname
from resource..login_details
where @login = @new_password /* aka login name */

-- assume proc name is same in each database; proc is called 'get_the_login_data'

select @full_proc_reference = @dbname + '..get_the_login_data

@exec @full_proc_reference @new_password /* aka login */
========================

Alternatively, if the proc has the same exact SQL in each source database, create a single proc in sybsystemprocs with a
name that begins with 'sp_'. This means you maintain just one stored proc, and the above becomes:

select @full_proc_reference = @dbname + '..sp_get_the_login_data'

----------------

My preference would be option #2, especially if you have different queries/requirements in the different source
databases. Putting the query details into subordinate procs means less coding in sp_extrapwdchecks, ie,
sp_extrapwdchecks remains cleaner/easier to read.

On 02/20/2011 08:07, Jamal wrote:
> Thanks Mark. This is really a good solution.
> I am already creating sp_extrapwdchecks to maintain password history and
> restrict to reuse last 5 password.
>
> One thing how I am gonna read information from application's database as
> sp_extrapwdchecks will stored in master database. I can't hardcode the
> database name. I haven't tried this solution yet may be I need to store
> information in master database.
>
> Any way thanks for this. I really appreciate your effort.
>
>
>
>
>
>
> "Mark A. Parsons"<iron_horse@no_spamola.compuserve.com> wrote in message
> news:4d5fdee1$1@forums-1-dub...
>> If a login is locked then you are correct, a login trigger will not fire.
>>
>> If a login's password has expired, a login trigger will not fire.
>>
>> If invalid login credentials (eg, wrong login id or password) are
>> provided, a login trigger will not fire.
>>
>> If the login is not locked and valid login credentials are supplied then
>> the login trigger will fire.
>>
>> Instead of actually locking a login you could a) leave the login unlocked
>> and b) let the login trigger disallow a login session based on whatever
>> criteria you wish. Is essence the login trigger could be coded to
>> simulate a locked login.
>>
>> ------
>>
>> As you've (probably) discovered ...
>>
>> - attempting to login into a locked login, or providing invalid login
>> credentials, merely sends an error message (eg, Msg 4002) back to the
>> client; this obviously doesn't meet your design criteria
>>
>> - login triggers can't pass any information back to the client
>> application; the output from a SELECT statement is silently suppressed;
>> the output from PRINT/RAISERROR statements are dumped to the dataserver's
>> errorlog
>>
>> ... so getting a message back to the client is going to require you
>> design/code your own solution.
>>
>> - accessing the dataserver errorlog (eg, to retrieve login trigger
>> PRINT/RAISERROR output) is problematic (ie, probably don't want to setup
>> application access to the errorlog; would have to parse/sort the
>> errorlog - which could be huge - for a message that may not exist)
>>
>> - using XP server to dump login details to an OS file (accessible by the
>> application) would probably work, but you'll need to address potential
>> security holes in using XP server; you'll also need to configure your
>> network so that the application and XP server have access to the same
>> directory where the detail file(s) reside
>>
>> - you could dump the login details to a proxy table that points at an OS
>> directory or file; this should be fairly secure from a dataserver
>> perspective as long as you're just writing to the proxy table; you'll
>> still need to work out the networking details to allow the application
>> access to the same directory/file referenced by the proxy table; and
>> depending on your ASE version you may need to get an additional license to
>> support the proxy-table-to-OS-directory/file feature
>>
>> - limiting a secondary login's authority to a specific database and
>> specific data access could lead to holes in security if not implemented
>> correctly, especially if you can't secure the secondary login's password
>>
>> - replicating (via repserver, replicator, home-grown CIS method) login
>> details to another dataserver (where a secondary login could obtain login
>> details) would probably work but would require the setup/maintenance of
>> the 2nd dataserver and replication components ... not to mention any
>> additional costs for licensing issues
>>
>> ------
>>
>> I've saved a somewhat convoluted, but fairly secure and easy to implement
>> idea for last ...
>>
>> - create a secondary login
>>
>> - expire the password for the secondary login; with 15.0.2 this is pretty
>> easy with 'sp_passwordpolicy "expire login passwords",<login>'
>>
>> NOTE: With ASE 12.5.4/15.0.2 the DBA has the option of calling a
>> DBA-specified stored proc to perform extra password checks whenever a
>> password is created or modified. This feature is implemented via the
>> creation of a stored proc named 'sp_extrapwdchecks' in the master
>> database.
>>
>> - in the master database 'create procedure sp_extrapwdchecks ....'; when
>> suser_name()=<secondary_login> you perform the desired login/password
>> processing that you're looking for; assume the second input parameter
>> (@new_password) contains the name of the login you wish to get password
>> details for; once you've sent the desired details back to the client
>> application (via SELECT/PRINT/RAISERROR), perform a 'select syb_quit()' -
>> this will leave the secondary login's password as EXPIRED; for all other
>> suser_name()!=<secondary_login> perform whatever additional (if any)
>> password checks you want
>>
>> - when the application login fails, reconnect to the dataserver with the
>> secondary login; when the secondary login's attempt to connect generates a
>> 7742 (password expired, must change password), submit 'sp_password
>> <secondary_login_current_password>,<login_you_want_to_lookup>' to the
>> dataserver; the sp_extrpwdchecks stored proc then uses @new_password (aka
>> <login_you_want_to_lookup>) to perform your special coding because
>> suser_name()=<secondary_login>
>>
>> NOTE: obviously if the application login fails with a 7742 (password
>> expired, must change password), you'll want to process this just like you
>> would normally process an expired password; because you've
>> compartmentalized your special login/password logic to the
>> <secondary_login> login, said logic won't get applied when the application
>> login (ie, suser_name()!=<secondary_login>) triggers a call of the
>> sp_extrapwdchecks stored proc
>>
>> As long as the secondary login has an expired password we can trigger the
>> ASE's special handling of expired passwords, ie, allow a login but limit
>> access to running the sp_password stored proc.
>>
>> NOTE: While we could modify the logic in sp_password, it's actually
>> easier/safer if we hijack the ASE's extra password processing capability
>> (via special coding in the sp_extrapwdchecks stored proc).
>>
>> Since there are no limits on what sp_extrapwdchecks can send back to the
>> client, we can use SELECT/PRINT/RAISERROR to send any details we want back
>> to the client application.
>>
>> Because we're terminating the secondary login's connection (via 'select
>> syb_quit()') before its password is actually reset, we don't have to worry
>> about someone finding the secondary login's password. Any attempt to
>> login with the secondary login's password will trigger the 7742 (password
>> expired, must change password). And if a call is made to sp_password our
>> special logic in sp_extrapwdchecks will, in essence, disable the changing
>> of the (expired) password for the secondary login and automatically
>> disconnect the session.
>>
>> Obviously there's always the chance for mischief if someone obtains the
>> secondary login's password *and* one of the following occurs:
>>
>> a - secondary login is 'unexpired'
>>
>> b - secondary login is expired but sp_extrapwdchecks is dropped or
>> modified so that the special secondary login logic disappears
>>
>>
>>
>> On 02/18/2011 21:49, Jamal wrote:
>>> Thanks Manish.
>>> As far I know Login triggers execute only after successful login. Is
>>> there
>>> anything we need to set to run them in case if invalid login?
>>>
>>>
>>> "Manish Negandhi [TeamSybase]"<negandhi.manish.nospam@gmail.com> wrote
>>> in
>>> message news:4d5288d8@forums-1-dub...
>>>>> On 2/6/2011 7:38 PM, Jamal wrote:
>>>>>> Hi,
>>>>>>
>>>>>> I am working on a requirement where I need to lock a user after
>>>>>> specified
>>>>>> number of failed login attempts and update user table of my
>>>>>> application
>>>>>> . It
>>>>>> is also required to give warning message about number if failed login
>>>>>> attempts left after each failed login attempt.
>>>>>>
>>>>>> I can configure this by enabling the Auditing in Sybase and limit the
>>>>>> Max
>>>>>> Failed Login attempts but I wouldn't know how many failed login
>>>>>> attempts
>>>>>> are
>>>>>> left. Also when user login is locked, Sybase gives my only generic
>>>>>> error
>>>>>> code (4002) which is same of Invalid User/Password.
>>>>>>
>>>>>> Solution in my mind is to Create a dedicated user login, hard coded
>>>>>> somewhere in my application and user that connection to get the
>>>>>> information
>>>>>> from database i.e. User status from syslogins, Number of available
>>>>>> failed
>>>>>> login attempts. This hard coding to user login may not be acceptable
>>>>>> to
>>>>>> our
>>>>>> clients and I am also a bit reluctant to use it unless there is no
>>>>>> other
>>>>>> solution.
>>>>>>
>>>>>> I am wondering if anybody has implements this type of requirement and
>>>>>> how?
>>>>>>
>>>>>> We are using Sybase ASE 15 and PowerBuilder 11.5.
>>>>>>
>>>>>> I appreciate any comments.
>>>>>>
>>>>
>>>> If you are using ASE 15.0.2 or later version you can make use of new
>>>> option included in sp_modifylogin proc. Setting value of option "max
>>>> failed_logins" to -1
>>>> will update logincount column of syslogins table for each failed login
>>>> attempt for the respective login id. However doing so will not actually
>>>> lock any logins and therefore
>>>> you might want to code the logic in a login trigger, update any user
>>>> defined table to set a flag that the login has reached max no of failed
>>>> attempt and can be locked
>>>>
>>>> sp_modifylogin account, "max failed_logins", -1
>>>>
>>>>
>>>> -HTH
>>>> Manish Negandhi
>>>> [TeamSybase]
>
>