Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2010
    Posts
    31

    Unanswered: grant permission on tables to public

    Hi,

    I am trying to grant permission to all the tables in a particular schema. There are more than 100 tables in that schema and I do not want to type the grant statement for every table like

    grant select on table1 to public;
    grant select on table2 to public;
    ....
    ....

    I tried to use the following statement.

    select 'grant select on',tabname,' to public' from syscat.tables where tabschema='ABC'

    This statement creates the select grant statements but does not grant permission. If I use this statement, then I have to export the output to a text file and then execute that text file which I do not want.

    I am using DB2 command editor and I do not want to use Linux shell utility.

    Can someone please help.

    Thanks in advance.

    Nav

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Unfortunately there is not much we can do to help you. You have stated the only options that will allow you to do what you want.

    Andy

  3. #3
    Join Date
    Jan 2010
    Posts
    31
    Ok, if it can not be done in one statement using command editor then how should it be done using other platforms.

    Nav

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    You can do it either of the two ways you mentioned. Use a select statement to generate a script that will do it and then run that script, or use a linux script to do the same thing.


    Andy

  5. #5
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    navch, If you use Control Center, you can do this.

    Expand User and Group objects under the database.
    Click on Groups.
    Right click on Public and select Change.
    Click on the Table tab.
    Click on Add Table.
    Enter or Select a Schema.
    Highlight all the tables (CNTL+A if you want everything) and click Ok.
    Highlight all the tables (Click the first one, hold SHIFT key down and click the last one).
    Change the Select Drop down box to Yes.
    Click OK.

Posting Permissions

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