revoke role not working Sybase ASE 15.7

[SybDBA]

Hi Experts,

I have a unusual situation, where I am not able to implement the concept that 'you can't revoke role from the user who is currently logged in'.

1) I have two users 'sa' and 'john'

2) 'sa' has granted mon_role(for example) to 'john'

grant role mon_role to 'john'

3) now 'sa' and 'john' both logged in to the server

and 'john' can see the output of 'sp_activeroles', its showing that mon_role is active for 'john'

4) now if 'sa' fires the revoke role command from their side no error occurs,(even I am quite shocked that this can be done by 'john also')

1> revoke role mon_role from 'john'

2> go

(successfully executed on john's session, no error )

1> revoke role mon_role from 'john'

2> go

(successfully executed on sa's session, no error )

I am quite surprised to see this, am I doing something wrong ??

Please guide me on this.

will be Thankful, and appreciate your answers and suggestions.

---

with kind regards

DJ

[jayrijnbergen · Accepted solution]

Sybooks says:

• You can revoke a role from a user while the user is logged in. Adaptive Server verifies a user’s activated roles before performing access checks. 

However, not all role checks are performed all the time.

If the login that got the role revoked doesn't disconnect & reconnect it will still be able to do things requiring sa_role. (Same for granting the role, login must reconnect)

Example on ASE 15.7 SP122

session 1 login sa:

create login test_sa with passwd MyS3cretpw

go

grant role sa_role to test_sa

go

sessions 2 login test_sa:

select * from master..syslisteners -- normally not readable for non-sa

go

-- shows listener info

session 1:

revoke role sa_role from test_sa

go

-- role is revoked from test_sa

sessions 2:, login test_sa:

select * from master..syslisteners -- normally not readable for non-sa

go

-- still shows listener info, even without the sa_role granted

disconnect session 2, reconnect again with test_sa

select * from master..syslisteners

go

Select permission denied on some columns

[SybDBA · Accepted solution]

Hi Jeroen Rijnbergen,

As you said in the above post is a bit incorrect, as I tried in my ASE 15.7 environment, I found

1) If you revoke the role from the Session 1(sa), without disconnecting Session 2(test_sa) can see the change in 'sp_activeroles' despite of this you can execute the query like 'select * from master..syslisteners', while in case of grant you are right that Session 2 can't see the change in 'sp_activeroles' not even execute the query also without reconnecting.

2) In Sybooks written like this too, what should I accept true and what is false

• You cannot revoke a role from a user while the user is logged in.

http://infocenter.sybase.com/archive/index.jsp?topic=/com.sybase.infocenter.dc36272.1502/html/comman...

----

with kind regards

DJ

[jayrijnbergen · Accepted solution]

ah, well spotted in manual for 15.0

So in that case both versions work as documented

15.0: Sybooks says cannot revoke from the user logged in, and error is raised on 15.0

15.7: Sybooks says can revoke, no restrictions, and it works as documented on 15.7

[SybDBA · Accepted solution]

Hi Jeroen Rijnbergen,

Can you let me know in detail that why it has been removed from ASE 15.7 and was there in ASE 15.0.x ??

As my curiosity regarding this is quite high, that what was the drawback or benefit of this ??

----

with kind regards

DJ

[SybDBA]

Hey All, Jeroen Rijnbergen Maria Victoria NORMAND Eun-hee Ahn

Good Evening to all of you as per GMT+5:30

After a long time I want to revise this case again, because as per the conventions we need to check the issue on the latest patch as far as it is possible.

So the good time comes...@@@

I was able to revoke all the from 'sa' user.

Now no user available in the server having the and .

So request you all the to suggest me what to do next to regain the specified roles. As 'sa' is in the trouble.

4> select @@version

5> go

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

Adaptive Server Enterprise/15.7/EBF 24639 SMP SP134 /P/Sun_svr4/OS 5.10/ase157s

         p133x/3925/64-bit/FBO/Sat May  2 13:13:12 2015

here is the situation :

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

sybase157/ASE-15_0/install>isql -Usa -S syb157134 -w999  

Password:

1>

2>

