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?