SEO

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

As DBADM or SYSADMIN, make sure that you have issued:
CONNECT TO <dbname>
REVOKE IMPLICIT_SCHEMA on DATABASE FROM PUBLIC
CREATE SCHEMA <schname> AUTHORISATION 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%" <huaxinzhang@gmail.com> a écrit dans le message de news:
1176661879.578861.286460@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
>

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 <schname> AUTHORISATION 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.

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%" <huaxinzhang@gmail.com> a écrit dans le message de news:
1176681185.799131.232930@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 <schname> AUTHORISATION 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.
>
>
>
>