Lost sa_role bypass recovery

[SybDBA]

Hi All,

Few days ago I found a tricky bug on the below system which tends to me to do some research on this which resulted few interesting things :

1. In ASE 15.0.3 you can't revoke the roles from the user who is currently logged in.

2. In ASE 15.7 and later you can revoke the  roles from the user who is currently logged in, even from user itself who is logged in.

Like a 'sa' user who is currently logged in can revoke the roles from other users as well as from himself also.

Which I think should be clarified that what all the causes were there to implement this ??

For more detailed discussion refer my discussion over here revoke role not working Sybase ASE 15.7

*************************** BEGIN **********************************

Adaptive Server Enterprise/15.7/EBF 24639 SMP SP134

OS : SunOS IHYDSUNT3-02-Z11 5.10 Generic_150400-22 sun4v sparc sun4v

Situation:

I(sa) have revoked the 'sa_role' from all the users even from 'sa' also.

Now I was unable to grant 'sa_role' to any login or to myself(sa) also. Its a kind of disastrous situation for me.

Note : In ASE 15.7 you can revoke the 'sa_role' from all the users including 'sa' user. Not in ASE 15.0.3 or earlier.

Solution :

1 ) You must should have a login with 'sso_role' enabled.       (let's say the user is 'john')

2) Login into the server with that login only, suppose

$ isql -Ujohn -Ssybase157 -w999

Password: <put_password_here>

1> sp_activeroles

2> go

Role Name                   

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

oper_role                   

sybase_ts_role              

mon_role                    

sso_role                    

3) Find the system table called sysloginroles :

1> select suid, suser_name(suid) "ServerUser", srid, role_name(srid) "RoleName" from master..sysloginroles

2> go

suid        ServerUser                     srid        RoleName                    

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

           4 john                                   1 sso_role                    

           1 sa                                       2 oper_role                   

           1 sa                                       3 sybase_ts_role              

           1 sa                                      10 mon_role                    

           1 sa                                      32 sa_serverprivs_role         

           1 sa                                       1 sso_role                    

in the above table you can see that user 'john' having at least 'sso_role'....... fine.......means we have a chance to grant role to 'sa' or some other user.

Now what all you need to do is to alter the above table which can give you a catch....so......let's begin:

4) First enable configure the system tables:

1> sp_configure 'allow updates', 1

2> go

Parameter Name                 Default     Memory Used Config Value                                                                                                                                                                                                                                                    Run Value                                                                                                                                                                                                                                                       Unit                 Type              

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

allow updates to system tables           0           0            1                                                                                                                                                                                                                                                               1                                                                                                                                                                                                                                                    switch               dynamic           

(1 row affected)

Configuration option changed. ASE need not be rebooted since the option is dynamic.

Changing the value of 'allow updates to system tables' does not increase the amount of memory Adaptive Server uses.

(return status = 0)

As it shows in the messages that no need to reboot the server as it is a dynamic option.

So here we go....we are on the right path..........:)

5) Now get the 'sa' user's suid from the sysloginroles table, which is 1 and get the 'srid' (sever role id) for the 'sa_role' which is also 1

and two more parameters status which shows a numerical value (0 or 1),

Note : if the status is 0 means the role in the 'srid' column is revoked from the user respective to the role in the 'suid' column and vice versa for status 1.

6) So we got the 'srid' for 'sa_role' which is 0(can find from the table syssrvroles) and the 'suid' is 1 for 'sa' user.

7) Now what all we need to do is to insert the values for the 'sa_role' manually into the table sysloginroles for 'sa' user.

1> insert into sysloginroles values(1,0,1,NULL)

2> go

(return status = 0)

don't forget to disable 'allow updates' configuration:

1> sp_configure 'allow updates', 0

2> go

Now quit from the isql and login with 'sa' :

$ isql -Usa -Ssybase157 -w999

Password: <password_enter_here>

1> sp_activeroles

2> go

Role Name                   

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

sa_role                     

oper_role                   

sybase_ts_role              

mon_role                    

sso_role                    

sa_serverprivs_role        

**************************** END OF DOCUMENT ***************************

Kindly review this document and let me know if any correction required.

Thanks to #RajatSir for all the support to make this happen.... :smile:

Thanks & Regards

PKP