Authorities of user on db2 v9 database



IBM's flagship relational database management system

Re: Authorities of user on db2 v9 database

Postby 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 . . .
Hope this helps,
d.sch.
User avatar
dick scherrer
Global moderator
 
Posts: 6304
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 91 times

Re: Re: Authorities of user on db2 v9 database

 

Re: Authorities of user on db2 v9 database

Postby arshadhrashid » Wed Jun 26, 2013 3:33 am

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.
arshadhrashid
 
Posts: 70
Joined: Tue Jul 28, 2009 5:03 am
Has thanked: 0 time
Been thanked: 0 time

Re: Authorities of user on db2 v9 database

Postby 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.
The problem I have is that people can explain things quickly but I can only comprehend slowly.
Regards
Nic
NicC
Global moderator
 
Posts: 2690
Joined: Sun Jul 04, 2010 12:13 am
Location: Pushing up the daisys (almost)
Has thanked: 4 times
Been thanked: 105 times

Re: Authorities of user on db2 v9 database

Postby 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
arshadhrashid
 
Posts: 70
Joined: Tue Jul 28, 2009 5:03 am
Has thanked: 0 time
Been thanked: 0 time

Re: Authorities of user on db2 v9 database

Postby 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.
"You have sat too long for any good you have been doing lately ... Depart, I say; and let us have done with you. In the name of God, go!" -- what I say to a junior programmer at least once a day
User avatar
Akatsukami
Global moderator
 
Posts: 1053
Joined: Sat Oct 16, 2010 2:31 am
Location: Bloomington, IL
Has thanked: 6 times
Been thanked: 51 times

Re: Authorities of user on db2 v9 database

Postby 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
Hope this helps,
d.sch.
User avatar
dick scherrer
Global moderator
 
Posts: 6304
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 91 times

Re: Authorities of user on db2 v9 database

Postby 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.
The problem I have is that people can explain things quickly but I can only comprehend slowly.
Regards
Nic
NicC
Global moderator
 
Posts: 2690
Joined: Sun Jul 04, 2010 12:13 am
Location: Pushing up the daisys (almost)
Has thanked: 4 times
Been thanked: 105 times

Previous

Return to DB2

 


  • Related topics
    Replies
    Views
    Last post