Results 1 to 9 of 9
  1. #1
    Join Date
    Jan 2008
    Posts
    88

    Unanswered: select privilege

    Hi,

    may be a basic question...but does anyone know that how to give select(or any privilege ) on all the tables for that database in one command to a particular user.

    like if i want to give only select access to a user on all the database den i have to give a seperate commad for all the table. So I wanted to give in using one command.

    please let me know if anyone has any idea

  2. #2
    Join Date
    Dec 2005
    Posts
    273
    There is no generic GRANT table-privilege for all tables of a database. You have to list each table.
    Of course you can generate the GRANT-statements with SQL.

    e.g.

    SELECT
    ' GRANT SELECT ON TABLE '
    concat STRIP(creator)
    concat '.'
    concat STRIP(name)
    concat ' TO userid ; '
    FROM SYSIBM.SYSTABLES
    WHERE DBNAME = 'your-DBname' ;

  3. #3
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by umayer
    There is no generic GRANT table-privilege for all tables of a database.
    But by granting the DBADM privilege, the user gets SELECT auth (and much more) on all tables of a database -- that is, on DB2 for z/OS.
    Is this also the case for L/U/W or iSeries?
    --_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
    Quote Originally Posted by Peter.Vanroose
    But by granting the DBADM privilege, the user gets SELECT auth (and much more) on all tables of a database -- that is, on DB2 for z/OS.
    Is this also the case for L/U/W or iSeries?
    It is the same for LUW.

    Andy

  5. #5
    Join Date
    Jan 2008
    Posts
    88
    But I want to only give select authority i.e. read only.

  6. #6
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by azs0309
    But I want to only give select authority i.e. read only.
    In that case your only option is to give it on a table-by-table basis.
    Use UMayer's suggestion for generating the GRANT statements.
    Be aware that there is no way of automatically granting SELECT auth for "future", i.e., not yet existing, tables!
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  7. #7
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    I wonder if it is possible to put an insert trigger on syscat.tables to do this?
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  8. #8
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by Marcus_A
    I wonder if it is possible to put an insert trigger on syscat.tables to do this?
    Good idea! Never tried that.
    Actually, shouldn't it be on sysibm.systables instead? I seem to recall that syscat.tables is a view on sysibm.systables.
    There could be restrictions on putting GRANT statements in triggers, though. I've had issues with that on z/OS.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  9. #9
    Join Date
    Dec 2005
    Posts
    273
    Quote Originally Posted by Marcus_A
    I wonder if it is possible to put an insert trigger on syscat.tables to do this?

    no.
    Catalog tables, auxiliary tables, temporary tables and MQTs
    can't be specified as the triggering table
    ( alias or synonyms can't be specified, too )

Posting Permissions

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