Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2008
    Posts
    76

    Unanswered: Grant statements for an id

    Hi,

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

    Thanks
    Panditt

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    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

  3. #3
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by deshaipet View Post
    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
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    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

  5. #5
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by ARWinner View Post
    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?
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  6. #6
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655
    Thanks Peter

  7. #7
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    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.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •