|
|
|
Forum Newbie
Group: Forum Members
Last Login: 10/4/2009 1:40:24 PM
Posts: 4,
Visits: 4
|
|
Hi,
Is there anyway to create a macro to this table and have a normal user run that macro and for it to return all the users (rather than the ones the user has access rights over)?
I have created the macro and tried execute / with grant options and still the user is only able to return limited results.
Thanks
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: Yesterday @ 3:55:54 AM
Posts: 111,
Visits: 263
|
|
Take the code from DBC.Users and edit out the criteria at the end of the view.
You will need to store it somewhere which has Select With Grant on DBC.Users, or it will give security violations. I normally store it in my own userid or SYSDBA, where the site allows it.
Check with your DBA's, and preferably get them to set it up - some sites do not want anyone to know about other users (although there are other ways of getting most of the info.
REPLACE VIEW SYSDBA.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'
) WITH CHECK OPTION;
|
|
|
|
|
Forum Newbie
Group: Forum Members
Last Login: 10/4/2009 1:40:24 PM
Posts: 4,
Visits: 4
|
|
Thanks for the info.
It turns out that 99% of the data the user requires can be got from the dbc.databases so have advised them of that route for now.
|
|
|
|