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

2 Replies to “Locking and Unlocking users in SAP Sybase IQ 15 and 16”

  1. 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. 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 *