IBM Mainframe Forum

by **dick scherrer** » Wed Jun 19, 2013 11:47 pm

Hello,

Yes, we Are here to help. . . .

But what you ask should be answered by someone in your organization. Agreed, this is not strictly RACF, but your DBA should know how to do this. If there is no DBA or the DBA does not know how, then some training is in order . . .

You might try looking in the SYSUSERAUTH table . . .

by **arshadhrashid** » Wed Jun 26, 2013 3:33 am

Here are the queries I developed so far.

FOR DATABASE AUTHORITIES GRANTED TO USERS/GROUPS:

Select all

SELECT SUBSTR(NAME,1,9) DATABASE,SUBSTR(GRANTOR,1,8) GRANTOR,
SUBSTR(GRANTEE,1,8) GRANTEE,
CREATETABAUTH,DBADMAUTH,DBCTRLAUTH,DBMAINTAUTH,CREATETSAUTH,
DISPLAYDBAUTH,DROPAUTH,IMAGCOPYAUTH,LOADAUTH,REORGAUTH,RECOVERDBAUTH,
REPAIRAUTH,STARTDBAUTH,STATSAUTH,STOPAUTH
FROM SYSIBM.SYSDBAUTH
WHERE (NAME = 'YOUR DATABASE NAME')
AND (GRANTEE<>''IF you want to exclude some names')
ORDER BY NAME;

FOR SYSTEM PRIVILEGES HELD BY USERS/GROUPS:

Select all

SELECT SUBSTR(GRANTOR,1,8) GRANTOR,SUBSTR(GRANTEE,1,8) GRANTEE,
BINDADDAUTH,CREATEDBAAUTH,CREATEDBCAUTH,DISPLAYAUTH,STOPALLAUTH,
SYSADMAUTH,SYSOPRAUTH
FROM SYSIBM.SYSUSERAUTH
WHERE (GRANTEE<>'IF YOU WANT TO EXCLUDE SOME NAME')
ORDER BY GRANTEE ;

FOR TABLE AUTHORITIES GRANTED TO USERS/GROUPS:

Select all

SELECT
DISTINCT(SUBSTR(GRANTEE,1,8)) GRANTEE,
SELECTAUTH SELECT,UPDATEAUTH UPDATE,INSERTAUTH INSERT,
DELETEAUTH DEL,ALTERAUTH ALT ,UPDATECOLS UPDCOL
FROM SYSIBM.SYSTABAUTH
WHERE (TCREATOR='NAME YOU WANT TO CHECK' )
AND (GRANTEE<>'TO EXCLUDE SOME NAME')
AND GRANTEETYPE <> 'P'
ORDER BY GRANTEE ;

FOR PRIVILEGES HELD BY USERS/GROUPS OVER PACKAGES:

Select all

SELECT SUBSTR(COLLID,1,8) COLLC_NAME,SUBSTR(NAME,1,10) PACKG,
SUBSTR(GRANTOR,1,8) GRANTOR,SUBSTR(GRANTEE,1,8) GRANTEE,GRANTEETYPE,
BINDAUTH,COPYAUTH,EXECUTEAUTH
FROM SYSIBM.SYSPACKAUTH
WHERE (GRANTEE <> 'TO EXCLUDE SOME NAME')
ORDER BY COLLID,NAME;

FOR PRIVILEGES HELD BY USERS/GROUPS OVER APPLICATION PLANS:

Select all

SELECT SUBSTR(NAME,1,10) PLAN,
SUBSTR(GRANTOR,1,8) GRANTOR,SUBSTR(GRANTEE,1,8) GRANTEE,
BINDAUTH,EXECUTEAUTH,SUBSTR(CHAR(GRANTEDTS),1,10) DATEGRANTED
FROM SYSIBM.SYSPLANAUTH
WHERE (GRANTEE <> 'TO EXCLUDE SOME NAME')
ORDER BY NAME;

FOR RESOURCE PRIVILEGES HELD BY USERS/GROUPS:

Select all

SELECT SUBSTR(NAME,1,10) RESOURCE, OBTYPE,
SUBSTR(GRANTOR,1,8) GRANTOR,SUBSTR(GRANTEE,1,8) GRANTEE
FROM SYSIBM.SYSRESAUTH
WHERE (GRANTEE <> 'NAME TO EXCLUDE')

Thanks.

by **NicC** » Wed Jun 26, 2013 5:42 pm

I do not understand why a) you posted, b) posted in all capitals and c) why you did not use the code tags for the code.

by **arshadhrashid** » Wed Jun 26, 2013 8:45 pm

Answers

a) why you posted ====== So that others can benefit from what I have learned.
b) posted in all capitals ========== Main frame is case in-sensitive and by default the case is on. What is the big deal?
c) why you did not use the code tags ============= Sorry I dont know this rule and not sure how to use code tags.

Thanks

by **Akatsukami** » Wed Jun 26, 2013 8:56 pm

arshadhrashid wrote:b) posted in all capitals ========== Main frame is case in-sensitive and by default the case is on.

Wrong.

by **dick scherrer** » Wed Jun 26, 2013 9:44 pm

Hello,

Sorry I dont know this rule and not sure how to use code tags

As your code was not indented, the Code tag wouldn't have helped much.

Indented code is much more easily read and maintained.

Thanks for the queries

d

by **NicC** » Wed Jun 26, 2013 10:31 pm

A) Great - thank you
B) As Akatsukai says - wrong - I am always switching between upper and lower case. If you mean on output - that depends! My output today was coming mainly in lwer case because that is how I coded it to come. Of course, you do not have the flexibility of doing that with utility output but it could have been edited before pasting which would have helped distinguish the 'comment' from the code.
C) after almost 4 years of being around the board you do not know how to use code tags? How to use them is often being explained. Search. Using the code tags would assist in distinguishing the code from the comment. I have 'coded' your post up and you should see what I mean.

IBM Mainframe Forum

Authorities of user on db2 v9 database

Re: Authorities of user on db2 v9 database

Re: Authorities of user on db2 v9 database

Re: Authorities of user on db2 v9 database

Re: Authorities of user on db2 v9 database

Re: Authorities of user on db2 v9 database

Re: Authorities of user on db2 v9 database

Re: Authorities of user on db2 v9 database