I just found a USER with more than 160 000 rows in DBC.ACCESSRIGHTS (skew is 88 !): the main part of those rows are orphan rights with createtimestamp dating of 2005 , 2006 ... before migration on a new system in 2011.
I need to decide wether i just can delete those rows from dbc.accessrights (using SQL "... and TVMID <> '000001000000'xb and TVMID NOT IN (SEL TVMId FROM DBC.TVM) ...") or DROP/CREATE (with caution) the USER to be sure to clean DBC.ACCESSRIGHTS.
Thanks for help and experience,
yes, that's the "official" query to delete orphan access rights.
WHERE TVMID <> ‘000001000000’xb
AND TVMID NOT IN (SEL TVMId FROM DBC.TVM);
Of course you need to "bypass accessrights" to actually delete rows :-)
I don't know if DROP/reCREATE USER works as expected, never tried that.
considering the two options i decided to drop/create the user, and no "bypass" ;-)
DU/CU is a good way to get the dbc.accessrights table clean, just waiting a few minutes for the whole operation anyway, and then give the useful rights to the user.
has anybody done this before and please let me know what steps are to be done post orphan access right deletion.
Orphan rights not the similar to direct grants, those are the rows in dbc.accessrights be left behind after a drop table. I am not sure whether you are aware of the story behind how these orphan rows in dbc.accessrights be left behind even after the table has been dropped. Here is the answer...
In order to minimize blocking, a row that gets created in DBC.TVM that sets a TempFlag column with a value of 'I'. After performing operations on the table like granting rights inserting data etc the flag gets set to 'N' . If the table is archived before the flag gets changed to 'N' then later if the table gets restored (or arc copied) the flag gets set back to 'I'. So if at one point the table had a value of 'N' then after it gets restored (or arc copied) and set back to I, this is where we run into the issue of orphan rows getting left behind.
Come to the part of cleaning the rights, it is not something under control of DBA, you must approach the GSC or CSR to clean them.
NOTE: System need to be down to have these rights cleaned. As far as I know GSC run a script to clean these.
I recommed the DBAs to have a pre check for every time if you have any system maintenanace, simply run the orphan rights before the system maintenance and if you find those, just ask GSC to clean them as part of the maitnenace window instead of having seperate down time for cleaning them.