3>

4> sp_displaylogin

5> go

Suid: 1                            

Loginame: sa

Fullname:

Default Database: master

Default Language:

Auto Login Script:

Configured Authorization:

        sa_role (default ON)

        sso_role (default ON)

        oper_role (default ON)

        sybase_ts_role (default ON)

        mon_role (default ON)

        js_admin_role (default ON)

        sa_serverprivs_role (default ON)

Locked: NO

Date of Last Password Change: Jun  9 2015  2:47PM

Password expiration interval: 0        

Password expired: NO                           

Minimum password length: 6        

Maximum failed logins: 0        

Current failed login attempts:

Authenticate with: AUTH_DEFAULT

Login Password Encryption: SHA-256

Last login date: Aug  7 2015  3:59PM

Exempt inactive lock: 0

(return status = 0)

1>

2>

3> revoke role sso_role from sa

4> go

1> sp_displaylogin

2> go

Suid: 1                            

Loginame: sa

Fullname:

Default Database: master

Default Language:

Auto Login Script:

Configured Authorization:

        sa_role (default ON)

        oper_role (default ON)

        sybase_ts_role (default ON)

        mon_role (default ON)

        js_admin_role (default ON)

        sa_serverprivs_role (default ON)

Locked: NO

Date of Last Password Change: Jun  9 2015  2:47PM

Password expiration interval: 0        

Password expired: NO                           

Minimum password length: 6        

Maximum failed logins: 0        

Current failed login attempts:

Authenticate with: AUTH_DEFAULT

Login Password Encryption: SHA-256

Last login date: Aug  7 2015  3:59PM

Exempt inactive lock: 0

(return status = 0)

1>

2>

3> revoke role sa_role from sa

4> go

1> sp_displaylogin

2> go

Suid: 1                            

Loginame: sa

Fullname:

Default Database: master

Default Language:

Auto Login Script:

Configured Authorization:

        oper_role (default ON)

        sybase_ts_role (default ON)

        mon_role (default ON)

        js_admin_role (default ON)

        sa_serverprivs_role (default ON)

Locked: NO

Date of Last Password Change: Jun  9 2015  2:47PM

Password expiration interval: 0        

Password expired: NO                           

Minimum password length: 6        

Maximum failed logins: 0        

Current failed login attempts:

Authenticate with: AUTH_DEFAULT

Login Password Encryption: SHA-256

Last login date: Aug  7 2015  3:59PM

Exempt inactive lock: 0

(return status = 0)

1>

2>

3> revoke role oper_role,sybase_ts_role,mon_role,js_admin_role,sa_serverprivs_role from sa

4> go

Msg 10353, Level 14, State 2:

Server 'syb157134', Line 3:

You must have any of the following role(s) to execute this command/procedure: 'sso_role' . Please contact a user with the appropriate role for help.

1> sp_displaylogin

2> go

Suid: 1                            

Loginame: sa

Fullname:

Default Database: master

Default Language:

Auto Login Script:

Configured Authorization:

        oper_role (default ON)

        sybase_ts_role (default ON)

        mon_role (default ON)

        js_admin_role (default ON)

        sa_serverprivs_role (default ON)

Locked: NO

Date of Last Password Change: Jun  9 2015  2:47PM

Password expiration interval: 0        

Password expired: NO                           

Minimum password length: 6        

Maximum failed logins: 0        

Current failed login attempts:

Authenticate with: AUTH_DEFAULT

Login Password Encryption: SHA-256

Last login date: Aug  7 2015  3:59PM

Exempt inactive lock: 0

(return status = 0)

1>

2>

3>

4>

5> grant role sso_role to sa

6> go

Msg 10353, Level 14, State 2:

Server 'syb157134', Line 5:

You must have any of the following role(s) to execute this command/procedure: 'sso_role' . Please contact a user with the appropriate role for help.

1>

2>

3> grant role sa_role to sa

4> go

Msg 10353, Level 14, State 6:

Server 'syb157134', Line 3:

You must have any of the following role(s) to execute this command/procedure: 'sa_role' . Please contact a user with the appropriate role for help.

