Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2008
    Posts
    83

    Unanswered: Table Access issue

    Hello All,

    Can you please help me on below issue.

    DB2 Version : 9.7
    OS : AIX

    There is a table called Sample and 2 users A and B are accessing that table. A user is having dbadm privileges and the A user is dropping and recreating the table. So here the problem is after the table got recreated with A user the access for the B user is getting lost, we are every time manually giving select permissions to B user.

    IS there any way to get Access to B user directly once the table recreates with A user.

    Thanks In advance.
    Ramesh

  2. #2
    Join Date
    Jul 2016
    Location
    Moscow
    Posts
    294
    Provided Answers: 45
    Hello,

    Use ADMIN_MOVE_TABLE procedure instead of drop/create table statements.
    Regards,
    Mark.

  3. #3
    Join Date
    Oct 2007
    Posts
    161
    Provided Answers: 9
    I think the better question would be... why do you drop and recreate the table repeatedly?

  4. #4
    Join Date
    Aug 2016
    Posts
    55
    Provided Answers: 2
    Is there advantage of dropping and recreating of tables in transaction logging like minimal impact ?.

    it its set to non logging the table, load replace options.

    just for understanding -- admin_table_move is used for moving data between tables, with has advantage of same name,

    but if the user is dropping the table he dont need the data correct ? .

    how can admin_move_table will be useful here ? .

    regds
    paul

  5. #5
    Join Date
    Jul 2016
    Location
    Moscow
    Posts
    294
    Provided Answers: 45
    Quote Originally Posted by mpaul View Post
    how can admin_move_table will be useful here ?
    The initial problem was: grants on a table are not preserved if you just drop and create a table with the same name.
    admin_move_table preserves grants.
    An alternative solution could be using myschema.mytable.ddl from the following command:
    db2look -d mydb -e -x -z myschema -t mytable -o myschema.mytable.ddl

    One can drop the table, change the CREATE TABLE statement accordingly, and run this script to recreate the table and all grants on it.
    Regards,
    Mark.

Posting Permissions

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