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.
Do not modify DBC.AccessRights directly. Fewer rows may reduce work, but do not mathematically guarantee a lower skew ratio; measure the actual distribution and parsing impact.
An object-level grant can overlap a database-level grant without being safely redundant. It may preserve access if the database grant is later revoked, or differ in grantor, grant option, roles and ownership. Review effective permissions and intended future behavior before changing grants.
Get the complete Teradata tuning book, free
Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis
206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.
Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.
About the free bookOne historical customer exercise reduced the access-rights row count by about 40% and reported lower parsing CPU. That is a case-specific observation, not a guaranteed gain or justification for indiscriminate revocation.
To effectively housekeep this table, some best practices have to be put into place:
- Use a reviewed, supported GRANT/REVOKE process with before/after permission checks; do not directly delete dictionary records or automatically revoke apparently overlapping grants.
- 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“).
Dictionary structures and partitioning changed across releases. Assess the current version and measured workload before applying any privilege-housekeeping recommendation; a historical improvement does not guarantee a current benefit.
Deciding what to change in your data platform?
Compare optimisation, modernisation and migration against your workload and business requirements.
Get the complete Teradata tuning book, free
Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis
206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.
Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.
About the free book
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).
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?)