DB2 users/ beginer questions

db2

New to this.

Looking at db2 schemas, which should be equal to OS system users, right ?
Doh apparently not. Except for PC,DB2ADMIN beeing on my OS system

How is it, that this

select * from sysibm.systabauth
where grantee = 'DB2ADMIN'

produsces something and 'DB2ADMIN' is not on list of schemas ?

'DB2ADMIN' was created at db setup, so I assume it should have all privileges, but no, if I'm at 'PC' schemata 'DB2ADMIN' can't even execute simple select statement.

How to grant all privileges to 'DB2ADMIN' on 'PC schemata ?'

if this is my connection url:
jdbc:db2://127.0.0.1:50000/SAMPLE
What is SAMPLE, should be db, a little confuzig coz looking at db tree structure at sqirell client there is DB2 & schematas listed under it, and nowhere 'SAMPLE' ?

Best Answer

Unlike some other databases, in DB2 schema name does not necessarily match any user name. Users are created and authenticated outside the database, in the operating system. After a user successfully connects to the database, the schema name defaults to its login ID. For example, connecting as DB2ADMIN and issuing the statement CREATE TABLE MYTAB ... will create the table DB2ADMIN.MYTAB. If that same user issues the statement CREATE TABLE BOGUS.MYTAB ..., the table BOGUS.MYTAB (and implicitly the schema BOGUS) will be created, although there may be no user named bogus.

DB2 takes the concept of separation of duties quite seriously. For example, the database instance administrator, which presumably DB2ADMIN is, does not by default have access to data, except the tables created by that same user. There are a variety of GRANT statements that allow you to grant access to objects and data as necessary. You can read more about them in the documentation.

Related Question