467,915 Members | 1,604 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 467,915 developers. It's quick & easy.

How to find out the owner of SQL objects?

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

Jan 22 '07 #1
  • viewed: 16659
Share:
7 Replies
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
Jan 22 '07 #2
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

Jan 22 '07 #3
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
Jan 22 '07 #4
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

Jan 26 '07 #5
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

Jan 26 '07 #6
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
Jan 26 '07 #7
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
Jan 26 '07 #8

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

5 posts views Thread by Brian | last post: by
6 posts views Thread by ritagoldman101 | last post: by
7 posts views Thread by Sharon | last post: by
1 post views Thread by Mike Mascari | last post: by
1 post views Thread by Teresa Masino | last post: by
2 posts views Thread by aj | last post: by
10 posts views Thread by SimeonD | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.