Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2004
    Posts
    1

    Unanswered: Grant SELECT to all tables in schema

    New to DB2 and am wanting to grant SELECT to all tables in a schema to a group. Am hoping not to have to do this by individual tables (several thousand), but instead loop through all matching tables in SYSCAT.TABLES where TABSCHEMA = 'myschema'.

    Anyone done this before?

    Thanks.

  2. #2
    Join Date
    Aug 2003
    Posts
    106
    This is what the syntax looks like:

    GRANT SELECT TO TABLE {TABLE NAME}

    Looking at the syntax there is no provision for entering multiple table names.

    What I would do is to do a select of all the tables and capture it in a text file. And do find replace.

    STEP 1:
    Capture the contents of the following select statement into text file say capture.sql using the select statement

    SELECT CONCAT TABSCHEMA CONCAT '.' TABNAME WHERE TABSCHEMA = 'myschema'

    So the file will have enteries like...

    myschema.table1
    myschema.table2

    STEP 2

    Use your favourite text editor and REPLACE like...

    REPLACE myschema with 'GRANT SELECT TO TABLE myschema'

    So
    myschema.table1
    myschema.table2

    will become...

    GRANT SELECT TO TABLE myschema.table1
    GRANT SELECT TO TABLE myschema.table2

    STEP 3
    And now run the capture.sql in CLI interface.

    Maybe there is better way others can suggest:-)

    You are the creator of your own destiny!

  3. #3
    Join Date
    Mar 2004
    Posts
    448
    Could do some thing like that

    db2 -x +o -z grant.ddl "select 'grant select on '||'.'|| tabschema '||' to group test;' from syscat.tables where tabschema ='DB2INST1'"

    // Change the DB2INST1 to your schema name and group test to your group


    check the file

    more grant.ddl

    run

    db2 -tvf grant.ddl


    regards,

    mujeeb

Posting Permissions

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