View previous topic :: View next topic
|
Author |
Message |
diwa_thilak
Active User
Joined: 13 Jul 2006 Posts: 205 Location: At my desk
|
|
|
|
Hi,
I need to grant(insert and update) access to all tables in a database DBPREP1 to specific users.
But the problem is, the tables in the database are in different tablespaces.
Is there any other way to grant access to all the tables using a query ?
Any other alternative soln for this problem? |
|
Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
|
|
|
|
Hello,
Quote: |
But the problem is, the tables in the database are in different tablespaces. |
Why is this a concern? Permissions are granted on the table-name not the tablespace.
Quote: |
Is there any other way to grant access to all the tables using a query ? |
An other way than what? |
|
Back to top |
|
|
diwa_thilak
Active User
Joined: 13 Jul 2006 Posts: 205 Location: At my desk
|
|
|
|
Hi,
Thanks for your reply.
Yeah you are right, we need to give only the table name for granting access, Please let me know, how to grant access (insert/update) to all the tables in a database ?
My another question is whether is it possible to grant acess to tables by using the high level qualifier (Ex: PO_ALLOC_%) ? |
|
Back to top |
|
|
murmohk1
Senior Member
Joined: 29 Jun 2006 Posts: 1436 Location: Bangalore,India
|
|
|
|
Diwakar,
Quote: |
Please let me know, how to grant access (insert/update) to all the tables in a database ? |
Do you have manuals/qw to refer? |
|
Back to top |
|
|
diwa_thilak
Active User
Joined: 13 Jul 2006 Posts: 205 Location: At my desk
|
|
|
|
Murali,
Thanks for your reply.
I tried a couple of manuals, but they discuss about granting access to single table. I need to grant acces to all the tables or set of tables using high level qualifier ?
If you got any different view or manual, do let me know... |
|
Back to top |
|
|
murmohk1
Senior Member
Joined: 29 Jun 2006 Posts: 1436 Location: Bangalore,India
|
|
|
|
Diwakar,
Code: |
GRANT <authorities> ON TABLE <table1>, <table2>, .... ,<tablen> TO
<user1>, <user2>,....,<usern>; |
In the above, all users get 'authorities' on the given 'tbales'. |
|
Back to top |
|
|
diwa_thilak
Active User
Joined: 13 Jul 2006 Posts: 205 Location: At my desk
|
|
|
|
Thanks Murali,
Do you got any idea how to grant access to all the tables in one shot (without specifying the table names) ? |
|
Back to top |
|
|
murmohk1
Senior Member
Joined: 29 Jun 2006 Posts: 1436 Location: Bangalore,India
|
|
|
|
diwa,
Quote: |
Do you got any idea how to grant access to all the tables in one shot (without specifying the table names) ? |
I dont think DB2 can read whats there in your mind. It expect some instance(s) on which the users get access/authority. |
|
Back to top |
|
|
murmohk1
Senior Member
Joined: 29 Jun 2006 Posts: 1436 Location: Bangalore,India
|
|
|
|
Also forgot to put -
Cant use in GRANT statement. |
|
Back to top |
|
|
diwa_thilak
Active User
Joined: 13 Jul 2006 Posts: 205 Location: At my desk
|
|
|
|
Yeah Murali. You are right..
My doubt is .. can we grant accesss to tables at database level ? |
|
Back to top |
|
|
murmohk1
Senior Member
Joined: 29 Jun 2006 Posts: 1436 Location: Bangalore,India
|
|
|
|
Go thru 'sql reference' for the privileges granted on diff instances (db, table etc...). |
|
Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
|
|
|
|
Hello,
Quote: |
My doubt is .. can we grant accesss to tables at database level ?
|
No, we cannot (as far as i know).
What is quite common is to generate all of the grant statements for the tables in a database and then issue the appropriate grants as needed. A simple query can identify all of the tables for the database and a bit of code or editing can turn the list of table names into the grant statements. |
|
Back to top |
|
|
|