Users & Schemas

Posted on 2003-02-24
Medium Priority
Last Modified: 2013-11-19
1) What is difference between users and schemas in DB2.

2) In DB2 instance:
a) Under user created database, I have under schemas:
DB2ADMIN, NULLID, SYSCAT, SYSFUN, SYSIBM, SYSSTAT. Under users & group objects, we have DB Users containing DB2ADMIN & USERID and DB Groups containing PUBLIC.

b) Under DWCTRLDB, I have under schemas:
FLG, IWH, NULLID, SYSCAT, SYSFUN, SYSIBM, SYSSTAT. Under users & group objects, we have DB Users containing DB2ADMIN & PUBLISH and DB Groups containing PUBLIC.

4) In DB2CTLSV instance:
a) Under SATCTLDB, I have under schemas:
users & group objects, we have DB Users containing DB2ADMIN and DB Groups containing PUBLIC.

5) What each of these schemas, db users and db groups signify and where can I find password to connect to them from remote client.

6) For all schemas, authorization is SYSIBM except SATADMIN which has DB2ADMIN as authorization. What do these 2 authorizations signify.

7) Schemas are for authentication to DB. There are no authorizations for schemas but they exist for other database objetcs, am I right?

8) DB Users means what. How is it different from schemas. I can see a set of 7 authorizarion/privileges for DB Users. There is a schema ITSO for user database DB User db2admin. What is this schema under an user.

9) Same is the case with User groups. We see all this in change user or change group window. Are there no roles in DB2 like oracle. Are DB Users & Groups like roles.

10) How do we create a group.
Question by:k_murli_krishna
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

Accepted Solution

mglxxx earned 400 total points
ID: 8023999
In DB2, schemata are a means of grouping database
objects together. They do not have any relation to
database users. If a user has been granted the
implicit_schema privilege, a schema with the same name
as the user's authorization id is automatically created,
when the user creates the first object.

When a database is created, DB2 will create a number of
predefined schemata:
SYSIBM: this is for catalog tables
SYSCAT: this is for views on the catalog tables. Objects
        under SYSIBM are for internal use by DB2 only,
        whereas objects in SYSCAT are meant to be queried
        by users.
SYSSTAT: Contains updatable views on objects in SYSIBM.
         These can be used to 'fake' a large database
         in a small one by updating statistical information.
SYSFUN: predefined UDFs are put under this schema.
NULLID: this is used for predefined packages.

I haven't used the satellite and dwh stuff which comes
with DB2, so I don't know what these other schemas are
use for. Presumably they contain metadata relevant for
the product, i.e. satellite db control and DWH control.

DB2 users are authorization ids known to the operating
system which have been granted privileges on the
database. DB2 user groups are user groups known to
the operating system, which have been granted privileges
on the database. The user group 'PUBLC' is a special
internal user group meaning 'everybody'.

5) Since schemas are only a means to group database
objects, there is no password associated with a schema.

6) The owner 'SYSIBM' is a special internal user. When
the database is created, everything is owned by SYSIBM.
Satellite control is something which isn't created by
DB2 itself but the product which uses that schema. During
creation of the objects for that schema, the product
setup program connects as instance owner an creates
the schema 'SATADMIN', that is, the instance owner is
also owner of that schema.

7) NO! Schemas are for grouping database objects.
There are privileges which can be granted on schemas.
These are: alterin, creatin, dropin

8) I think I answered that question already: a user is
an authorization id known to the OS who has been granted
privileges on the database.

9) There are no roles in DB2. You can use user groups
(which need to be defined in the OS first) for getting
something similar to Oracle's roles. The main difference
to oracle is that you cannot create a hierarchy of
roles (i.e. grant a role to another role).

10) A user group in DB2 is a user group in the
operating system.
You create a user group using the admin tools of
your os (e.g. mkgroup in AIX) and to assign users to
that group. Then you can use DB2 to grant privileges
to that group.
LVL 17

Author Comment

ID: 8026354
mglxx, thank you & i am giving it to you. For any further doubts I will post a fresh question.

Featured Post

Get real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Preface In the first article: A Better Website Login System (http://www.experts-exchange.com/A_2902.html) I introduced the EE Collaborative Login System and its intended purpose. In this article I will discuss some of the design consideratio…
Preface This is the third article about the EE Collaborative Login Project. A Better Website Login System (http://www.experts-exchange.com/A_2902.html) introduces the Login System and shows how to implement a login page. The EE Collaborative Logi…
Viewers will learn about arithmetic and Boolean expressions in Java and the logical operators used to create Boolean expressions. We will cover the symbols used for arithmetic expressions and define each logical operator and how to use them in Boole…
The viewer will learn the basics of jQuery, including how to invoke it on a web page. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery.: (CODE)
Suggested Courses

770 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