?
Solved

DB2 database access

Posted on 2004-08-10
2
Medium Priority
?
706 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 1000 total points
ID: 11767426
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
ID: 11777038
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

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

November 2009 Recently, a question came up in the DB2 forum regarding the date format in DB2 UDB for AS/400.  Apparently in UDB LUW (Linux/Unix/Windows), the date format is a system-wide setting, and is not controlled at the session level.  I'm n…
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…
To export Lotus Notes to Outlook PST or Exchange and Domino Server files to Exchange Server or PST files with ease, go for Kernel for Lotus Notes to Outlook conversion tool. Through the video, you can watch the conversion process. A common user with…
Watch the software video of Kernel Import PST to Office 365 tools which can easily import PST and OST files to Office 365 for bulk mailboxes. The process of migration is simple and user can map source and destination mailboxes and easily import data…

585 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