Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

DB2 database access

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

Tutorials alone can't teach real engineering

So we built better training tools.

-Hands-on Labs
-Instructor Mentoring
-Scenario-Based Tests
-Dedicated Cloud Servers

All at your fingertips. What are you waiting for?

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…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

719 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