Solved

DB2 database access

Posted on 2004-08-10
2
690 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 50

Accepted Solution

by:
Lowfatspread earned 250 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

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 (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…
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…

628 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