IBM Data Studio can't browse data on SAMPLE (DB2 Express-C)

I can't browse data on SAMPLE DB.
Setup is as follows:
- Windows 7 64 bit PRO,  
- IBM DB2 Express-C, 10.5.500.107 (latest)
- IBM Data Studio Version 4.1.1 (Administrative installation, Installed using IBM Installation Manager 1.8.1)

Database credentials are: db2admin/db2admin
Windows administrator username is: Nenad (password protected)

With DB2 command line processor (Start -> IBM DB2 DB2COPY1 (Default) -> DB2 Command Line Processor) I can connect to SAMPLE database using:

db2 => connect to sample
Database Connection Information

 Database server        = DB2/NT64 10.5.5
 SQL authorization ID   = NENAD
 Local database alias   = SAMPLE

Open in new window


And query staff table with:
db2 => select * from staff

ID     NAME      DEPT   JOB   YEARS  SALARY    COMM
------ --------- ------ ----- ------ --------- ---------
    10 Sanders       20 Mgr        7  98357.50         -
    20 Pernal        20 Sales      8  78171.25    612.45
    30 Marenghi      38 Mgr        5  77506.75         -
    40 O'Brien       38 Sales      6  78006.00    846.55
    50 Hanes         15 Mgr       10  80659.80         -
    60 Quigley       38 Sales      -  66808.30    650.25
    70 Rothman       15 Sales      7  76502.83   1152.00
    80 James         20 Clerk      -  43504.60    128.20
    90 Koonitz       42 Sales      6  38001.75   1386.70
   100 Plotz         42 Mgr        7  78352.80         -
   110 Ngan          15 Clerk      5  42508.20    206.60
   120 Naughton      38 Clerk      -  42954.75    180.00
   130 Yamaguchi     42 Clerk      6  40505.90     75.60
   140 Fraye         51 Mgr        6  91150.00         -
   150 Williams      51 Sales      6  79456.50    637.65
   160 Molinare      10 Mgr        7  82959.20         -
   170 Kermisch      15 Clerk      4  42258.50    110.10
   180 Abrahams      38 Clerk      3  37009.75    236.50
   190 Sneider       20 Clerk      8  34252.75    126.50
   200 Scoutten      42 Clerk      -  41508.60     84.20
   210 Lu            10 Mgr       10  90010.00         -
   220 Smith         51 Sales      7  87654.50    992.80
   230 Lundquist     51 Clerk      3  83369.80    189.65
   240 Daniels       10 Mgr        5  79260.25         -
   250 Wheeler       51 Clerk      6  74460.00    513.30
   260 Jones         10 Mgr       12  81234.00         -
   270 Lea           66 Mgr        9  88555.50         -
   280 Wilson        66 Sales      9  78674.50    811.50
   290 Quill         84 Mgr       10  89818.00         -
   300 Davis         84 Sales      5  65454.50    806.10
   310 Graham        66 Sales     13  71000.00    200.30
   320 Gonzales      66 Sales      4  76858.20    844.00
   330 Burke         66 Clerk      1  49988.00     55.50
   340 Edwards       84 Sales      7  67844.00   1285.00
   350 Gafney        84 Clerk      5  43030.50    188.00

  35 record(s) selected.

Open in new window


So i guess DB/DBMS works fine.

Then I create connection in Data Studio using settings:

Database: SAMPLE
Host: localhost
Port number: 50000
Username: db2admin
Password: db2admin

And Test connection: with Ping succeeded! result.

When i click on Tables I can see all of them.
But when I right-click on (Schema: NENAD) (Table Name: STAFF) Data -> Browse Data I got this error:

error
com.ibm.db2.jcc.am.SqlSyntaxErrorException: The statement failed because the authorization ID does not have the required authorization or privilege to perform the operation.  Authorization ID: "DB2ADMIN".  Operation: "SELECT". Object: "NENAD.STAFF".. SQLCODE=-551, SQLSTATE=42501, DRIVER=4.18.60
The statement failed because the authorization ID does not have the required authorization or privilege to perform the operation.  Authorization ID: "DB2ADMIN".  Operation: "SELECT". Object: "NENAD.STAFF".. SQLCODE=-551, SQLSTATE=42501, DRIVER=4.18.60

I tried several reinstallations of both DB2 and Data Studio with various settings and usernames, I tried to right click on table then manage privileges (giving all privileges to both DB2ADMIN and NENAD) but in vain.

I also can't create new database, I got this error:
CREATE DATABASE FTN AUTOMATIC STORAGE YES ON 'C:\DB2\NODE0000' DBPATH ON 'C:\DB2\NODE0000' ALIAS FTN  WITH 'FTN database'

Failed to execute command.  DAS returned the following error: sqlcode=-22201 output=null

I have zero experience with both DB2 Express-C and IBM Data Studio. I have been using Oracle DB/DB Express with SQL Developer until now.

My questions are:
1. How should I resolve this error?
2. Is there a simple way (just like in SQL developer) to just create user and then click on Grant All Privileges and that's it?


adding user in SQL developergranting privileges to user in SQL developer
KPaxAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

waynezhuCommented:
The error stated clear that DB2ADMIN does not have access to  NENAD schema objects.
You can either grant access or simply connect as  NENAD user.
KPaxAuthor Commented:
I found answer on another place:

Your issue is that you are logging in to the command line as Nenad (you can tell that by the default schema), and that you are logging into the DB through DataStudio as db2admin. You either need to log in to Data Studio as Nenad. Or as Nenad you need to grant the appropriate permissions to user ID db2admin.

EDIT: To add to better explain things as well as your question in the comments below.... DB2 doesn't use created user ids like other databases do. It looks to the underlying operating system or LDAP for authentication. In the Unix/Linux environment, the instance Id that is the highest ID administratively is what ID the instance is set up to run as. Your db2admin account is this account. That being said....Windows introduces a different security scheme. Even though the instance ID exists, whoever installed the product (their user ID) tends to be granted high administrative powers in DB2. And when you issue the CREATE DATABASE statement, you are doing it with your Windows user ID. Therefore, it becomes the owner of the database and has DBADM, SECADM, DATAACCESS, and ACCESSCTRL granted to it. db2admin will generally have DBADM WITHOUT DATAACCESS WITHOUT
  ACCESSCTRL. So you would need to log in as Nenad (the ID with  SECADM) and grant (to be safe) DBADM, SECADM, DATAACCESS,  ACCESSCTRL to user db2admin. Now that ID should have what you are looking for.

I don't recommend those privileges for just any ID. If all you need is access to read/write to tables then DATAACCESS alone should do it.

Thank you VERY MUCH! I logged in Data Studio with Windows credentials (something I not possible in Oracle SQL Developer, therefore my confusion) and - viola - I was able to both query SAMPLE and browse data, as well as create new DB. Please would you explain to me - expand your answer, how should I grant privileges (from either command prompt or Data Studio) to db2admin to enable that user to both create new DB and query/browse existing ones.
               
Thank you VERY much for clearing up this for me "...DB2 doesn't use created user ids like other databases do...." - that twas primary cause of my confusion, as am I novice to IBM DB2.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Tomas Helgi JohannssonCommented:
Hi!

To give a user access to some resource you execute GRANT (REVOKE to take away from user ) commands.
You will need to read and understand the DB2 Security to be able to grant enough privileges to user  as you maybe don't want to grant to that user more privileges than he/she needs.

More info here
http://www-01.ibm.com/support/knowledgecenter/SSEPGG_10.5.0/com.ibm.db2.luw.admin.sec.doc/doc/c0005478.html

Regards,
    Tomas Helgi
KPaxAuthor Commented:
This is most comprehensive answer  I found on this topic, and it helped me to immediately understand and solve the problem.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
DB2

From novice to tech pro — start learning today.