See which user can access all tables and views in a database

Teradata Administrator gives you some very handy features.  One I find very useful is the “Rights” function when you right-click on a table or view, which will show you all users that can access a single table or view.  Or you can right-click on a Database and use “Right on DB/User” to list all users that have access to the Database.

But, what if you want to know who can access all tables and views in a Database individually?

I’ve taken the SQL from the “Rights” function and modified it to list all access rights on individual objects in the database, so this will show you what access has been granted individually to all tables, views, macros, and procedures within that database specified as “DatabaseName=’database'”:

SELECT *
FROM
(
SELECT UserName AS Grantee,TableName,ColumnName,AccessRight,GrantAuthority,
GrantorName,AllnessFlag,DatabaseName
FROM dbc.AllRightsV
UNION
SELECT RoleName AS Grantee,TableName,ColumnName,AccessRight,'R',
GrantorName,
'',DatabaseName
FROM dbc.AllRoleRightsV
) DbRights
--Modify the 'database' value to the name of the database you wish to view
WHERE DatabaseName = 'database'
-- AND TableName='TableName'
-- AND Grantee='Username"
ORDER BY Grantee, TableName

Note you can modify this further (note the commented-out lines) for different purposes, such as by Table or User.

If you want to see the SQL executed by functions within Teradata Administrator when you execute any function, click Window –> SQL History, or press F12.

Created from Teradata Administrator 13.10.0.2

 

 

Posted in dba, Teradata, Teradata Developer | Leave a comment

Aborting all sessions for a given user

SELECT AbortSessions (1, 'USERNAME', 0, 'N', 'N');

The above would abort all sessions for user “USERNAME”.  The third value is the “SessionNoIn” field, where you can either specify a specific session number, or specify 0 for all sessions that match the other conditions.

The function is defined as follows:

Parameter Name Type Comment Nullable Format Max Length Decimal Total Digits Decimal Fractional Digits Table/View? Char Type Parameter Type UDT Name
1 HostIdIn I2 _?_ Y -(5)9 2 _?_ _?_ F _?_ I _?_
2 UserNameIn CV _?_ Y X(128) 128 _?_ _?_ F 1 I _?_
3 SessionNoIn I _?_ Y -(10)9 4 _?_ _?_ F _?_ I _?_
4 LogoffSessions CV _?_ Y X(1) 1 _?_ _?_ F 1 I _?_
5 UserOverride CV _?_ Y X(1) 1 _?_ _?_ F 1 I _?_
6 RETURN0 I _?_ Y -(10)9 4 _?_ _?_ F _?_ O _?_

NB: For the uninitiated, “RETURNo” is the return number field, not an input field.  As such only 5 parameters are specified when calling the function.

The details for each field are as follows:

  • HostIdIn – The host ID (normally 1, as it’s only relevant to Z/OS clients)
  • UserNameIn – The username of the user that executed the session.
  • SessionNoIn – The session number of the session (or 0 if you want to kill all sessions for that user)
  • LogoffSessions – (This needs to be confirmed) Logoff session after abort (rather than leaving it idle)
  • UserOverride – …as below…

Indicator of whether to override an ABORT SESSION failure:
• Y = Override the ABORT SESSION request to fail in any of the following cases:
• An identified session is being session-switched.
• An identified session is executing its own ABORT SESSION request.
• An identified session has a PEState of IDLE: IN-DOUBT as a result of a 2PC.
Note: Sessions are marked IN-DOUBT by the 2PC protocol, which governs how transactions are committed by multiple systems that do not share memory. The protocol guarantees that either all systems commit or all roll back.
• N or NULL = Do not override.

The AbortSessions function returns the number of sessions aborted as the resultset.

Instructions created for Teradata 14.10

References:

http://forums.teradata.com/forum/database/parameter-values-in-syslib-abortsessions – viewed 2015-06-12

Posted in Uncategorized | Leave a comment

SQLJ permissions for Teradata Java UDF (JUDF) Compilation and Creation

If you receive the following error message, you need some permissions on your SQLJ database in your Teradata RDBMS:

“User does not have permission to access SQLJ on the current database”

Image

This simply means that the user that is creating the JUDF needs “EXECUTE PROCEDURE” access on the “SQLJ” database within Teradata.  A statement such as the following will fix this for you:

GRANT EXECUTE PROCEDURE ON SQLJ TO <username>;

By default, only the DBC user has permission to do this, so as good practice you should first use DBC to grant permission to your DBC/super-user account, rather than having to use DBC every time:

GRANT ALL ON SQLJ TO <super-user> WITH GRANT OPTION;

References:

http://developer.teradata.com/tools/articles/creating-a-table-java-user-defined-function-using-the-teradata-plug-in-for-eclipse

http://developer.teradata.com/extensibility/articles/hadoop-dfs-to-teradata

This information was based on a Teradata 14.0 system.

Posted in Uncategorized | Tagged , , , , , | Leave a comment

