How to grant all privileges on all tables in a schema to a user in IBM DB2?

Question

At first place, I want to export database from IBM DB2 AIX into IBM DB2 windows. Unfortunately, I can't use BACKUP and RESTORE command because of difference OS issue. So I have to resort to db2move command.

I had exported myschema schema and all of tables from dbemp database in remote IBM DB2 AIX using this command:

db2move dbemp export -sn myschema

When I run that command I was login as user named dbuser1.

It generates bunch of files in the current folder. I grab the files into my local machine (windows) which has IBM DB2 LUW installed.

Now in my local machine's IBM DB2, I dropped the existing schema myschema first by using IBM Data Studio (I also had a local dbemp database too).

Then I imported the files using this command:

db2move dbemp import

It successfully import the schema and tables into local dbemp database.

When I did above actions, I was login as user winuser1.

I have a local user named dbuser1 too in my local machine (windows). In IBM Data Studio, I created a connection profile that connect to local dbemp using dbuser1 user and I can browse myschema schema and the tables, but I can't browse the table's data (I got privilege error). User winuser1 can browse table's data, but for development purpose, I have to connect to the database using user dbuser1.

So I found out that I can grant privilege to a user on the table like this:

GRANT ALL ON myschema.table1 TO USER dbuser1

The problem is I have 100 tables, I don't want to type that lines for each table. And unfortunately, there is no wildcard solution like this too:

GRANT ALL ON myschema.* TO USER dbuser1 -- this doesn't work

So the question is how to grant all privileges on all tables in a schema to a user? Or is there alternative better solution? maybe by copying all privileges from winuser1 to dbuser1?

Answer 1

If you want access to all data (ie, all tables in all schemas), you would need to grant dataaccess.

db2 grant dataaccess on database to user winuser1

If you only want winuser1 to access just the 100 tables in the schema you are referring to, then unfortunately, there is no easy way, you would need to grant SELECT on each table. That being said, it can be accomplished through scripting.

You could do the following

db2 -tnx "select distinct 'GRANT ALL ON TABLE '||
    '\"'||rtrim(tabschema)||'\".\"'||rtrim(tabname)||'\" TO USER winuser1;'
    from syscat.tables
    where tabschema = 'myschema' "  >> grants.sql

db2 -tvf grants.sql

This makes use of querying the system catalogs to dynamically generate a script to permission things. This is a lot of how we permission for users we don't want to give dataaccess to.

Here is a good page of the authorities for DB2.

Answer 2

You can also try (using an SQL editor):

BEGIN
 FOR v AS cur CURSOR FOR 
    select 
        'GRANT SELECT, INSERT, UPDATE, DELETE ON ' || trim(tabschema) || '.' || trim(tabname) || ' TO YourUser' stmt 
    from 
        syscat.tables 
    where 
        tabschema = 'YourSchema' 
  DO 
    EXECUTE IMMEDIATE v.stmt;
  END FOR;
END

Answer 3

If you are using a SQL editor like Toad, this script may help as well. You can copy/paste the results in a new window.

SELECT DISTINCT
      'GRANT Select ON TABLE '
   || rtrim (tabschema)
   || '.'
   || rtrim (tabname)
   || ' TO USER USERNAME;'
  FROM syscat.tables
 WHERE tabschema = 'Schema'
 UNION
 SELECT    'GRANT Select ON VIEW '
   || rtrim (VIEWSCHEMA)
   || '.'
   || rtrim (viewname)
   || ' TO USER USERNAME;'
 FROM SYSCAT.VIEWS
 WHERE VIEWSCHEMA = 'Schema';