Home » Databases » Locking and Unlocking users in SAP Sybase IQ 15 and 16

Locking and Unlocking users in SAP Sybase IQ 15 and 16

In IQ 12.x and below, we had the sp_iqlocklogins stored procedure that we could use to quickly lock and unlock user accounts. Starting in v15 of IQ, that stored procedure was replaced with a login policy.

First we need to create the locked_users policy (taken from sybooks:

CREATE LOGIN POLICY locked_users locked=ON;

Now we can simply assign the login(s) to the locked_users policy:

alter user darthvader login policy locked_users;

If at some point Darth has been a good boy or he has us in a force choke hold, we can unlock his login:

alter user darthvader login policy root;

The default login policy is root.

What if you have hundreds of users to lock and unlock?

We need to make sure we can undo locking the logins:

select 'alter user ' + sul.name 
   +  ' login policy ' + lp.login_policy_name + ';' 
from sys.sysuserlist sul, sys.sysuser su, sys.sysloginpolicy lp
where 
sul.user_group = 'N'
and sul.dbaauth = 'N'
and sul.name not in ('EXTENV_MAIN', 'EXTENV_WORKER')
and su.user_name = sul.name
and su.login_policy_id = lp.login_policy_id;

Produces unlock script:

alter user darthvader login policy root;
alter user bilbo login policy root;

Lock all non DBA role logins:

select 'alter user ' + name +  ' login policy locked_users;' 
from sysuserlist
where 
user_group = 'N'
and dbaauth = 'N'
and name not in ('EXTENV_MAIN', 'EXTENV_WORKER');

Produces lock script:

alter user darthvader login policy locked_users;
alter user bilbo login policy locked_users;
Share Button

Comments

  1. David Louie says:

    this is not working for me on IQ 15.4

    DEVEWD_IQ.sa..52.1> alter user tsgops login policy locked_users
    DEVEWD_IQ.sa..52.2> go
    DEVEWD_IQ.sa..53.1> quit
    ndhpdba001$sqsh -U tsgops -P “XXXXX” -S DEVEWD_IQ
    DEVEWD_IQ.tsgops..51.1>

  2. David Louie says:

    this is not working for me on IQ 15.4

    DEVEWD_IQ.sa..52.1> alter user tsgops login policy locked_users
    DEVEWD_IQ.sa..52.2> go
    DEVEWD_IQ.sa..53.1> quit
    ndhpdba001$sqsh -U tsgops -P “XXXXX” -S DEVEWD_IQ
    DEVEWD_IQ.tsgops..51.1>

    found the issue if dba authority is granted the policy is not enforceable!

Leave a Reply

Your email address will not be published. Required fields are marked *

*
*

Facebook login by WP-FB-AutoConnect