Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question
up vote 3 down vote accepted

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.

share|improve this answer
thanks, db2 grant dataaccess works. I don't mind to grant them to all schemas as I'm in development environment. – suud Jun 5 '13 at 10:43

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.

      'GRANT Select ON TABLE '
   || rtrim (tabschema)
   || '.'
   || rtrim (tabname)
  FROM syscat.tables
 WHERE tabschema = 'Schema'
   || rtrim (VIEWSCHEMA)
   || '.'
   || rtrim (viewname)
share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.