 |
 |
Archives of the TeradataForum
Message Posted: Fri, 18 Nov 2005 @ 19:07:07 GMT
| Subj: | | Re: Getting a complete list of users from dbc.users |
| |
| From: | | Bill Grenwelge |
I just take the dbc.users view and hack it up a bit and create the view under sysadmin (or my id) so I can get all the users from any id I log
in with. Here is the hacked up version, basically just removed any of the stuff that forced it to show the stuff based on the USER option. It
works just like the dbc.users view but shows everything in the same format.
Thanks,
BillG
replace view .users
as locking dbc.dbase for access
SELECT
dbase.DatabaseName(NAMED UserName),
dbase.CreatorName,
case when SUBSTR(CHAR2HEXINT(dbase.PasswordString),24,1) = 9 or
SUBSTR(CHAR2HEXINT(dbase.PasswordString),24,1) = 8
then '19'
|| SUBSTR(CHAR2HEXINT(dbase.PasswordString),24,1)||
SUBSTR(CHAR2HEXINT(dbase.PasswordString),26,1)|| '/' ||
SUBSTR(CHAR2HEXINT(dbase.PasswordString),30,1)||
SUBSTR(CHAR2HEXINT(dbase.PasswordString),32,1)|| '/' ||
SUBSTR(CHAR2HEXINT(dbase.PasswordString),36,1)||
SUBSTR(CHAR2HEXINT(dbase.PasswordString),38,1) else '20'
|| SUBSTR(CHAR2HEXINT(dbase.PasswordString),24,1)||
SUBSTR(CHAR2HEXINT(dbase.PasswordString),26,1)|| '/' ||
SUBSTR(CHAR2HEXINT(dbase.PasswordString),30,1)||
SUBSTR(CHAR2HEXINT(dbase.PasswordString),32,1)|| '/' ||
SUBSTR(CHAR2HEXINT(dbase.PasswordString),36,1)||
SUBSTR(CHAR2HEXINT(dbase.PasswordString),38,1)
end
(date, FORMAT 'yyyy/mm/dd', Named PasswordLastModDate)(format 'yy/mm/dd'),
((SUBSTR(CHAR2HEXINT(dbase.PasswordString),42,1)||
SUBSTR(CHAR2HEXINT(dbase.PasswordString),44,1))*10000 +
(SUBSTR(CHAR2HEXINT(dbase.PasswordString),48,1)||
SUBSTR(CHAR2HEXINT(dbase.PasswordString),50,1))*100 +
(SUBSTR(CHAR2HEXINT(dbase.PasswordString),54,1)||
SUBSTR(CHAR2HEXINT(dbase.PasswordString),56,1)))
(Integer, FORMAT '99:99:99', Named PasswordLastModTime),
dbase.OwnerName,
dbase.PermSpace(FORMAT '---,---,---,---,--9'),
dbase.SpoolSpace(FORMAT '---,---,---,---,--9'),
dbase.TempSpace(FORMAT '---,---,---,---,--9'),
dbase.ProtectionType,
dbase.JournalFlag,
dbase.StartupString,
dbase.AccountName(NAMED DefaultAccount),
dbase.DefaultDataBase,
dbase.CommentString,
dbase.DefaultCollation,
((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', Named PasswordChgDate),
((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),
(Dbase.LockedTime / 60 ) * 100 +
( Dbase.LockedTime MOD 60)
(Integer, format '99:99',
Named LockedTime ),
dbase.LockedCount,
dbase.TimeZoneHour,
dbase.TimeZoneMinute,
dbase.DefaultDateForm,
dbase.CreateTimeStamp,
DB2.DatabaseName (named LastAlterName),
dbase.LastAlterTimeStamp,
dbase.DefaultCharType,
dbase.RoleName,
dbase.ProfileName,
dbase.AccessCount,
dbase.LastAccessTimeStamp
FROM DBC.dbase
LEFT OUTER JOIN DBC.Dbase DB2
ON DBC.dbase.LastAlterUID = DB2.DatabaseID
where dbase.rowtype = 'u';
| |