Monitoring Rollback Sessions from Viewpoint

If you abort a query and want to see tha status of the table roll-back, you can monitor the status/progress of the roll-back from Viewpoint.

Viewpoint has a Remote Console portlet available for admins to access a restricted version of the console.

You can access the Recovery Manager (the equivalent of the rcvmanager utility) from within the Remote Console portlet.

From within the Recovery Manager you can issue the following command to see the status of tables being rolled-back:

 LIST ROLLBACK TABLES;

This is an example of the output (albeit with nothing currently being rolled-back):

LIST ROLLBACK TABLES;
TABLES BEING ROLLED BACK AT 15:50:47 13/06/27

ONLINE USER ROLLBACK TABLE LIST

Host  Session   User ID     Performance Group               AMP W/Count
----  --------  ---------   ------------------------------  -----------

TJ Rows Left   TJ Rows Done   Time Est.
-------------  -------------  ---------

Table ID   Name
---------  ------------------------------------------------------------------

SYSTEM RECOVERY ROLLBACK TABLE LIST

Host  Session   TJ Row Count
----  --------  -------------

Table ID   Name
---------  ------------------------------------------------------------------

Enter command, "QUIT;" or "HELP;" :
This information was derived from Teradata 14.0 and Teradata Viewpoint 14.10
Posted in Uncategorized | Leave a comment

A simple macro to find a username based on part of a user’s full name

This macro will retrieve a person’s username, based on a provided text string (wildcards included for the LIKE operator).  This assumes that you have your user’s full name stored in the user comment string.

CREATE MACRO getUserID
(
Name VARCHAR(255)
)
AS (
   SELECT DatabaseName AS Username
   ,CommentString AS Name
   FROM DBC.Databases
   WHERE Name LIKE :Name
   ;
   );

Example:

exec getuserid ('%smith%');

Written for Teradata 13.10

Posted in Teradata | Tagged , , , , | Leave a comment

A macro to identify a user by username

This is a short macro I wrote to find out who a user is by returning the comment string for the given user (as it is common for the username to be a unique identifier, while the user’s real name is stored in the comment string).  I find myself doing this a lot when receiving user revoke requests from the service desk.

CREATE MACRO whois
(
Username VARCHAR(255)
)
AS (
   SELECT DatabaseName AS Username
   ,CommentString AS Name
   FROM DBC.Databases
   WHERE DatabaseName = :Username
   ;
   );
exec whois ('username');

Written for Teradata 13.10

Posted in Teradata, Uncategorized | Tagged , , , , | Leave a comment

Modified DBC.Users view to display all users

I have modified the existing DBC.Users view to display details of all users, rather than just of the user executing the query (which is how DBC.Users works).  This is handy to keep in your own database, or a shared DBA database, because it has more informtion that DBC.Databases.

