Skip to Content
avatar image
Former Member

Missing permissions even though role is granted

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

Add comment
10|10000 characters needed characters exceeded

  • Follow
  • Get RSS Feed

2 Answers

  • Jul 15, 2016 at 02:25 PM

    You've mentioned a 'new server' so I'm guessing your migration consisted of dumping from 12.5.4 and loading into 16.0 (ie, you did *not* do an in-place upgrade of the dataserver).

    In the case of copying a user database between dataservers it is up to the dba to address likely system table mismatches between the target dataserver's master database and the newly loaded database.

    Primary mismatches of interest (these ensure a users's server login/role get mapped to the correct user/role in the database, which in turn affects the correct permissions assignments):

    syslogins.suid <--> sysusers.suid , sysalternates.{suid|altsuid}

    syslogins.name <--> sysobjects.loginame

    syssrvroles.srid <--> sysroles.id

    The prescribed (by SAP/Sybase) method for fixing these types of mismatches is to drop/delete all login/role/group/permissions records from the newly loaded target database and recreate all users/roles/groups/permissions via the appropriate system stored procs and/or T-SQL commands.

    Alternatively you can directly modify the system tables in the newly loaded target database, though a) this method is typically not supported by tech support and b) it is up to the dba to understand the nuances of the various tables (eg, sysusers stores user/group/role data, with references to the local sysroles/sysalternates tables and the master..syslogins table).

    For a discussion on patching roles in the newly loaded user database see: Cross database permissions problems | SCN . Keep in mind however that that discussion assumed the same ASE version for source/target. With a move from 12.5.4 to 16.0 you'll have to make sure you address old user-defined roles that may be sharing a server role id with a new system role (ie, you'll need to decide on how to go about re-assigning the server role id such roles); for this scenario take a look at how any new system roles have been added to other databases in the target dataserver.

    -----

    For other potential mismatches take a look at the ASE System Table ER Diagram and look for links that cross the boundary between the master database (top area) and user database (bottom area).

    As for potential system table mismatches between user databases:

    <db1>..syscolumns.encr* <--> <db2>..sysencryptkeys (when column's encryption key resides in separate user database)

    <db1>..sysreferences <--> <db>..sysreferences (when RI constraints reference objects in a separate user database)

    NOTE: There are several other ... shall we say esoteric mismatches ... that entail the master and/or local sysattributes table (eg, cache bindings, tempdb assignments, role hierarchies, remote servers referenced by proxy definitions), and while it's possible to copy these raw records (and update accordingly), the recommended approach would be reverse engineering the details from the source dataserver and submitting via normal commands/SQL in the target dataserver.

    -----

    As for the issue with the master..sysusers record(s) ... *shrug* ... I'm in agreement with Jeff ... no idea why the other person is referencing master..sysusers ... would need more details as to what issue the other person is trying to address ...

    Add comment
    10|10000 characters needed characters exceeded

  • Jul 15, 2016 at 01:55 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member Jeff Tallman

      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