Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2004
    Posts
    545
    Provided Answers: 4

    Unanswered: [ASE12.5] Information about roles in systemtables

    Today I discovered something odd I never noticed before. The following situation:
    A server with the usual user-logins, several non-system-roles, those roles being assigned to logins. Nothing strange so far.
    The master..sysloginroles contains the records that join the master..syslogins-table with the master..syslogins-table, I get that. Yet, I have a userdefined role that has the sso-role assigned to it. When looking in the master..sysloginroles, I found no trace of that combination, nor in any other system table in the masterdatabase.

    When looking at the poster of the ASE-systemtables, I noticed the sysroles and sysusers-table in the userdatabases. The sysroles-table seems to join the syssrvroles-table with the sysusers-table, i.e. assigning a role to a user in a database. I would have expected that kind of join to go from sysusers through syslogins, through sysloginroles to syssrvroles.

    When looking at the content of the sysroles and the sysusers-tables I discovered something. The sysusers-table contains a record for a userdefined role, join to the master..syssrvroles-table through the sysroles-table. That record in sysusers has no corresponding value in the suid-column that points to a login in the master..syslogins-table. All it says is value -2. All records in sysusers that contain proper users point to the proper record in master..syslogins.
    Now, I could be convinced that this is how ASE works, but why is it that only a few userdatabase have those records in sysusers, and not all databases?

    Long story, I know, but I hope that someone understands it and can comment on it.
    I'm not crazy, I'm an aeroplane!

  2. #2
    Join Date
    Feb 2002
    Location
    Willy is on vacation
    Posts
    1,208
    Sybase stores roles information in master..sysattributes with object_type='UR'.

    All role chaining information comes from master..sysattributes.

  3. #3
    Join Date
    Jan 2004
    Posts
    545
    Provided Answers: 4
    Interesting, I didn't know that. You seem to know quite a bit about the internals of ASE, are you a developer for Sybase or anything like it?
    I'm not crazy, I'm an aeroplane!

  4. #4
    Join Date
    Feb 2002
    Location
    Willy is on vacation
    Posts
    1,208
    work for Sybase? Yuck! that is blasphemous.

    I spend my days netting fish and working in the farms.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •