How to find out the owner of SQL objects?

[Laurence]

Hi folks,

Who knows how to retrieve the owner of SQL objects, such as SCHEMA,
TABLE etc.? Is GRANTOR within catalog view owner?
Thanks,

Laurence

[Knut Stolze]

Laurence wrote:

Hi folks,

Who knows how to retrieve the owner of SQL objects, such as SCHEMA,
TABLE etc.? Is GRANTOR within catalog view owner?

Technically, there is no such thing as an "owner". You have users that have
CONTROL privilege on objects and, thus, can modify the object, for example
drop it.

Additionally, you could have a look at the DEFINER column in the respective
catalog view. But being a definer does not imply to be the owner. It does
not even imply that the definer has any privileges to the object in
question.

--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany

[Laurence]

Thank you for the reply.

On page 227 in the book "Administration Guide: Implementation" stated
-"When a new database is created, PUBLIC is given IMPLICIT_SCHEMA
database authority. With this authority, any user can create a schema
by creating an object and specifying a schema name that does not
already exist. SYSIBM becomes the owner of the implicitly created
schema and PUBLIC is given the privilege to create objects in this
schema."

And in the middle of that page stated -"Any user with DBADM authority
can explicitly create any schema which does not already exist, and can
optionally specify another user as the owner of the schema."

In addition, I did the following tests:

1. Log-in as db2user1, and create table t1 (col1 int, col2 char(8)).
DB2 will create the schema named DB2USR1 implicitly. I cannot drop the
schema DB2USER1 after drop table t1, even though I granted the
CREATEIN/ALTERIN/DROPIN privileges. (SYSIBM should be an owner of
schema DB2USER1)

2. Log-in as db2user1, and create schema DB2USER1 explicitly. This
time, I can drop schema DB2USER1. (DB2USER1 should be an owner of
schema DB2USER1)

Is it because of "owner", so test 1 cannot drop schema DB2USER1? If it
is true, how can I find the right owner of schema? Because I cannot
find the owner-like column in the catalog views.
Laurence

[Knut Stolze]

Laurence wrote:

Thank you for the reply.

On page 227 in the book "Administration Guide: Implementation" stated
-"When a new database is created, PUBLIC is given IMPLICIT_SCHEMA
database authority. With this authority, any user can create a schema
by creating an object and specifying a schema name that does not
already exist. SYSIBM becomes the owner of the implicitly created
schema and PUBLIC is given the privilege to create objects in this
schema."

And in the middle of that page stated -"Any user with DBADM authority
can explicitly create any schema which does not already exist, and can
optionally specify another user as the owner of the schema."

Ok, schemas have owners. They are the exception.

In addition, I did the following tests:

1. Log-in as db2user1, and create table t1 (col1 int, col2 char(8)).
DB2 will create the schema named DB2USR1 implicitly. I cannot drop the
schema DB2USER1 after drop table t1, even though I granted the
CREATEIN/ALTERIN/DROPIN privileges. (SYSIBM should be an owner of
schema DB2USER1)

SYSIBM _is_ the owner of schema DB2USER1. So only SYSIBM (or a DBADM) can
drop this schema.
http://publib.boulder.ibm.com/infoce...n/r0000945.htm

2. Log-in as db2user1, and create schema DB2USER1 explicitly. This
time, I can drop schema DB2USER1. (DB2USER1 should be an owner of
schema DB2USER1)

Is it because of "owner", so test 1 cannot drop schema DB2USER1? If it
is true, how can I find the right owner of schema? Because I cannot
find the owner-like column in the catalog views.

As usual, all such things are listed in the DB2 catalog views. So you
simply query the view:

SELECT owner
FROM syscat.schemata
WHERE schemaname = ...

--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany

[Laurence]

Thanks for the reply.

By the way, users/groups/PUBLIC need have CREATEIN privilege on the
specific schema so that user can create SQL objects (like table) within
that specific schema. BUT, why user can drop SQL objects within the
specific schema when DROPIN privilege doesn't grant to
users/groups/PUBLIC for that specific schema?
Laurence

[Laurence]

Thanks for the reply.

By the way, users/groups/PUBLIC need have CREATEIN privilege on the
specific schema so that user can create table within that specific
schema. BUT, why user can drop table within the specific schema when
DROPIN privilege doesn't grant to users/groups/PUBLIC for that specific
schema?

Is it because of the creator automatically have the CONTROL privilege
on that table? If it's true, when/what will need to check DROPIN
privilege for schema?
Laurence

[Serge Rielau]

Laurence wrote:

Thanks for the reply.

By the way, users/groups/PUBLIC need have CREATEIN privilege on the
specific schema so that user can create SQL objects (like table) within
that specific schema. BUT, why user can drop SQL objects within the
specific schema when DROPIN privilege doesn't grant to
users/groups/PUBLIC for that specific schema?

A user can have the required pivilege on the specific object without
having the privilege for all objects in the schema.
Since on CREATE the specific object doesn't yet exist the schema level
privilege is all there is.
So the design makes sense. (at least to me :-)

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[Pierre Saint-Jacques]

If you have CREATEIN on the schema and CREATETAB on the db, you can create
an object in that schema. You are now the owner and have control of the
object which means you can drop it without having the DROPIN prvilege.
DROPIN would apply to objects that your AUTHID does not CONTROL.
HTH, Pierre.

--
Pierre Saint-Jacques
SES Consultants Inc.
514-737-4515
"Laurence" <wo**********@gmail.coma écrit dans le message de news:
11*********************@a75g2000cwd.googlegroups.c om...

Thanks for the reply.

By the way, users/groups/PUBLIC need have CREATEIN privilege on the
specific schema so that user can create SQL objects (like table) within
that specific schema. BUT, why user can drop SQL objects within the
specific schema when DROPIN privilege doesn't grant to
users/groups/PUBLIC for that specific schema?
Laurence