can dbc.AccessRights contain records pertaininng to dropped users?

Database
Enthusiast

can dbc.AccessRights contain records pertaininng to dropped users?

I'm looking at records in dbc.AccessRights, joining it to dbc.UserDBV to get the username, and I'm finding some records return a null value for the name.  Is it possible to have records in AccessRights which do not pertain to a current user?   I would have thought when a user was dropped, the records in this table would be cleaned up.  If these records don't refer to a dropped user, what is their purpose/function?

2 REPLIES
Junior Contributor

Re: can dbc.AccessRights contain records pertaininng to dropped users?

Yes, this is possible, the system views will return this as Dropped User, e.g. SHOW VIEW dbc.AllRightsV.

 

From the DDL Manual:

 Effects of Dropping a User on Database Privileges

When you drop a user, the system deletes individual privileges granted directly to the

dropped user, and privileges obtained through role assignment, from dictionary tables.

Dropping database objects that exist in the user space prior to the DROP USER statement

(required) removes any privileges on the objects from roles and other users or databases.

Any privileges granted by the dropped user on objects outside the user space remain in the

system.

 Effects of Dropping a User on User-Created Roles and Profiles

Roles and profiles created by a dropped user remain in the system after the user is dropped.

Enthusiast

Re: can dbc.AccessRights contain records pertaininng to dropped users?

Dieter,

Thanks for the info and reference.

 

However, I'm wondering whether the dbc.AllRightsV view is written the way you describe in the following regards:  I think it is dropping users that have been deleted from the system rather than listing them as "Dropped User" . 

 

In my situation, AllRightsV is showing 1309 fewer records than the AccessRights table. I'm not seeing any records with the user name "Dropped User" which accounts for 819 of the record missing.  The AllRightsV view code is "interesting", being a mix of old (join conditions in the where clause) and new (join condition in an "ON" clause).  AllRightsV is using dbc.DBase.DatabaseID for its UserID.  In my situation, there are 2 records that have DatabaseID values not found in AccessRights and 6 records have a UserID in AccessRights not found in dbc.DBase.DatabaseID.

 

I think the view definition would need to use an outer join when trying to look up the UserID in order for any "Dropped User"s to show up.

 

I'm going to look at things a bit more to find where the other dropped records are occuring.