Newbee - Schema concept question

[cmc]

I need some clarification to help me understand the DB2 strucure more.
The questions are about "implicit schema"

1. This is a very interest concpet that DB2 let every user to create new
schema (as this is part of the PUBLIC group privilege - if I am not wrong).
From a practical stand point, what is the application of such concept.

2. Suprisingly, if the schema is an implicitly created, everyone else can
create objects in it too. What is the practical use of this feature ? I
would tend to think it should be only used by the owner (also not sure why
implicit schema is owned by SYSIBM).

3. Can we set up some database parameter such that by default only the owner
have a FULL access to the schema ?

Any help is appreciated.
Tom

[Serge Rielau]

cmc wrote:

I need some clarification to help me understand the DB2 strucure more.
The questions are about "implicit schema"

1. This is a very interest concpet that DB2 let every user to create new
schema (as this is part of the PUBLIC group privilege - if I am not wrong).
From a practical stand point, what is the application of such concept. DB2 gives you essentially two options. One option is that you want to
control schemas tightly. In this case you REVOKE IMPLICIT_SCHEMA on the
database from PUBLIC.
If you don't want to control tighly, then leave it as is.
I woudl think that both options have value depending on the environment
(e.g. development vs production or the size of the shop).
Why the default is to grant IMPLICIT_SCHEMA to PUBLIC after database
creation I can only guess. Often such defaults are borne through legacy
(perhaps coming from SQL/DS or DB2 for zOS). Given that there is a
switch folks rarely bother about it.
2. Suprisingly, if the schema is an implicitly created, everyone else can
create objects in it too. What is the practical use of this feature ? I
would tend to think it should be only used by the owner (also not sure why
implicit schema is owned by SYSIBM). This is the continuation of the default in the first point. If schemata
can be created ad-hoc without much thinking, then why bother to suddenly
control the usage.
Essentially the default is that schemata are just public "directories".
It is the content that needs protecting.
Again, as soon as IMPLCITIC_SCHEMA (as teh master switch) is revoked
this behavior goes away and DB2 ends up with a strict policy of schema
usage.
3. Can we set up some database parameter such that by default only the owner
have a FULL access to the schema ?

REVOKE IMPLICIT_SCHEMA ON <DB> FROM PUBLIC :-)

As an aside: It is important to note that USER and SCHEMA are
orthogonal. E.g. the migration toolkit generates schemata such as ORA8
or SQL7 but there is normally is no ORA8 or SQL7 user on the system.

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Mark A]

"Serge Rielau" <sr*****@ca.ibm.com> wrote in message
news:3c*************@individual.net...

Why the default is to grant IMPLICIT_SCHEMA to PUBLIC after database
creation I can only guess. Often such defaults are borne through legacy
(perhaps coming from SQL/DS or DB2 for zOS). Given that there is a switch
folks rarely bother about it.

There was no "schema" in DB2 mainframe until well after DB2 for LUW had it,
and it was added later for compatibility. Anyone who has create table
authority can create a table with their auth id as the high level qualifier
(what is now called schema) on DB2 mainframe. So by granting create table to
a user, an implicit schema authority is created on DB2 mainframe.

[cmc]

Thanks for providing the useful informatin to help me understand the DB2
design.

More questions:
1. How do you grant/assign a schema to a group so that every user in that
group has access to the objects in that schema?
2. When a user, say X, do a schema switch by following (assume it has been
set up to allow access the schema TEST in question 1):
db2> set schema TEST
Does it mean user X is now has complete access to everything of the schema
just like the owner/creater ? Or, you still need to have individual grants
on each object ?

Thanks
Tom

"Serge Rielau" <sr*****@ca.ibm.com> wrote in message
news:3c*************@individual.net...

cmc wrote:

I need some clarification to help me understand the DB2 strucure more.
The questions are about "implicit schema"

1. This is a very interest concpet that DB2 let every user to create new schema (as this is part of the PUBLIC group privilege - if I am not wrong). From a practical stand point, what is the application of such concept.

DB2 gives you essentially two options. One option is that you want to
control schemas tightly. In this case you REVOKE IMPLICIT_SCHEMA on the
database from PUBLIC.
If you don't want to control tighly, then leave it as is.
I woudl think that both options have value depending on the environment
(e.g. development vs production or the size of the shop).
Why the default is to grant IMPLICIT_SCHEMA to PUBLIC after database
creation I can only guess. Often such defaults are borne through legacy
(perhaps coming from SQL/DS or DB2 for zOS). Given that there is a
switch folks rarely bother about it.

2. Suprisingly, if the schema is an implicitly created, everyone else can create objects in it too. What is the practical use of this feature ? I would tend to think it should be only used by the owner (also not sure why implicit schema is owned by SYSIBM).

This is the continuation of the default in the first point. If schemata
can be created ad-hoc without much thinking, then why bother to suddenly
control the usage.
Essentially the default is that schemata are just public "directories".
It is the content that needs protecting.
Again, as soon as IMPLCITIC_SCHEMA (as teh master switch) is revoked
this behavior goes away and DB2 ends up with a strict policy of schema
usage.

3. Can we set up some database parameter such that by default only the owner have a FULL access to the schema ?

REVOKE IMPLICIT_SCHEMA ON <DB> FROM PUBLIC :-)

As an aside: It is important to note that USER and SCHEMA are
orthogonal. E.g. the migration toolkit generates schemata such as ORA8
or SQL7 but there is normally is no ORA8 or SQL7 user on the system.

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Serge Rielau]

cmc wrote:

1. How do you grant/assign a schema to a group so that every user in that
group has access to the objects in that schema? There is no privilege that gives a user access to all objects in a
schema, existing and future. There is only ALTERIN, CREATEIN and DROPIN
on the schema level.
Having said that DB2 has started to introduce wildcards with the EXECUTE
privilege for routines. GRANTing EXECUTE on myschema.* TO <userorgroup>
will cover all existing routines in that schema.
Whiel it is reasonable to imagine that both the schema level privileges
as well as the wildcard support being expanded upon, today thsi is not
teh case.
2. When a user, say X, do a schema switch by following (assume it has been
set up to allow access the schema TEST in question 1):
db2> set schema TEST
Does it mean user X is now has complete access to everything of the schema
just like the owner/creater ? Or, you still need to have individual grants
on each object ?

That's what I refereed to as USER and SCHEMA being orthoginal.
In a Unix environment USER corresponds to "whoami", while CURRENT SCHEMA
corresonds to "pwd".
SET SCHEMA matched a cd to another directory.
SET SESSION AUTHID matches the "su" command. Naturally SET SESSION
AUTHID is heavily restricted.

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab