Page 2 of 2

Re: Authorities of user on db2 v9 database

PostPosted: Wed Jun 19, 2013 11:47 pm
by dick scherrer
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 . . .

Re: Authorities of user on db2 v9 database

PostPosted: Wed Jun 26, 2013 3:33 am
by arshadhrashid
Here are the queries I developed so far.

FOR DATABASE AUTHORITIES GRANTED TO USERS/GROUPS:

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 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
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 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 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 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.

Re: Authorities of user on db2 v9 database

PostPosted: Wed Jun 26, 2013 5:42 pm
by NicC
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.

Re: Authorities of user on db2 v9 database

PostPosted: Wed Jun 26, 2013 8:45 pm
by arshadhrashid
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

Re: Authorities of user on db2 v9 database

PostPosted: Wed Jun 26, 2013 8:56 pm
by Akatsukami
arshadhrashid wrote:b) posted in all capitals ========== Main frame is case in-sensitive and by default the case is on.

Wrong.

Re: Authorities of user on db2 v9 database

PostPosted: Wed Jun 26, 2013 9:44 pm
by dick scherrer
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

Re: Authorities of user on db2 v9 database

PostPosted: Wed Jun 26, 2013 10:31 pm
by NicC
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.