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 statementCREATE TABLE MYTAB ...
will create the tableDB2ADMIN.MYTAB
. If that same user issues the statementCREATE TABLE BOGUS.MYTAB ...
, the tableBOGUS.MYTAB
(and implicitly the schemaBOGUS
) will be created, although there may be no user namedbogus
.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 ofGRANT
statements that allow you to grant access to objects and data as necessary. You can read more about them in the documentation.