Archives of the TeradataForum

Message Posted: Tue, 10 Dec 2013 @ 22:10:55 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

I guess my first question is why do you believe the skew on this dictionary table is an issue? Skew by itself is not necessarily bad.

Most times, performance issues with accessrights comes from unneeded rows. Have you tried running the standard cleanup script (attached below) to see what that does for you?

Regards,

Dave

     LOCKING ROW FOR ACCESS
     SELECT 'REVOKE '||
            CASE
                 WHEN AccessRight='AE'  THEN 'ALTER EXTERNAL PROCEDURE'
                 WHEN AccessRight='AF'  THEN 'ALTER FUNCTION'
                 WHEN AccessRight='AP'  THEN 'ALTER PROCEDURE'
                 WHEN AccessRight='AS'  THEN 'ABORT SESSION'
                 WHEN AccessRight='CA'  THEN 'CREATE AUTHORIZATION'
                 WHEN AccessRight='CD'  THEN 'CREATE DATABASE'
                 WHEN AccessRight='CE'  THEN 'CREATE EXTERNAL PROCEDURE'
                 WHEN AccessRight='CF'  THEN 'CREATE FUNCTION'
                 WHEN AccessRight='CG'  THEN 'CREATE TRIGGER'
                 WHEN AccessRight='CM'  THEN 'CREATE MACRO'
                 WHEN AccessRight='CO'  THEN 'CREATE PROFILE'
                 WHEN AccessRight='CP'  THEN 'CHECKPOINT'
                 WHEN AccessRight='CR'  THEN 'CREATE ROLE'
                 WHEN AccessRight='CT'  THEN 'CREATE TABLE'
                 WHEN AccessRight='CU'  THEN 'CREATE USER'
                 WHEN AccessRight='CV'  THEN 'CREATE VIEW'
                 WHEN AccessRight='D'   THEN 'DELETE'
                 WHEN AccessRight='DA'  THEN 'DROP AUTHORIZATION'
                 WHEN AccessRight='DD'  THEN 'DROP DATABASE'
                 WHEN AccessRight='DF'  THEN 'DROP FUNCTION'
                 WHEN AccessRight='DG'  THEN 'DROP TRIGGER'
                 WHEN AccessRight='DM'  THEN 'DROP MACRO'
                 WHEN AccessRight='DO'  THEN 'DROP PROFILE'
                 WHEN AccessRight='DP'  THEN 'DUMP'
                 WHEN AccessRight='DR'  THEN 'DROP ROLE'
                 WHEN AccessRight='DT'  THEN 'DROP TABLE'
                 WHEN AccessRight='DU'  THEN 'DROP USER'
                 WHEN AccessRight='DV'  THEN 'DROP VIEW'
                 WHEN AccessRight='E'   THEN 'EXECUTE'
                 WHEN AccessRight='EF'  THEN 'EXECUTE FUNCTION'
                 WHEN AccessRight='GC'  THEN 'CREATE GLOP'
                 WHEN AccessRight='GD'  THEN 'DROP GLOP'
                 WHEN AccessRight='GM'  THEN 'GLOP MEMBER'
                 WHEN AccessRight='I'   THEN 'INSERT'
                 WHEN AccessRight='IX'  THEN 'INDEX'
                 WHEN AccessRight='MR'  THEN 'MONITOR RESOURCE'
                 WHEN AccessRight='MS'  THEN 'MONITOR SESSION'
                 WHEN AccessRight='NT'  THEN 'NONTEMPORAL'
                 WHEN AccessRight='OD'  THEN 'OVERRIDE DELETE POLICY'
                 WHEN AccessRight='OI'  THEN 'OVERRIDE INSERT POLICY'
                 WHEN AccessRight='OP'  THEN 'CREATE OWNER PROCEDURE'
                 WHEN AccessRight='OS'  THEN 'OVERRIDE SELECT POLICY'
                 WHEN AccessRight='OU'  THEN 'OVERRIDE UPDATE POLICY'
                 WHEN AccessRight='PC'  THEN 'CREATE PROCEDURE'
                 WHEN AccessRight='PD'  THEN 'DROP PROCEDURE'
                 WHEN AccessRight='PE'  THEN 'EXECUTE PROCEDURE'
                 WHEN AccessRight='RO'  THEN 'REPLICATION OVERRIDE'
                 WHEN AccessRight='R'   THEN 'SELECT'
                 WHEN AccessRight='RF'  THEN 'REFERENCE'
                 WHEN AccessRight='RS'  THEN 'RESTORE'
                 WHEN AccessRight='SA'  THEN 'SECURITY CONSTRAINT ASSIGNMENT'
                 WHEN AccessRight='SD'  THEN 'SECURITY CONSTRAINT DEFINITION'
                 WHEN AccessRight='SH'  THEN 'SHOW'
                 WHEN AccessRight='ST'  THEN 'STATISTICS'
                 WHEN AccessRight='SS'  THEN 'SET SESSION RATE'
                 WHEN AccessRight='SR'  THEN 'SET RESOURCE RATE'
                 WHEN AccessRight='TH'  THEN 'CTCONTROL'
                 WHEN AccessRight='U'   THEN 'UPDATE'
                 WHEN AccessRight='UU'  THEN 'UDT Usage'
                 WHEN AccessRight='UT'  THEN 'UDT Type'
                 WHEN AccessRight='UM'  THEN 'UDT Method'
                 END||' ON "'||

            CASE WHEN ColumnName NOT = 'All'
                      THEN TRIM(DatabaseName)||'"."'||TRIM(TableName)||'.'||TRIM(ColumnName)
                  WHEN TableName NOT = 'All'
                       THEN TRIM(DatabaseName)||'"."'||TRIM(TableName)
                  ELSE TRIM(DatabaseName)
                  END ||'" FROM '|| TRIM(UserName) || ';' (TITLE '')
     FROM ( SELECT A.Username AS Username ,
                   A.Databasename AS Databasename,
                   A.Accessright AS Accessright,
                   A.Tablename AS Tablename,
                   A.Columnname AS Columnname,
                   A.GrantAuthority AS GrantAuthority

               FROM dbc.ALLRights A,
                    ( SELECT Username, Databasename, Accessright,GrantAuthority
                         FROM dbc.ALLRights  WHERE
                       -- to exclude any database from report, add the database name at IN clause
                         Username NOT IN ('DBC','TDWM','SYSTEMFE','SYSADMIN','DBCMANAGER')
                         AND  Tablename='ALL'
                         GROUP BY 1,2,3,4
                         INTERSECT
                         SELECT Username, Databasename, Accessright,GrantAuthority
                         FROM dbc.ALLRights WHERE
                       -- to exclude any database from report, add the database name at IN clause
                         Username NOT IN ('DBC','TDWM','SYSTEMFE','SYSADMIN','DBCMANAGER')
                          AND Tablename<>'ALL'
                         GROUP BY 1,2,3,4
                    ) B

             WHERE A.Username = B.Username
             AND A.Databasename = B.Databasename
             AND A.Accessright = B.Accessright
             AND A.Tablename<>'ALL'
          ) C

     ORDER BY username, databasename, tablename, accessright ;

David Roth
Senior Consultant
Teradata Certified Master
Certified Scrum Master

	<Prev	Next>		<<First	<Prev	Next>	Last>>