Want to reduce the Size of Access Rights Table

General
Enthusiast

Want to reduce the Size of Access Rights Table

Hi All,

There is an Access Rights Table whose size has increased a lot. But the Issue is that All the Users have been defined Roles in Prod and DEV and they have their Access Rights on different Objects. So how can I reduce the size of Access Rights Table without the users getting effecting with their access rights.

Thanks and Regards,

2 REPLIES
Enthusiast

Re: Want to reduce the Size of Access Rights Table

The rights at the role level are identified in the dbc.allrolerights table. The member and their roles can be identified in the dbc.rolemembers table. Thus you get what are the rights that are existing for a user through a role. DBC.allrights has all the rights including the implicit rights the user has when he or she created the objects. These will at times be duplicates to those which can be accessed through the roles. For ex: A user has a role to access,create and manipulate a table in a database. When he creates the table, the user will be explicitly given rights on the table which is a duplication of the right but this will not be attached to the role and hence this would be at the object level which can be deleted without impacting the users access.

ideally the roles will be given at the database level and in some typical cases at the table level.

When you join the dbc.allrights with the combination of dbc.allrolerights and dbc.rolemembers, there are other tihings that you need to check like the grant authority and tablename should not be ALL and the username should not be the same as databasename.

HTH!

Krishna

Enthusiast

Re: Want to reduce the Size of Access Rights Table

Hi All,

There is a script below which shows the counts of Username as EDWADMIN:-

1) Select count(1) from dbc.allrights where username='EDWADMIN' and grantorname <> 'EDWADMIN';

1,796

2) Select count(1) from dbc.allrights where username='EDWADMIN';

44,059

Now if we go with the first query it is showing very less counts and its just the opposite case in second script:-

So what is the next step we should take of reducing the rows in this Username.

Thanks and Regards,