Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Grant access to tables

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
diwa_thilak

Active User


Joined: 13 Jul 2006
Posts: 205
Location: At my desk

PostPosted: Fri Jan 11, 2008 2:50 pm    Post subject: Grant access to tables
Reply with quote

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
View user's profile Send private message

dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Fri Jan 18, 2008 2:51 am    Post subject:
Reply with quote

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
View user's profile Send private message
diwa_thilak

Active User


Joined: 13 Jul 2006
Posts: 205
Location: At my desk

PostPosted: Fri Jan 18, 2008 10:17 am    Post subject: Reply to: Grant access to tables
Reply with quote

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
View user's profile Send private message
murmohk1

Senior Member


Joined: 29 Jun 2006
Posts: 1439
Location: Bangalore,India

PostPosted: Fri Jan 18, 2008 11:20 am    Post subject:
Reply with quote

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
View user's profile Send private message
diwa_thilak

Active User


Joined: 13 Jul 2006
Posts: 205
Location: At my desk

PostPosted: Fri Jan 18, 2008 11:23 am    Post subject:
Reply with quote

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
View user's profile Send private message
murmohk1

Senior Member


Joined: 29 Jun 2006
Posts: 1439
Location: Bangalore,India

PostPosted: Fri Jan 18, 2008 11:45 am    Post subject:
Reply with quote

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
View user's profile Send private message
diwa_thilak

Active User


Joined: 13 Jul 2006
Posts: 205
Location: At my desk

PostPosted: Fri Jan 18, 2008 12:14 pm    Post subject:
Reply with quote

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
View user's profile Send private message
murmohk1

Senior Member


Joined: 29 Jun 2006
Posts: 1439
Location: Bangalore,India

PostPosted: Fri Jan 18, 2008 12:24 pm    Post subject:
Reply with quote

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
View user's profile Send private message
murmohk1

Senior Member


Joined: 29 Jun 2006
Posts: 1439
Location: Bangalore,India

PostPosted: Fri Jan 18, 2008 12:32 pm    Post subject:
Reply with quote

Also forgot to put -

Quote:
PO_ALLOC_%)

Cant use in GRANT statement.
Back to top
View user's profile Send private message
diwa_thilak

Active User


Joined: 13 Jul 2006
Posts: 205
Location: At my desk

PostPosted: Fri Jan 18, 2008 12:33 pm    Post subject:
Reply with quote

Yeah Murali. You are right..

My doubt is .. can we grant accesss to tables at database level ?
Back to top
View user's profile Send private message
murmohk1

Senior Member


Joined: 29 Jun 2006
Posts: 1439
Location: Bangalore,India

PostPosted: Fri Jan 18, 2008 12:50 pm    Post subject:
Reply with quote

Go thru 'sql reference' for the privileges granted on diff instances (db, table etc...).
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Fri Jan 18, 2008 8:40 pm    Post subject:
Reply with quote

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
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Identify top 30 big tables or indexes... ashek15 DB2 0 Fri Jun 16, 2017 10:01 am
No new posts find RACF group for access to spooled... jzhardy JCL & VSAM 1 Mon May 08, 2017 11:46 am
No new posts Data replication from multiple Db2 ta... kishpra DB2 9 Mon Mar 27, 2017 9:58 pm
No new posts SPUFI -- Joining 3 tables – data in... Sysaron DB2 2 Wed Mar 08, 2017 4:18 am
No new posts To know activities on db2 tables ashek15 DB2 4 Fri Mar 03, 2017 11:23 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us