Grant statements for an id

deshaipet · #1 (**permalink**) 09-12-11, 13:07

Hi,

I am trying to extract grant statements for only one id using db2look.
can someone please provide me the command ?

Thanks
Panditt

ARWinner · #2 (**permalink**) 09-12-11, 13:15

There is no option for db2look to do that. Just generate the the GRANT statements for everything and the use something like grep to filter what you want.

Andy

Peter.Vanroose · #3 (**permalink**) 09-12-11, 16:47

Quote:

Originally Posted by deshaipet

Hi,

I am trying to extract grant statements for only one id using db2look.
can someone please provide me the command ?

You could start from

Code:

SELECT * FROM syscat.tabauth WHERE grantee = 'THE_ID'

(of course, replacing "THE_ID" by the real id).

Something like

Code:

SELECT 'GRANT'||
CASE ALTERAUTH WHEN 'Y' THEN ' ALTER' ELSE '' END ||
CASE DELETEAUTH WHEN 'Y' THEN ' DELETE' ELSE '' END ||
CASE INSERTAUTH WHEN 'Y' THEN ' INSERT' ELSE '' END ||
CASE SELECTAUTH WHEN 'Y' THEN ' SELECT' ELSE '' END ||
CASE UPDATEAUTH WHEN 'Y' THEN ' UPDATE' ELSE '' END ||
' ON TABLE '||rtrim(TABSCHEMA)||'.'||rtrim(TABNAME)||' TO '||grantee||';'
FROM syscat.tabauth WHERE grantee = user

ARWinner · #4 (**permalink**) 09-12-11, 16:58

Peter,
It is more complicated than that. You need to put a comma between each authorization, but not before the first and not after the last. That will make the SQL real nasty looking. It would probably be easier to do each authorization separately as a single output statement. Plus the OP will want it for all objects, not just tables.

Andy

Peter.Vanroose · #5 (**permalink**) 09-12-11, 17:42

Quote:

Originally Posted by ARWinner

It is more complicated than that.

Agreed. I also left out some authorisation columns, like INDEXAUTH.
The nasty details like the comma's are left as an exercise for the reader

I'd guess the precise queries for doing exactly this have been written already several times by several people, and should be available somewhere?

DBFinder · #6 (**permalink**) 09-12-11, 20:30

Thanks Peter

Peter.Vanroose · #7 (**permalink**) 09-13-11, 03:35

New attempt (still not 100% perfect of course, but this time the outcome should be syntactically correct, and it should be clear how to extend this if wanted):

Code:

WITH au(t,g,s,i,u,d,a,x,r,c) AS
(SELECT rtrim(TABSCHEMA)||'.'||rtrim(TABNAME),rtrim(GRANTEE),
        selectauth,insertauth,updateauth,deleteauth,alterauth,indexauth,refauth,controlauth
 FROM syscat.tabauth WHERE grantee = 'Name')
SELECT 'GRANT SELECT ON TABLE '||t||' TO '||g
       ||CASE s WHEN 'G' THEN ' WITH GRANT OPTION' ELSE '' END
       ||';' FROM au WHERE s IN ('G','Y')
UNION ALL
SELECT 'GRANT INSERT ON TABLE '||t||' TO '||g
       ||CASE i WHEN 'G' THEN ' WITH GRANT OPTION' ELSE '' END
       ||';' FROM au WHERE i IN ('G','Y')
UNION ALL
SELECT 'GRANT UPDATE ON TABLE '||t||' TO '||g
       ||CASE u WHEN 'G' THEN ' WITH GRANT OPTION' ELSE '' END
       ||';' FROM au WHERE u IN ('G','Y')
UNION ALL
SELECT 'GRANT DELETE ON TABLE '||t||' TO '||g
       ||CASE d WHEN 'G' THEN ' WITH GRANT OPTION' ELSE '' END
       ||';' FROM au WHERE d IN ('G','Y')
UNION ALL
SELECT 'GRANT ALTER ON TABLE '||t||' TO '||g
       ||CASE a WHEN 'G' THEN ' WITH GRANT OPTION' ELSE '' END
       ||';' FROM au WHERE a IN ('G','Y')
UNION ALL
SELECT 'GRANT INDEX ON TABLE '||t||' TO '||g
       ||CASE x WHEN 'G' THEN ' WITH GRANT OPTION' ELSE '' END
       ||';' FROM au WHERE x IN ('G','Y')
UNION ALL
SELECT 'GRANT REFERENCES ON TABLE '||t||' TO '||g
       ||CASE r WHEN 'G' THEN ' WITH GRANT OPTION' ELSE '' END
       ||';' FROM au WHERE r IN ('G','Y')
UNION ALL
SELECT 'GRANT CONTROL ON TABLE '||t||' TO '||g
       ||CASE c WHEN 'G' THEN ' WITH GRANT OPTION' ELSE '' END
       ||';' FROM au WHERE c IN ('G','Y')

This lists all table, view, and nickname privileges (except for the column-specific update & references privs) granted to user "Name".
Other authorisation tables in SYSCAT are COLAUTH, DBAUTH, INDEXAUTH, LIBRARYAUTH, PACKAGEAUTH, PASSTHRUAUTH, ROUTINEAUTH, SCHEMAAUTH, SEQUENCEAUTH, TBSPACEAUTH, and XMLOBJECTAUTH.
They have a similar structure and can thus be queried in a similar way as above.

Internet Services

Web Development

Digital Marketing

Consumer Tech