Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

DB2 database access

Posted on 2004-08-10
2
Medium Priority
?
702 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

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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 (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…
Loops Section Overview
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses

926 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