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

About these ads