fbpx

Guest Post

As any experienced Teradata DBA will tell you, some tables in the Teradata dictionary (DBC) need housekeeping. But some customer sites that I have worked with ignore table DBC.AccessRights.

If you have ever analyzed this table on a Teradata system, you will almost certainly spot two things very quickly.

  1. For a non-history table, it can contain lots of rows.
  2. It is very skewed.

As this is a system table, you cannot affect the table definition, so reducing the skew is not simply changing the Primary Index. Still, as with all skewed tables, if you can reduce the number of rows, the skew will typically reduce.

Further analysis of this table’s content will usually show that many rows are ‘redundant.’  By that, I mean the situation where a user has a particular Access Right at the object level (say SELECT on a table), and they also have the same Access Right at the database level. Therefore, the object level Access Right serves no purpose because the user can SELECT from the table even without it.

Housekeeping this table can have significant effects: one customer where a process was implemented reduced the row count by @40%, which also helped with parsing CPU consumption.

To effectively housekeep this table, some best practices have to be put into place:

  1. The normal batch process to identify and remove redundant Access Rights.
  2. Use Roles instead of direct grants of Access rights to users.
  3. Prevent the use of DROP/CREATE staging tables by ETL processes (see my article “DELETE, don’t DROP”).

As a side note, the impact of skewing in this table should be reduced in TD 15.00 because the table now has a PPI on it, but the above steps will still help.

__CONFIG_colors_palette__{"active_palette":0,"config":{"colors":{"62516":{"name":"Main Accent","parent":-1}},"gradients":[]},"palettes":[{"name":"Default Palette","value":{"colors":{"62516":{"val":"var(--tcb-skin-color-0)"}},"gradients":[]},"original":{"colors":{"62516":{"val":"rgb(19, 114, 211)","hsl":{"h":210,"s":0.83,"l":0.45}}},"gradients":[]}}]}__CONFIG_colors_palette__
__CONFIG_colors_palette__{"active_palette":0,"config":{"colors":{"b4fbe":{"name":"Main Accent","parent":-1}},"gradients":[]},"palettes":[{"name":"Default Palette","value":{"colors":{"b4fbe":{"val":"rgb(241, 99, 52)"}},"gradients":[]},"original":{"colors":{"b4fbe":{"val":"rgb(19, 114, 211)","hsl":{"h":210,"s":0.83,"l":0.45}}},"gradients":[]}}]}__CONFIG_colors_palette__
Previous Article
__CONFIG_colors_palette__{"active_palette":0,"config":{"colors":{"b4fbe":{"name":"Main Accent","parent":-1}},"gradients":[]},"palettes":[{"name":"Default Palette","value":{"colors":{"b4fbe":{"val":"rgb(241, 99, 52)"}},"gradients":[]},"original":{"colors":{"b4fbe":{"val":"rgb(19, 114, 211)","hsl":{"h":210,"s":0.83,"l":0.45}}},"gradients":[]}}]}__CONFIG_colors_palette__
Next Article
__CONFIG_colors_palette__{"active_palette":0,"config":{"colors":{"62516":{"name":"Main Accent","parent":-1}},"gradients":[]},"palettes":[{"name":"Default Palette","value":{"colors":{"62516":{"val":"rgb(255, 0, 0)"}},"gradients":[]}}]}__CONFIG_colors_palette__
GET OUR TERADATA BOOK

David Wellman

David Wellman is the Technical Director of Ward Analytics Ltd, a UK company specialising in Teradata performance analysis and management. David has been using the Teradata products for over 20 years and regularly delivers Teradata training courses and performance consulting engagements. David is a Teradata Certified Master at V2R3, V2R5, TD12 and TD14.More details at: http://www.ward-analytics.com

  • Sure you are allowed to comment on your own post.

    I use the approach of cleaning the AccessRights table on a huge site where we have 1:1 views in a database pointing to all the existing tables in our data warehouse. This simplifies access as you don’t have to qualify the database for each table, but it causes huge skewing. We ended up with parsing times up to some seconds because of redundant rights!

    Nevertheless, as you mentioned, we have to be careful when cleaning up the Access Rights table. I experienced missing permission with Stored Procedures depending on their user context (CALLER, OWNER, USER).

  • David Wellman
    David Wellman says:

    Although not specifically mentioned above, you can also get potential redundant Access Rights when a user has an Access Right granted directly to them and also in a Role that they are a member of. You have to be a bit careful about removing such Access Rights, but it is at least worth reporting on them.

    (P.S. Am I allowed to comment on my own post?)

  • {"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

    You might also like

    >