AccessRights reporting macros ============================= Introduction ------------ This folder contains the definitions of two macros which are intended to simplify reporting of which AccessRights are held (A) by a user or (B) on a database. Different Versions ------------------ From a functional perspective there are only two macros: - reporting of all AccessRights held by a user (AllUserRights) - reporting of all AccessRights held on a database (AllDatabaseRights) For each of these two macros there are four versions (which is why this folder contains 8 files). The differences between the four versions are: - two macros return character strings using the Unicode character set and two return character strings using the Latin character set - two macros use the standard views and two access the table directly (these are expected to be more efficient). The following list of files identifies which macro does what (although hopefully it is fairly obvious): - macro name AllDatabaseRights show_all_database_rights_latin_views.txt: uses the views and returns data using Latin character set show_all_database_rights_latin_tables.txt: uses direct access to tables and returns data using Latin character set - macro name AllDatabaseRightsV show_all_database_rights_unicode_tables.txt: uses direct access to tables and returns data using Unicode character set show_all_database_rights_unicode_views.txt: uses the views and returns data using Unicode character set - macro name AllUserRights show_all_user_rights_latin_views.txt: uses the views and returns data using Latin character set show_all_user_rights_latin_tables.txt: uses direct access to tables and returns data using Latin character set - macro name AllUserRightsV show_all_user_rights_unicode_tables.txt: uses direct access to tables and returns data using Unicode character set show_all_user_rights_unicode_views.txt: uses the views and returns data using Unicode character set Do NOT install all 8 files - you will only end up with 4 macros. The intention is that you would install one 'user' macro and one 'table' macro - probably the 'Unicode' version of each. Installing ---------- The supplied files just contain REPLACE MACRO commands. Decide which version of a macro you want to install and copy the contents of the relevant file into your preferred query tool. The REPLACE MACRO commands do not have any databasename for the macro so they will install into your current default database. The owner of these macros will need the following AccessRights: - 'view' versions: SELECT 'WITH GRANT' on DBC.ALLRIGHTS(V), DBC.ROLEMEMBERS(V), DBC.ALLROLERIGHTS(V) - 'table' versions: SELECT 'WITH GRANT' on DBC.ACCESSRIGHTS, DBC.DBASE, DBC.ROLEGRANTS, DBC.ROLES, DBC.TVM, DBC.TVFIELDS Using these macros ------------------ Each macro accepts two parameters. The first parameter MUST be supplied and is the name of the user or database (as appropriate). - this parameter determines which user or database is the subject of the report The second parameter (named 'Options') is optional and defaults to an empty string (''). - this parameter controls the inclusion of certain AccessRights in the report - if you supply more than one value for this parameter, the values need to be delimited. I suggest you use a space (' ') or a comma (',') - the valid values for this parameter are: PUB - display AccessRights on the named database granted to PUBLIC SELF - display AccessRights the named database has on itself DBC - display AccessRights that user DBC has on the database (by default none of these sets of AccessRights are displayed) Output from these macros ------------------------ I think that most displayed columns are fairly obvious in their meaning, except for 'Grant Type' which is explained below: The column 'Grant Type' now describes how the user has received the AccessRight: - User: the AccessRight was specifically granted to the user - Direct Role: the AccessRight was granted to a role and that role was granted to the user - Nested Role: the AccessRight was granted to a role which was granted to another role that was granted to the user - Public: the AccessRight was granted to 'Public' (by default these AccessRights are not shown, see 'OPTIONS') Teradata Version Coverage ------------------------- These macros have been installed and executed on Teradata 12, 13.0, 13.10, 14.0, 14.10, 15.0 and 15.10 systems with no known problems. Regards, Dave Wellman Ward Analytics Ltd