REPLACE VIEW Users
AS
SELECT CAST(TRANSLATE(dbase.DatabaseName USING UNICODE_TO_LOCALE WITH ERROR)
            AS CHAR(30)) (NAMED UserName),
       CAST(TRANSLATE(dbase.CreatorName USING UNICODE_TO_LOCALE WITH ERROR)
            AS CHAR(30)) (NAMED CreatorName),
       /* DR101935-jw180009-01-> */
       CAST(dbase.PasswordModTime AS DATE) AS PasswordLastModDate,
       CAST(dbase.PasswordModTime AS TIME(0)) AS PasswordLastModTime,
       /* <-DR101935-jw180009-01 */
       CAST(TRANSLATE(dbase.OwnerName USING UNICODE_TO_LOCALE WITH ERROR)
            AS CHAR(30)) (NAMED OwnerName),
       dbase.PermSpace(FORMAT '---,---,---,---,--9'),
       COALESCE(PF.SpoolSpace, dbase.SpoolSpace)(FORMAT '---,---,---,---,--9')
       (NAMED SpoolSpace),
       COALESCE(PF.TempSpace, dbase.TempSpace)(FORMAT '---,---,---,---,--9')
       (NAMED TempSpace),
       dbase.ProtectionType,
       dbase.JournalFlag,
       dbase.StartupString,
       CAST(TRANSLATE(COALESCE(PF.DefaultAccount, dbase.AccountName) USING
                      UNICODE_TO_LOCALE WITH ERROR)
            AS CHAR(30)) (NAMED DefaultAccount),
       CAST(TRANSLATE(COALESCE(PF.DefaultDataBase, dbase.DefaultDataBase)
                      USING UNICODE_TO_LOCALE WITH ERROR)
            AS CHAR(30)) (NAMED DefaultDataBase),
       dbase.CommentString,
       dbase.DefaultCollation,
       /*+-----------------------------------------------------+
         | PasswordChgDate converted from Julian to YY/MM/DD   |
         +-----------------------------------------------------+ */
       CASE WHEN DBASE.PasswordChgDate < 0
       THEN NULL
       ELSE
       (((100 * ((4 * NULLIFZERO(DBASE.PasswordChgDate) - 1) / 146097)
       + (4 * (((4 * DBASE.PasswordChgDate - 1) MOD 146097) / 4)
       + 3) / 1461 - 1900) + ((5 * (((4 * (((4 * DBASE.PasswordChgDate
       - 1) MOD 146097) / 4) + 3) MOD 1461 + 4) / 4) - 3) / 153 + 2)
       / 12) * 10000 + (((5 * (((4 * (((4 * DBASE.PasswordChgDate - 1)
       MOD 146097) / 4) + 3) MOD 1461 + 4) / 4) - 3) / 153 + 2) MOD 12
       + 1) * 100 + ((5 * (((4 * (((4 * DBASE.PasswordChgDate - 1) MOD
       146097) / 4) + 3) MOD 1461 + 4) / 4) - 3) MOD 153 +5) / 5
       (DATE, FORMAT 'yy/mm/dd'))
       END AS PasswordChgDate,
       /*+------------------------------------------------+
         | LockedDate converted from Julian to YY/MM/DD   |
         +------------------------------------------------+ */
       ((100 * ((4 * DBASE.LockedDate - 1) / 146097)
       + (4 * (((4 * DBASE.LockedDate - 1) MOD 146097) / 4)
       + 3) / 1461 - 1900) + ((5 * (((4 * (((4 * DBASE.LockedDate
       - 1) MOD 146097) / 4) + 3) MOD 1461 + 4) / 4) - 3) / 153 + 2)
       / 12) * 10000 + (((5 * (((4 * (((4 * DBASE.LockedDate - 1)
       MOD 146097) / 4) + 3) MOD 1461 + 4) / 4) - 3) / 153 + 2) MOD 12
       + 1) * 100 + ((5 * (((4 * (((4 * DBASE.LockedDate - 1) MOD
       146097) / 4) + 3) MOD 1461 + 4) / 4) - 3) MOD 153 +5) / 5
       (DATE, FORMAT 'yy/mm/dd', NAMED LockedDate),
       /*+------------------------------------------------+
         | Lockedtime converted from minutes to HH:MM     |
         +------------------------------------------------+*/
       (Dbase.LockedTime / 60 ) * 100 +(Dbase.LockedTime MOD 60)
       (INTEGER, FORMAT '99:99', NAMED LockedTime),
       dbase.LockedCount,
       dbase.TimeZoneHour,
       dbase.TimeZoneMinute,
       dbase.DefaultDateForm,
       dbase.CreateTimeStamp,
       CAST(TRANSLATE(DB2.DatabaseName USING UNICODE_TO_LOCALE WITH ERROR)
            AS CHAR(30)) (NAMED LastAlterName),
       dbase.LastAlterTimeStamp,
       dbase.DefaultCharType,
       CAST(TRANSLATE(dbase.RoleName USING UNICODE_TO_LOCALE WITH ERROR)
            AS CHAR(30)) (NAMED RoleName),
       CAST(TRANSLATE(dbase.ProfileName USING UNICODE_TO_LOCALE WITH ERROR)
            AS CHAR(30)) (NAMED ProfileName),
       dbase.AccessCount,
       dbase.LastAccessTimeStamp
FROM DBC.dbase
         LEFT OUTER JOIN DBC.Profiles PF
                      ON DBC.Dbase.ProfileName = PF.ProfileNameI
         LEFT OUTER JOIN DBC.Dbase DB2
                      ON DBC.dbase.LastAlterUID = DB2.DatabaseID
WHERE  dbase.DatabaseId IN
        (/* IDs of users controlled by this USER */
SELECT dbase.DatabaseId
        FROM DBC.dbase
        WHERE  dbase.RowType = 'U' 
--        AND
--             (/* I AM the user */
--                   (dbase.DataBaseNameI = USER))
--UNION  ALL
--SELECT dbase.DatabaseId
--        FROM DBC.dbase, DBC.owners
--        WHERE  dbase.RowType = 'U' 
--        AND
--              /* I own the user */
--                   (dbase.DatabaseId = owners.owneeid
--                    AND owners.ownerid = (SEL DISTINCT
--                     databaseid FROM DBC.dbase WHERE
--                     databasenamei=USER))
--UNION ALL
--SELECT dbase.DatabaseId
--        FROM DBC.dbase, DBC.accessrights, DBC.userdbV
--        WHERE    dbase.RowType = 'U'
--        AND      (
--                  /* I have explicit modify rights on user */
--                   (dbase.DatabaseId = accessrights.DatabaseId
--                   AND (accessrights.accessright = 'DD'
--                   OR   accessrights.accessright = 'DU')
--                   AND accessrights.UserId = userdbV.id
--                   AND userdbV.name=USER))
       ) WITH CHECK OPTION;

Written for Teradata 13.10

Posted in Teradata, Uncategorized | Tagged , , , , , | 1 Comment