By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,327 Members | 2,627 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,327 IT Pros & Developers. It's quick & easy.

restrict creating table in other existing schema

P: n/a
Suppose I am an admin of a database instance. Now
I need to specify that user A has the right to create tables
in his own schema, but not anywhere else.

In order to let user A create tables, i grant createtab to that user.
However, that would enable the user to create tables in other schemas
as well (except those sys schema).

Any way to restrict the users from creating objects in other schema
than their own?

thanks

Apr 15 '07 #1
Share this Question
Share on Google+
3 Replies


P: n/a
As DBADM or SYSADMIN, make sure that you have issued:
CONNECT TO <dbname>
REVOKE IMPLICIT_SCHEMA on DATABASE FROM PUBLIC
CREATE SCHEMA <schnameAUTHORISATION userid
GRANT CREATETAB on DATABASE to userid
CONNECT RESET
This will give userid the ability to createin,alterin,dropin but only in the
<schname>. It will "own" the schema.

HTH, Pierre.

--
Pierre Saint-Jacques
SES Consultants Inc.
514-737-4515
"%NAME%" <hu*********@gmail.coma écrit dans le message de news:
11**********************@n76g2000hsh.googlegroups. com...
Suppose I am an admin of a database instance. Now
I need to specify that user A has the right to create tables
in his own schema, but not anywhere else.

In order to let user A create tables, i grant createtab to that user.
However, that would enable the user to create tables in other schemas
as well (except those sys schema).

Any way to restrict the users from creating objects in other schema
than their own?

thanks
Apr 15 '07 #2

P: n/a
I tried this. Somehow, the user is still able to perform the
following:
>db2 -t
connect to db user userid;
(enter password for that user)
>set schema otherid;
create table test (t char(5));
insert into test values ('sdfs');
"list tables" under that user shows zero tables, but the user is able
to "select * from otherid.test". syscat.tables also shows there is
such table. The following is what I got from "get authorizations"

Administrative Authorizations for Current User

Direct SYSADM authority = NO
Direct SYSCTRL authority = NO
Direct SYSMAINT authority = NO
Direct DBADM authority = NO
Direct CREATETAB authority = YES
Direct BINDADD authority = NO
Direct CONNECT authority = YES
Direct CREATE_NOT_FENC authority = NO
Direct IMPLICIT_SCHEMA authority = NO
Direct LOAD authority = NO

Indirect SYSADM authority = NO
Indirect SYSCTRL authority = NO
Indirect SYSMAINT authority = NO
Indirect DBADM authority = NO
Indirect CREATETAB authority = NO
Indirect BINDADD authority = NO
Indirect CONNECT authority = NO
Indirect CREATE_NOT_FENC authority = NO
Indirect IMPLICIT_SCHEMA authority = NO
Indirect LOAD authority = NO

I am really puzzled why this userid is still able to create table in
other
schema.


On Apr 15, 3:58 pm, "Pierre Saint-Jacques" <sesc...@invalid.net>
wrote:
As DBADM or SYSADMIN, make sure that you have issued:
CONNECT TO <dbname>
REVOKE IMPLICIT_SCHEMA on DATABASE FROM PUBLIC
CREATE SCHEMA <schnameAUTHORISATION userid
GRANT CREATETAB on DATABASE to userid
CONNECT RESET
This will give userid the ability to createin,alterin,dropin but only in the
<schname>. It will "own" the schema.

Apr 15 '07 #3

P: n/a
List tables for a userid does a list of table that are "owned" by that id.
The id userid and the schema is otherid. The table name is otherid.test and
will not show in the list.
If you have done the revoke for implicit_schema, are you sure that userid is
not a member of a group that would have that privilege. Your list of
authorizations show if it was not given the explicit privilege nor did it
acquire it implicitly from being part of public (you revoked from public).
What this list will never show is if this userid acquires the
implicit_schema privilege from being a member of a group.

You can track every step of your small script by turning on the DB2AUDIT
facility and look at its output.
This will tell you step by step who did what when and to what and the reason
the command succeeded or failed.

Regards, Pierre.

--
Pierre Saint-Jacques
SES Consultants Inc.
514-737-4515
"%NAME%" <hu*********@gmail.coma écrit dans le message de news:
11**********************@w1g2000hsg.googlegroups.c om...
>I tried this. Somehow, the user is still able to perform the
following:
>>db2 -t
connect to db user userid;
(enter password for that user)
>>set schema otherid;
create table test (t char(5));
insert into test values ('sdfs');

"list tables" under that user shows zero tables, but the user is able
to "select * from otherid.test". syscat.tables also shows there is
such table. The following is what I got from "get authorizations"

Administrative Authorizations for Current User

Direct SYSADM authority = NO
Direct SYSCTRL authority = NO
Direct SYSMAINT authority = NO
Direct DBADM authority = NO
Direct CREATETAB authority = YES
Direct BINDADD authority = NO
Direct CONNECT authority = YES
Direct CREATE_NOT_FENC authority = NO
Direct IMPLICIT_SCHEMA authority = NO
Direct LOAD authority = NO

Indirect SYSADM authority = NO
Indirect SYSCTRL authority = NO
Indirect SYSMAINT authority = NO
Indirect DBADM authority = NO
Indirect CREATETAB authority = NO
Indirect BINDADD authority = NO
Indirect CONNECT authority = NO
Indirect CREATE_NOT_FENC authority = NO
Indirect IMPLICIT_SCHEMA authority = NO
Indirect LOAD authority = NO

I am really puzzled why this userid is still able to create table in
other
schema.


On Apr 15, 3:58 pm, "Pierre Saint-Jacques" <sesc...@invalid.net>
wrote:
>As DBADM or SYSADMIN, make sure that you have issued:
CONNECT TO <dbname>
REVOKE IMPLICIT_SCHEMA on DATABASE FROM PUBLIC
CREATE SCHEMA <schnameAUTHORISATION userid
GRANT CREATETAB on DATABASE to userid
CONNECT RESET
This will give userid the ability to createin,alterin,dropin but only in
the
<schname>. It will "own" the schema.

Apr 19 '07 #4

This discussion thread is closed

Replies have been disabled for this discussion.