| View previous topic :: View next topic |
| Author |
Message |
vmbigot
Beginner
Joined: 17 Jun 2004
Posts: 36
Topics: 14
Location: westminster, california
|
 Posted: Tue May 31, 2005 4:55 pm Post subject: Granting select access on multiple tables |
 |
|
Is there a way to grant select privileges on ALL tables defined to your database? For example, "GRANT SELECT ON ALL TABLES TO username". The manuals show that the GRANT command is issued on a per table basis, but with thousands of tables it becomes complicated.
Thanks,
VMbigot
_________________
Inside every older man is a young boy asking this question. What the heck happened?!? |
|
| Back to top |
|
 |
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12383
Topics: 75
Location: San Jose
|
| Posted: Tue May 31, 2005 5:51 pm Post subject: |
|
|
VMbigot,
You can generate the GRANT statements dynamically and then run them in batch to grant select access to the tables.
First step generate the grant statements dynamically as below.
| Code: |
//STEP0100 EXEC PGM=IKJEFT01
//*
//SYSTSPRT DD SYSOUT=*,DCB=BLKSIZE=121
//SYSPRINT DD SYSOUT=*
//SYSTSIN DD *
DSN SYSTEM(xxx)
RUN PROGRAM(DSNTIAUL) -
PLAN(DSNTIAUL) -
PARMS('SQL') -
LIB('DB2P.RUNLIB.LOAD')
//SYSREC00 DD DSN=DATASET FOR GRANT STMTS,
// DISP=(NEW,CATLG,DELETE),
// UNIT=SYSDA,
// SPACE=(CYL,(10,10),RLSE)
//SYSPUNCH DD SYSOUT=*
//SYSIN DD *
SELECT CHAR('GRANT SELECT ON ') CONCAT
CHAR(NAME) CONCAT
CHAR(' TO USERNAME;')
FROM SYSIBM.SYSTABLES
WHERE DBNAME = 'your desired database name'
;
/*
|
Take a look at the output and if you are satisfied with the output then run the following step.
| Code: |
//STEP0200 EXEC PGM=IKJEFT01
//SYSTSIN DD *
DSN SYSTEM(xxx)
RUN PROGRAM(DSNTEP2) -
PLAN(DSNTEP2) -
LIB('DB2P.RUNLIB.LOAD')
END
//*
//SYSTSPRT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSIN DD DSN=DATASET FOR GRANT STMTS,
// DISP=SHR
/*
|
Hope this helps...
Cheers
Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
vmbigot
Beginner
Joined: 17 Jun 2004
Posts: 36
Topics: 14
Location: westminster, california
|
| Posted: Tue May 31, 2005 8:54 pm Post subject: |
|
|
Kolusu,
Now that's cool! Thanks for the quick reply and help. I had to make some modifications as our DB2 installer may have changed some IBM defaults, specifically the plan names for the utilities. The plans defined for the utilities were different so I had to search around to get the proper plan names. This DB2 subsystem is also running version 8 load modules but is in version 7 compatibility mode, making it even more confusing. I also had to add spaces to the end of the generated SELECT statement to fill up an 80 byte record. I kept getting a -104 error in the last step and couldn't figure out what I did wrong, so it was easier just to add the spaces in the SELECT statement. The modifications I made are as follows:
| Code: |
//STEP0100 EXEC PGM=IKJEFT01
//SYSTSPRT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSTSIN DD *
DSN SYSTEM(DB2T)
RUN PROGRAM(DSNTIAUL) -
PLAN(DSNTIB71) -
PARMS('SQL') -
LIB('DB2T.DSN.RUNLIB.LOAD')
//SYSREC00 DD DSN=DB2T.GRANT.ALL,
// DISP=(NEW,CATLG,DELETE),
// UNIT=SYSDA,
// SPACE=(CYL,(10,10),RLSE)
//SYSPUNCH DD SYSOUT=*
//SYSIN DD *
SELECT CHAR('GRANT SELECT ON TABLE HOURS.') CONCAT
CHAR(NAME) CONCAT
CHAR(' TO TFDBREAD ; ')
FROM SYSIBM.SYSTABLES
WHERE DBNAME = 'FDBHRS'
;
/*
//STEP0200 EXEC PGM=IKJEFT01
//SYSTSIN DD *
DSN SYSTEM(DB2T)
RUN PROGRAM(DSNTEP2) -
PLAN(DSNTEP71) -
LIB('DB2T.DSN.RUNLIB.LOAD')
END
//*
//SYSTSPRT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSIN DD DSN=DB2T.GRANT.ALL,
// DISP=SHR
|
Thanks again!
VMbigot
_________________
Inside every older man is a young boy asking this question. What the heck happened?!? |
|
| Back to top |
|
|
vmbigot
Beginner
Joined: 17 Jun 2004
Posts: 36
Topics: 14
Location: westminster, california
|
| Posted: Wed Jun 01, 2005 12:49 am Post subject: |
|
|
Kolusu,
Since your suggestion worked so well, I tried using a similar approach to grant execute privileges on packages as well. Below is what I am executing:
| Code: |
SELECT CHAR('GRANT EXECUTE ON PACKAGE ') CONCAT
RTRIM( CHAR(COLLID) ) CONCAT
CHAR('.') CONCAT
CHAR(NAME) CONCAT
CHAR(' TO TFDBREAD ;')
FROM SYSIBM.SYSPACKAGE
WHERE COLLID NOT LIKE 'NULL%' AND
COLLID NOT LIKE 'DSN%' AND
COLLID NOT LIKE 'QFW%'
;
|
Since the package collection id and name are variable in length, I need to string them together (with a period between) in order for the grant to work correctly. However, what I get in return is odd. It looks as if the first two bytes of each record contains the length of the record created (RDW).
| Code: |
..GRANT EXECUTE ON PACKAGE ELGFND7.ELGFND7 TO TFDBREAD ;...........
03CDCDE4CECCEEC4DD4DCCDCCC4CDCCDCF4CDCCDCF44ED4ECCCDCCC4500000000000
0779153057534350650713217505376547B537654700360364295140E00000000000
|
Other than the RDW at the beginning of each record, the RTRIM function works great but the result is obviously unusable. What am I doing wrong?
VMbigot
_________________
Inside every older man is a young boy asking this question. What the heck happened?!? |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12383
Topics: 75
Location: San Jose
|
| Posted: Wed Jun 01, 2005 8:40 am Post subject: |
|
|
vmbigot,
The extra 2 bytes you are getting in the output is due to the output of RTRIM function. The output result of the RTRIM function is VARCHAR if the argument is a character string. So use a CHar function on the concatenated string like example shown below.
| Code: |
SELECT CHAR('GRANT EXECUTE ON PACKAGE ')
,CHAR(RTRIM(COLLID) CONCAT
CHAR('.') CONCAT
CHAR(NAME) CONCAT
CHAR(' TO TFDBREAD ; '))
FROM SYSIBM.SYSPACKAGE
WHERE COLLID NOT LIKE 'NULL%'
AND COLLID NOT LIKE 'DSN%'
AND COLLID NOT LIKE 'QFW%'
;
|
Hope this helps...
Cheers
Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
vmbigot
Beginner
Joined: 17 Jun 2004
Posts: 36
Topics: 14
Location: westminster, california
|
| Posted: Wed Jun 01, 2005 10:45 am Post subject: |
|
|
Kolusu,
Outstanding! It worked like a charm. Now if I could only get my programmers to follow naming conventions this would make my life easier. But that's another project in itself.
Thanks for the help!
vmbigot
_________________
Inside every older man is a young boy asking this question. What the heck happened?!? |
|
| Back to top |
|
|
vak255
Intermediate
Joined: 10 Sep 2004
Posts: 384
Topics: 79
|
| Posted: Thu Jul 26, 2007 2:19 pm Post subject: |
|
|
Is there any GRANT priviledge on PLAN?
like GRANT EXECUTE ON PLAN XYZ.* TO definer/user; |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12383
Topics: 75
Location: San Jose
|
|
| Back to top |
|
|
dbzTHEdinosauer
Supermod
Joined: 20 Oct 2006
Posts: 1411
Topics: 26
Location: germany
|
| Posted: Thu Jul 26, 2007 3:28 pm Post subject: |
|
|
thanks kolusu and to vmbigot,
this tip and trick was definitely a keeper - or should I say a PDFer.
dick
_________________
Dick Brenholtz
American living in Varel, Germany |
|
| Back to top |
|
|
vak255
Intermediate
Joined: 10 Sep 2004
Posts: 384
Topics: 79
|
| Posted: Mon Jul 30, 2007 1:00 pm Post subject: |
|
|
| Thanks Kolusu. |
|
| Back to top |
|
|
|
|
|
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
