Why Teradata DBAs Should Prioritize Housekeeping the DBC AccessRights Table
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 quickly.
- A non-history table, it can contain lots of rows.
- It is very skewed.
Since this is a system table, altering the table definition is impossible. Therefore, diminishing the skew is not merely accomplished by modifying the Primary Index. Nonetheless, like all tables with skew, diminishing the number of rows usually leads to a decrease in skew.
Want more practical data engineering analysis like this?
Join DWHPro Letters and get field-tested notes on Teradata, Snowflake, AI, migrations, performance, and enterprise data work. Early subscribers keep launch access before the paid plan launches.
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). They also have the same Access Rights at the database level. Therefore, the object level Access Right serves no purpose because the user can SELECT from the table without it.
Get the next issue by email.
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:
- The normal batch process to identify and remove redundant Access Rights.
- Use Roles instead of direct grants of Access rights to users.
- Prevent DROP/CREATE staging tables using ETL processes (see my article "DELETE, don't DROP").
Note that the impact of skewing in the table should only be reduced to TD 15.00 due to the inclusion of a PPI. However, implementing the above steps will still be beneficial.
Planning or surviving an enterprise data platform migration?
I write regularly about the performance, cost, architecture, and project mistakes that show up in real Teradata, Snowflake, Databricks, and enterprise data work.
Subscribe before the paid plan launches and keep launch access.
Written by Roland Wenzlofsky, founder of DWHPro and author of Teradata Query Performance Tuning. DWHPro has helped data warehouse practitioners for 15+ years.