restrict creating table in other existing schema

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

%NAME% - 15 Apr 2007 19:31 GMT

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

Reply to this Message

Pierre Saint-Jacques - 15 Apr 2007 20:58 GMT

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.

Signature

Pierre Saint-Jacques
SES Consultants Inc.
514-737-4515

> Suppose I am an admin of a database instance. Now
> I need to specify that user A has the right to create tables
[quoted text clipped - 8 lines]
>
> thanks

Reply to this Message

%NAME% - 16 Apr 2007 00:53 GMT

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>
[quoted text clipped - 4 lines]
> This will give userid the ability to createin,alterin,dropin but only in the
> <schname>. It will "own" the schema.

Reply to this Message

Pierre Saint-Jacques - 19 Apr 2007 04:04 GMT

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.

Signature

Pierre Saint-Jacques
SES Consultants Inc.
514-737-4515

>I tried this. Somehow, the user is still able to perform the
> following:
[quoted text clipped - 49 lines]
>> the
>> <schname>. It will "own" the schema.

Reply to this Message