1>

========================================================

Kindly let me know that, Is it a bug or anything else.

----

Regards

PKP

[former_member188958]

Yes, it is considered a bug that you can end up without any logins with sa_role or sso_role.  CR 786879  was opened for this a couple of weeks ago.  It isn't fixed yet, but an engineer is assigned to work on it.

Currently you will have to recover to your most recent dump of the master database.  If you don't have one, you will have to rebuild the server.

-bret

[SybDBA]

Thanks a ton... for your prompt response.

Hopefully it will be resolved in the next patches.

By the way what I need to do next?

Kindly explain the steps.

---

Regards

PKP

[former_member188958]

If you have a recent dump of the master database, follow the instructions in the SA Guide for restoring a master database from dump:  Recovering the master database

If you don't have a recent dump of master, do you have a login that can bcp out the major system tables from the master database?  If so, you can follow the method documented in the old 12.5 Troubleshooting Guide:  Manually Rebuilding Adaptive Server with bcp

If you can't even get the contents of the system tables, you can create a new server, use the DISK REINIT command to rebuild sysdevices using the device names from the server errorlog without overwriting those devices. Then use the DISK REFIT command which will rebuild the sysdatabases and sysusages tables by scanning the contents of the devices.  syslogins can then be rebuilt from account names in the sysusers tables of your user databases, but the paswords will be lost, you will have to assign new passwords.   There is still various small stuff to do after this (remote servers, remote logins, password security configurations, etc.), but the solid core will now be back up.

[SybDBA]

Thanks for your precious time on it...Bret Halford

I have done with bcp...out for all the important tables which includes

master..sysdevices
master..sysdatabases
master..sysusages
master..syslogins
master..sysconfigures
master..syscharsets
master..sysservers

master..sysremotelogins

as 'sa' still having below roles

oper_role (default ON)

sybase_ts_role (default ON)

mon_role (default ON)

js_admin_role (default ON)

sa_serverprivs_role (default ON)

what further need to be done.

as you suggested in the previous thread:

If you can't even get the contents of the system tables, you can create a new server, use the DISK REINIT command to rebuild sysdevices using the device names from the server errorlog without overwriting those devices. Then use the DISK REFIT command which will rebuild the sysdatabases and sysusages tables by scanning the contents of the devices.  syslogins can then be rebuilt from account names in the sysusers tables of your user databases, but the paswords will be lost, you will have to assign new passwords.   There is still various small stuff to do after this (remote servers, remote logins, password security configurations, etc.), but the solid core will now be back up.

Kindly describe it in detail as I am doing it all first time, and not getting properly.

++

----

Regards

PKP

[victoria_normand]

Hello,

This thread has been moved to SAP ASE for Custom Applications community as this question doesn't apply for SAP Applications running on ASE.

Regards,

Victoria.

[eahn]

Hi,

I've tested the following on ASE 15.7 SMP122.

SA_session -----------------------------------

1> sp_addlogin john,johnpw

2> go

Password correctly set.

Account unlocked.

New login created.

(return status = 0)

1> grant role mon_role to 'john'

2> go

John_session -----------------------------------

1> sp_displaylogin john

2> go

Suid: 15

Loginame: john

Fullname:

Default Database: master

Default Language:

Auto Login Script:

Configured Authorization:

        mon_role (default ON)

Locked: NO

Date of Last Password Change: Jun 22 2015  9:36AM

Password expiration interval: 0

Password expired: NO

Minimum password length: 6

Maximum failed logins: 0

Current failed login attempts: 0

Authenticate with: AUTH_DEFAULT

Login Password Encryption: SHA-256

Last login date: Jun 22 2015  9:37AM

Exempt inactive lock: 0

(return status = 0)

1> sp_activeroles

2> go

Role Name

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

mon_role

(1 row affected)

(return status = 0)

1> revoke role mon_role from 'john'

2> go

Msg 10353, Level 14, State 2:

Server 'RDS1', Line 1:

You must have any of the following role(s) to execute this command/procedure:

'sso_role' . Please contact a user with the appropriate role for help.

SA_session -----------------------------------

