Solved

DB2 database access

Posted on 2004-08-10
2
662 Views
Last Modified: 2008-03-03
Hi,

I am facing a very peculiar type of problem. I logged in with the username db2admin and created a database TEST. I have tables and stored procedures defined in that TEST db.
Now when I loggin with some other user (to whom I have given all the previleges), it says that db2admin.tablename is not accessible.

Why is it happening that db2 by default is attaching the name of the schema/username with the table name or stored procedure name ?

Due to this problem, the database access is purely dependent on db2admin user. Is DB2 user dependent ?

Rgds,
Inderjeet
0
Comment
Question by:isbhatia
2 Comments
 
LVL 50

Accepted Solution

by:
Lowfatspread earned 250 total points
Comment Utility
with any dbms you need to properly name your objects...

in most the default is to iuse the "USerid" as the Schema or Owner name of the component
if you;ve not explicitly stated it to be otherwise.

your simplest option is probably to

get the user to issue a

set current schema DBADMIN;

then  the DBADMIN will  automtically be assumed to be the qualifier for any  operations they issue without a
specifically named version.



 
0
 
LVL 13

Expert Comment

by:ghp7000
Comment Utility
lowfat is correct, that would be one way. You have to keep in mind that the privlidges you assign only mean that user a can access tables created by user b in either select, delete, insert or update mode. By default, tables created by user a cannot be accessed by any other user. When user b is given the privledge to access tables created by user a, user b must qualify the table name with the schema name of user a. You can see this by creating a table name test containing 1 record when you are logged in as user a and then create the same table with a different record when logged in as another user. Assign the select privlidge to both users on each other's table. When you issue select * from test, you will get the record inserted with the same schema name as the current user. If you issue select * from a.test, you will get the record inserted when user a created the table.
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Recursive SQL in UDB/LUW (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
Recursive SQL in UDB/LUW (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now