Missing permissions even though role is granted

[Former Member]

Hi,

We are managing permissions through roles for application team members. We are encountering a permission issue post migration. We noticed that even though the roles have been granted to the logins, the user gets stuck with permission denied error.

We tried to dig deep by checking the roles and the login-role mapping in master database. The roles have all the permissions on the tables and the login is mapped to the role in sysloginroles table.

Our onshore DBA worked on this and provided the below explanation:

Whenever a role is created the role gets added in the syssrvroles table. When the role is granted permissions in a database the role gets added to the sysroles and sysusers table in the user database(Don't remember where it is added first). When migration happened, the new server has additional roles and due to the role ID mismatch the logins are getting mapped to wrong roles internally and users encounter permission denied issues.

I did understand this concept and agreed but he asked us to delete the entry for the problematic user in master database sysusers table. I did not understand this and asked him to explain further. He explained me how the permission check actually works in sybase ASE. However, I still do not seem to understand the permission checking procedure in sybase ASE.

It would be great if someone could throw some light on this issue. I would like to know the purpose of the sysroles table in master and user databases and how the permission checking happens when the permissions are granted through roles.

Please find the details below:

Pre-migration

Sybase ASE version: 12.5.4

Post migration

Sybase ASE version: 16.0

Many thanks.

--Nandy

[former_member182259]

It is true that sysusers in each database has an entry for roles (and groups) usually with negative user id values.   When moving databases between different servers, it is important to sync sysusers with syslogins & syssrvroles - and yes, ASE 16 has some additional roles due to adding several in the past 10+ years since 12.5 was around.    There are script floating around to do this....but essentially you need to do something like:

sp_configure 'allow updates',1  -- allow changes to system tables

go

use databasename

go

update sysusers

set suid=l.suid

from sysusers u, master..syslogins l

where u.name=l.name

go

-- repeat with syssrvroles

...

sp_configure 'allow updates', 0

go

[former_member182259]

BTW - I am not sure how deleting the 'problematic' user in master helps with the problem in a user database.   Ideally, there shouldn't be too many users in master.   If the 'problematic' user was a role, then the consultant just messed you up big time - you might want to verify by comparing with model.

[Former Member]

Hi Jeff,

Thanks for the tip.

My thought was to just drop the problematic role, extract the role from the original source server using DDLGEN and run it in the target.

This way I don't face any issues with the role ID mismatch between syssrvroles and the sysroles table in the user database.

I am still not sure what is the purpose behind the onshore DBA's suggestion about dropping the user from master. I need to set up a call with him to understand.

I will update here once I find out.

Thanks.

--Nandy