1> revoke role mon_role from 'john'

2> go

John_session ---------------------------------

1> sp_activeroles

2> go

Role Name

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

(0 rows affected)

(return status = 0)

SA_session ------------------------------

1>  revoke role mon_role from 'john'

2> go

Msg 11155, Level 16, State 1:

Operation failed. Role 'mon_role' has not been granted to user 'john'.

Is there any difference between the tests ?

If you provide more detailed procedures, I'll repro it.

Best regards,

Eunhee Ahn

[SybDBA]

Hi Eun-hee Ahn,

You have got the exact situation.

One more thing I want to add that

If I am granting the 'sso_role' to 'john', then 'john' will be able to revoke 'mon_role' (for example) from himself, which should not be.

( which is quite surprising in this case as well as in real life scenario )

Should I provide anything else on this ??

---

with kind regards,

DJ

[jayrijnbergen]

why do you expect that it's not possible to revoke roles from yourself?

if John has sso_role, then John can manage roles and assign and revoke them from any login, including himself.

[jayrijnbergen]

just noticed different behavior on different versions.

On 15.0 error 11166 is raised when trying to revoke sso_role from yourself

User 'testlg' is currently logged in. The revocation of roles from users that are currently logged in is not allowed.

On 15.7 the revoke works fine.

Message 11166 still exists on 15.7, not sure what would be expected behaviour

[eahn]

Hi Dwayne J.

I did the test in the same way you did

But that could not happen.

SA_session >>> 

1> sp_addlogin john4,john4pw

2> go

1> grant role sso_role, mon_role to john4

2> go

john4_session >>>

1> sp_activeroles

2> go

Role Name

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

sso_role

mon_role

1> revoke role mon_role from john4

2> go

SA_session >>> 

1> revoke role mon_role from john4

2> go

Msg 11155, Level 16, State 1:

Server 'RDS1', Line 1:

Operation failed. Role 'mon_role' has not been granted to user 'john4'.

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

SA_session >>>

1> sp_addlogin john5,john5pw

2> go

1> grant role sso_role, mon_role to john5

2> go

john5_sesion >>>

isql -Ujohn5 -Pjohn5pw

SA_session >>>

1> revoke role mon_role from john5

2> go

john5_session >>>

1> revoke role mon_role from john5

2> go

Msg 11155, Level 16, State 1:

Server 'RDS1', Line 1:

Operation failed. Role 'mon_role' has not been granted to user 'john5'.

I did this on Adaptive Server Enterprise/15.7/EBF 22779 SMP SP122 /P/x86_64/Enterprise Linux.

Which version of ASE are you using ?

If your ASE is lower version than mine, that seems to be fixed.

[SybDBA]

Hi Eun-hee,

Thanks for consideration over this post...!!!

I had done a detailed R&D on this topic yesterday and found the catch of it.

As you said in above post that you have tried this on ASE 15.7

There are different behaviors in this case of ASE 15.0.x and ASE 15.7

1) In ASE 15.7 you can revoke the roles from itself.

2) In ASE 15.7 you can revoke the roles from the users who are logged in currently.

But both the above points opposite in the ASE 15.0.x

1) In ASE 15.0.x you can't revoke the roles from itself.

2) In ASE 15.0.x you can't revoke the roles from the users who are logged in currently.

I have got these points after the above post by Jeroen Rijnbergen

just noticed different behavior on different versions.

On 15.0 error 11166 is raised when trying to revoke sso_role from yourself

User 'testlg' is currently logged in. The revocation of roles from users that are currently logged in is not allowed.

On 15.7 the revoke works fine.

Message 11166 still exists on 15.7, not sure what would be expected behavior

I felt the same, after research.

You can share the points which you found during the tests.

Thanks

----

with kind regards

DJ

[SybDBA]

Sorry Jeroen,

The problem may occur in this case,

1) if an user(suppose 'sa' ) who is having sso_role can revoke or grant the roles to himself, then if by mistake he has revoked its own sso_role, then what will happen??

what will be the solution of it ??

as i have depicted in the below figures, in ASE 15.7, it allows to do this

