Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Jul 2003
    Posts
    9

    Unanswered: grant select questions

    Is there a way of granting select to all tables for a user, e.g.

    GRANT SELECT ON * TO USER TEST_USER

    Is there a way to grant select to specific attributes within a table? For instance MySQL will apparently let you type:

    GRANT SELECT (COL_A,COL_B,COL_C) ON TEST_TABLE TO TEST_USER

    I've tried the above in DB2's Command Center and get a SQLSTATE=42601 error. However a similar update statement is accepted without problem. For instance:

    GRANT UPDATE(COL_A) ON TEST_TABLE TO TEST_USER

    Thanks,

    JR

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    GRANT SELECT on columns is not supported. But you can create a View which only contains the columns you want to authorize access to, and then GRANT SELECT on that View.

  3. #3
    Join Date
    Jul 2003
    Posts
    9
    Thanks for that Marcus, I was beginning to think that much. Do you know of a what of granting select on all tables (apart from granting them individually)?

    Regards

    JR

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    No. GRANT SELECT on * is not supported. But you can write an SQL statement to build the list of Grants that you want:

    select "grant select on ", tabname, "to user1;"
    from syscat.tables
    where .....

    If you need the schema name in front of the table, then you could concatenate tabschema, ".", tabname. This would have to be done without trailing blanks in the tabschema using RTRIM.

  5. #5
    Join Date
    Jul 2003
    Posts
    9
    Crafty solution Marcus! Ta very much.

  6. #6
    Join Date
    Jul 2003
    Posts
    9
    It seems that doing anything with DB2 involves searching for hours, making a minor step forward, and then repeating the process with a new error.

    I've taken your suggestions and produced the following:

    SELECT "GRANT SELECT ON ",TABNAME," TO HAPPY;"
    FROM SYSCAT.TABLES
    WHERE TABSCHEMA='DB2ADMIN';

    The problem is that I'm now getting the following error:

    SQL0010N The string constant beginning with "" TO HAPPY" does not have an ending string delimiter. SQLSTATE=42603

    DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned: SQL0010N The string constant beginning with "" FROM SYSCAT.TABLES WHERE TABSCHEMA='DB2ADMIN'" does not have an ending string delimiter. SQLSTATE=42603

    Is the string delimiter the same as the statement termination character (i.e. ; ). If so, then that is what is already set in "Tools Settings".

  7. #7
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    I am not sure where you are submitting this from, but you may need a single quote (') or some way to indicate the string delimiter. Sorry for the confusion.

  8. #8
    Join Date
    Jul 2003
    Posts
    9
    Thanks for your patience. I was submitting the text in the command center and have tried both double and single quotes.

  9. #9
    Join Date
    Jul 2003
    Posts
    9
    Thanks, but I'm afraid that I still get the same error even when I try the following:

    SELECT 'GRANT SELECT ON',TABNAME,'TO HAPPY;'
    FROM SYSCAT.TABLES
    WHERE TABSCHEMA='DB2ADMIN';

    I'm finding DB2 very frustrating to learn as it seems that every little step takes hours of searching to find out what the problem is.

    PS There was another post here (now deleted) that suggested single quotes.
    Last edited by rudgej; 07-13-03 at 13:50.

  10. #10
    Join Date
    Jul 2003
    Posts
    9
    DB2 is playing true to form. After a couple of frustrating hours, I finally managed it with the following:

    SELECT 'GRANT SELECT ON ' || TABNAME || ' TO USER HAPPY'
    FROM SYSCAT.TABLES
    WHERE TABSCHEMA='DB2ADMIN';

    Regards

    JR

  11. #11
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    I'm not sure what the problem is with your original query ... I tried the same on my Linux Box(using db2 -tvf ) and it was working OK ...

    The query suggested by Marcus_A generates the select column as 3 columns and what you have done is concatenation of the three columns...



    Cheers

    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  12. #12
    Join Date
    Jul 2003
    Posts
    9
    No it beats me too. I tried every combination of things that I could find, and by trial and error got one that worked. I find this to be very frustrating with DB2, but since I'm only using it for the next few weeks, the end is in sight...

  13. #13
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    I tried the following using the Comand Center (remotely) on DB2 7.2 FP9 on Windows.

    SELECT 'GRANT SELECT ON',TABNAME,'TO HAPPY;'
    FROM SYSCAT.TABLES
    WHERE TABSCHEMA='DB2ADMIN';

    The problem is the embedded ';' after HAPPY, which DB2 thinks is a statement termination character.

    I tried changing or disabling the statement termination character ";" in the Tool Settings, but that did not seem to work.

  14. #14
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Just to clarify, the problem is with the GUI, not the DB2 server, since as Sathyaram pointed out, the following works from the command line with the embedded ';' termination character:

    db2 -tvf input_query.txt -r output.txt
    Last edited by Marcus_A; 07-13-03 at 19:47.

  15. #15
    Join Date
    Nov 2003
    Location
    Oslo, Norway
    Posts
    2
    Hi
    To get this to work in the rather stupid command centre 7.2 GUI you have to put it into one line and drop the ';' delimiter:

    SELECT 'GRANT SELECT ON ' || TABNAME || ' TO USER HAPPY' FROM SYSCAT.TABLES WHERE TABSCHEMA='DB2ADMIN'

    results in:

    GRANT SELECT ON ACC_VIEW TO USER HAPPY
    GRANT SELECT ON AGENT TO USER HAPPY
    GRANT SELECT ON AGENT_CON TO USER HAPPY
    GRANT SELECT ON AGENT_CUSTOMER TO USER HAPPY
    GRANT SELECT ON AGENT_ORDER TO USER HAPPY
    GRANT SELECT ON CUST_SUM TO USER HAPPY
    GRANT SELECT ON CUSTOMER TO USER HAPPY
    GRANT SELECT ON EXPLAIN_INSTANCE TO USER HAPPY
    GRANT SELECT ON EXPLAIN_STATEMENT TO USER HAPPY
    GRANT SELECT ON NUM_ORDERS TO USER HAPPY
    GRANT SELECT ON ORD_LINE TO USER HAPPY
    GRANT SELECT ON ORD_ORD_LINE TO USER HAPPY
    GRANT SELECT ON ORD_ORDER_LINE TO USER HAPPY
    GRANT SELECT ON ORD_TOTAL TO USER HAPPY
    GRANT SELECT ON ORDER TO USER HAPPY
    GRANT SELECT ON ORDER_COST TO USER HAPPY
    GRANT SELECT ON PROD_CUST_CITY TO USER HAPPY
    GRANT SELECT ON PRODUCT TO USER HAPPY
    GRANT SELECT ON R1 TO USER HAPPY
    GRANT SELECT ON R2 TO USER HAPPY

    20 record(s) selected.

    So it works, it is just the stupid java GUI, even after installing DB7.2PE Fixpack10 I still cant use a multiline SQL statement and the ';' delimiter.

    I have some triggers that overflows the 255 character line limit. How am I supposed to get them working.

Posting Permissions

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