Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2005
    Posts
    8

    Unanswered: Command Required

    Hi,

    I have around 10,000 tables in my db2 database. I have to grant a user insert privilege to all the tables in the database. Please let me know the command to perform this grant operation in a single shot.

    Regards,
    Santhosh

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    You cannot do that with one command, but you can build a script that will do that:

    db2 connect to XXXXX

    db2 "select 'grant insert on table '||rtrim(tabschema)||'.'||rtrim(tabname)||' to authorization-name;' from syscat.tables where tabschema <> 'SYSIBM' and type = 'T'"

    Pipe the output to a file. Edit the file a bit and submit it to grant the inserts.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Oct 2005
    Posts
    8
    Thanks Marcus. I am able to generate the script and grant insert privileges to the particular user to all the tables in the database.

  4. #4
    Join Date
    Oct 2005
    Posts
    8
    Marucs,

    you used rtrim(tabschema) for character string values. If I have to pass integer variable say "tbspaceid" in the below command I am facing the errors.

    C:\>db2 "select 'list tablespace container for '||rtrim(tbspaceid)||' with detail' from syscat.table
    spaces"
    SQL0440N No authorized routine named "RTRIM" of type "FUNCTION" having
    compatible arguments was found. SQLSTATE=42884

    Regards,
    Santhosh

  5. #5
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    You need to convert it to character:

    db2 "select 'list tablespace containers for '||rtrim(cast (tbspaceid as char(3)))||' show detail' from syscat.tablespaces"
    Last edited by Marcus_A; 10-19-05 at 03:52.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  6. #6
    Join Date
    Oct 2005
    Posts
    8
    Thanks marcus. I am able to get the output.

Posting Permissions

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