DB2 Table privilage

[Amphrodite1]

Hi There
I am using IBM DB2 7.2 and we have one db running in db2 7.2. I created a new user for report creation. How i can give him the read permission for all the tables of the DB using the command. Pls help

 

[gregsimpson]

Amphrodite1,

try something along the lines of

GRANT CONNECT ON DATABASE TO USER AMPHRODITE1

Cheers
Greg

 

[Amphrodite1]

But it will give privilage to connect to database only, when i select some tables it is not connecting.There are around 450 tables i want to give select privilage to all the tables with the command.

 

[gbag]

You need to generate a script for granting the select privilegs on all the tables.
Write the following lines of SQL in a file called get_tabs.sql.

connect to <database>;
select tabname from syscat.tables where tabschema='<some_schema'>'

Then execute the sql as

db2 -tvf get_tabs.sql >tabs


With this you will get all the tables pertaining to a schema.This will be saved in the file 'tabs'.
Then you write a shell script to generate the SQL scritp for granting select privilege as :

>grant_sel.sql
for i in `cat tabs` ## Note that is grave-accent not quote
do
echo "grant select on <some_schema>.$i" >>grant_sel.sql
done

After execution of this, you will get a file grant_sel.sql. You run that SQL as

db2 -tvf grant_sel.sql

This should do the required granting the select privilege on all the tables.
Let me know if you run into any problems.
Good Luck
Gbag.

 

[Amphrodite1]

we are using only one schema named trade , I created one user name for report creation named report and given select privilage to all tables but when i select any tables with the report username it is showing the error

report.tablename does not exist
I think that we have to create another schema in the name report.If so can you pls explain how to create and how to add all tables in that schema.

 

[gbag]

Some questions:
Have you tried above methods?
If you have tried above or other method , let us know which method you used?
What error you got?
Note that even if you are some other user[x], you can access table owned by y like
select * from y.tab1;
or sometimes you need to put the schema in quotations as
select * from 'y'.tab1; or
select * from "y.tab1";
This schema and tabname should be upper case as you see in the control-center.
Post the method you used and error you got.
Good luck.
gbag

 

[Amphrodite1]

I used control centre to add the user. The report creation is in crystal report for that i created a new user named report.If I try from the command centre
eg:
seclect * from tablename
it will show the error
report.tablename not excisting

 

[blom0344]

If you do not specify a schema name . DB2 will assume that the account = schemaname.

So, add the proper schemaname to the target:

Select * from schema.tablename

T. Blom
Information analyst
tbl@shimano-eu.com