fig. 1: have granted roles

fig 2: allows to revoke the role, from itself(even sso_role)

please explain it...!!

Thanks

-----------

with kind regards

DJ

[jayrijnbergen]

I can't find it so quickly, but I think there's a restriction that you can't revoke sso_role if this is the only login left having the sso_role granted (but I've never tested that scenario myself)

If all logins with sso_role are locked out, you can restart your server with extra options to list all the logins with system roles granted (-A systemrole or --role-logins), and with other option ( -p login ) you can reset and unlock a login with system roles.

Why this behavior was changed I don't know.

Personally I prefer the way it works on 15.7

[jayrijnbergen]

note with granular permissions enabled on 15.7 it gets a bit more complicated.

in that case you need a login with permission 'manage roles'

if you have this permission granted, you can grant any role to any login.

it's even possible to grant sa_role *without* having sa_role, but I think that's a bug (I've opened an incident for that already)

[SybDBA]

Hi Experts,

Its a horrible situation for me, Kindly guide me to get the 'sso_role' back

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

ASE Version : Adaptive Server Enterprise/15.7.0/EBF 19497 SMP /P/Sun_svr4/OS 5.10/ase157/2820/64-bit/FBO/Fri Sep 16 05:21:18 2011

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

SESSION 1: (pankaj user)

/sybase>isql -Upankaj -SSYBASE157 -w999

Password:

1>

2>

3>

4> sp_activeroles

5> go

Role Name                     

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

sa_role                       

sso_role                      

mon_role                      

(3 rows affected)

(return status = 0)

1>

2>

3> revoke role sa_role,mon_role,sso_role from pankaj

4> go

1> sp_activeroles

2> go

Role Name                     

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

(0 rows affected)

(return status = 0)

1>

2>

3>

4>

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

SESSION 2: (sa user)

/sybase>isql -Usa -SSYBASE157 -w999

Password:

1>

2>

3>

4> sp_activeroles

5> go

Role Name                     

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

sa_role                       

sso_role                      

oper_role                     

sybase_ts_role                

(4 rows affected)

(return status = 0)

1> revoke role sa_role from sa              

2> go

1> sp_activeroles

2> go

Role Name                     

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

sso_role                      

oper_role                     

sybase_ts_role                

(3 rows affected)

(return status = 0)

1>

2>

3>

4> revoke sybase_ts_role from sa          

5> go

Msg 102, Level 15, State 181:

Server 'SYBASE157', Line 4:

Incorrect syntax near 'sybase_ts_role'.

1> revoke role sybase_ts_role from sa     

2> go

1>

2>

3> revoke role oper_role from sa 

4> go

1>

2>

3> sp_activeroles

4> go

Role Name                     

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

sso_role                      

(1 row affected)

(return status = 0)

1>

2>

3> revoke role sso_role from sa 

4> go

1> sp_activeroles

2> go

Role Name                     

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

(0 rows affected)

(return status = 0)

1>

2>

3>

4> grant role sa_role to sa

5> go

Msg 10353, Level 14, State 1:

Server 'SYBASE157', Line 4:

You must have any of the following role(s) to execute this command/procedure: 'sa_role' . Please contact a user with the appropriate role for help.

--

with kind regards,

DJ

[jayrijnbergen]

check which logins still have roles assigned, and use those logins

select suser_name(suid), role_name(srid) from master..sysloginroles where srid in (0,1)

[SybDBA]

Hi Jeroen Rijnbergen,

In case I don't have those logins or password for those logins, what I can do ??

---

with kind regards

DJ

[jayrijnbergen]

worst case if you don't have password for any login with these roles, then you need to restart the Sybase ASE server with extra option to reset password for those logins

for that you need to add extra parameter in your runserver file:

-p login

that login must have sso_role granted, ASE will generate a random password and print it on the console.

If the login is locked, you can get it unlocked with:

-u login

If you can't do a normal shutdown, because you don't have sa_role, then you'll have to kill your dataserver process from Unix level. Preferably with kill -15 pid. If nothing else works then kill -9 pid

(pid = process id, check with ps -ef|grep dataserver)