User login Locked?

[Clive Collie]

I need to get the information obtainable using the sa_get_user_status
procedure for all users. However it seems I can only do this if I am DBA. Or
administrative app has user called Admin who is not DBA but needs to do some
admin tasks like establish who has a locked password. My usual trick of
wrapping a command in a procedure owned by DBA but with permission for Admin
to execute doesn't work here because sa_get_user_status seems to react to
the DBA status of the person calling the procedure.

How can I get round this all and find who has a locked login without being
DBA?

Regards,
Clive

[unknown]

There is currently no way to get status information for all users when
connected as a non-DBA user. We have discussed some alternatives, but have
not yet come to an agreement on the best approach. We're continuing to
investigate.

SQL Anywhere Developer Community:
http://www.sybase.com/developer/library/sql-anywhere-techcorner
SQL Anywhere Blog Center: http://www.sybase.com/sqlanyblogs

Post by Clive Collie
I need to get the information obtainable using the sa_get_user_status
procedure for all users. However it seems I can only do this if I am DBA.
Or administrative app has user called Admin who is not DBA but needs to do
some admin tasks like establish who has a locked password. My usual trick
of wrapping a command in a procedure owned by DBA but with permission for
Admin to execute doesn't work here because sa_get_user_status seems to
react to the DBA status of the person calling the procedure.
How can I get round this all and find who has a locked login without being
DBA?
Regards,
Clive

...

[Clive Collie]

Thanks for the info Bruce.

There is something odd going on with the failed login attempts thing.

I have set my policy to 3 failed attempts. I then deliberately log in with
the wrong password 3 times. The forth time I get the failed because of
policy error message. Now I go and look at the user in Sybase Central Users
and Groups. Sure enough it tells me the login is locked, the correct number
of failed attempts, the reason for the lock and the time of the last
successful login.

However if I run SELECT * FROM SYS.SYSUSER where user_name = 'clive'

it shows 0 in the failed_login_attempts column and null in the
last_login_time column. Then it gets stranger. I can muck about for a while
re-running the query and doing I don't know what else and suddenly, at some
point in either time of after doing something, the data in the
failed_login_attempts and last_login_time cols appears correctly.

Bug or feature? All in all I seem to be prevented from accessing this
information. My logic for flagging this up is since the DB is used as an
embedded component I don't want end users using Sybase Central and certainly
I don't want them knowing any DBA logins. So either sa_get_user_status must
work as non-dba or SYS.SYSUSER must return the correct information in a
timely fashion.

ASA 11.0.0.1264

Regards
Clive



"Bruce Hay [Sybase]" <h_a_y~a_t~i_a_n_y_w_h_e_r_e~d_o_t~c_o_m> wrote in

Post by unknown
There is currently no way to get status information for all users when
connected as a non-DBA user. We have discussed some alternatives, but have
not yet come to an agreement on the best approach. We're continuing to
investigate.
http://www.sybase.com/developer/library/sql-anywhere-techcorner
SQL Anywhere Blog Center: http://www.sybase.com/sqlanyblogs

Post by Clive Collie
I need to get the information obtainable using the sa_get_user_status
procedure for all users. However it seems I can only do this if I am DBA.
Or administrative app has user called Admin who is not DBA but needs to do
some admin tasks like establish who has a locked password. My usual trick
of wrapping a command in a procedure owned by DBA but with permission for
Admin to execute doesn't work here because sa_get_user_status seems to
react to the DBA status of the person calling the procedure.
How can I get round this all and find who has a locked login without
being DBA?
Regards,
Clive

...

[anil k goel]

Clive,

The behaviour you are noticing with the catalog is as designed.

Some of the real time information is maintained in memory and flushed to the
catalog only at checkpoint. So querying the catalog shows the information as
of the last checkpoint. sa_get_user_status() is meant to display the in
memory information and, as Bruce said, we are looking into ways of making it
work as non-DBA through a DBA owned procedure.

In the meanwhile, before issuing the SEELCT * FROM SYSUSER, do an explicit
checkpoint and then you should see consistent behaviour. Let us know of any
difficulties.

-anil

Post by Clive Collie
Thanks for the info Bruce.
There is something odd going on with the failed login attempts thing.
I have set my policy to 3 failed attempts. I then deliberately log in with
the wrong password 3 times. The forth time I get the failed because of
policy error message. Now I go and look at the user in Sybase Central
Users and Groups. Sure enough it tells me the login is locked, the correct
number of failed attempts, the reason for the lock and the time of the
last successful login.
However if I run SELECT * FROM SYS.SYSUSER where user_name = 'clive'
it shows 0 in the failed_login_attempts column and null in the
last_login_time column. Then it gets stranger. I can muck about for a
while re-running the query and doing I don't know what else and suddenly,
at some point in either time of after doing something, the data in the
failed_login_attempts and last_login_time cols appears correctly.
Bug or feature? All in all I seem to be prevented from accessing this
information. My logic for flagging this up is since the DB is used as an
embedded component I don't want end users using Sybase Central and
certainly I don't want them knowing any DBA logins. So either
sa_get_user_status must work as non-dba or SYS.SYSUSER must return the
correct information in a timely fashion.
ASA 11.0.0.1264
Regards
Clive
"Bruce Hay [Sybase]" <h_a_y~a_t~i_a_n_y_w_h_e_r_e~d_o_t~c_o_m> wrote in

Post by unknown
There is currently no way to get status information for all users when
connected as a non-DBA user. We have discussed some alternatives, but
have not yet come to an agreement on the best approach. We're continuing
to investigate.
http://www.sybase.com/developer/library/sql-anywhere-techcorner
SQL Anywhere Blog Center: http://www.sybase.com/sqlanyblogs

Post by Clive Collie
I need to get the information obtainable using the sa_get_user_status
procedure for all users. However it seems I can only do this if I am DBA.
Or administrative app has user called Admin who is not DBA but needs to
do some admin tasks like establish who has a locked password. My usual
trick of wrapping a command in a procedure owned by DBA but with
permission for Admin to execute doesn't work here because
sa_get_user_status seems to react to the DBA status of the person calling
the procedure.
How can I get round this all and find who has a locked login without
being DBA?
Regards,
Clive

...