DB2 Permissions - createtab in schema 
Author Message
 DB2 Permissions - createtab in schema
Hi Freaks,

I need a litty help.
Maybe somebody had the same problem too

I have two schema in my database A und B.
I want that user X has select permissions on some tables in schema A,
but cannot create any table in schema A. This seems to be no problem.
(grant select on table .... to user X; revoke createtab on database
from user X)

but also user X should to be able to create tables in schema B an
write into this tables.
How to do this ???
Is there anybody with a good idea for me ???
In advance thank you.

Gerold



Sat, 30 Jul 2005 21:52:03 GMT
 DB2 Permissions - createtab in schema


Quote:
> Hi Freaks,

> I need a litty help.
> Maybe somebody had the same problem too

> I have two schema in my database A und B.
> I want that user X has select permissions on some tables in schema A,
> but cannot create any table in schema A. This seems to be no problem.
> (grant select on table .... to user X; revoke createtab on database
> from user X)

> but also user X should to be able to create tables in schema B an
> write into this tables.
> How to do this ???

Just revoke the CREATIN privilege from user X on schema A and grant it on
schema B:

REVOKE CREATEIN ON SCHEMA A FROM USER X;
GRANT CREATEIN ON SCHEMA B TO USER X;

Of course, you should not revoke the CREATETAB privilege on the database level
then.

--
Knut Stolze
Information Integration
IBM Germany / University of Jena



Sat, 30 Jul 2005 22:40:49 GMT
 DB2 Permissions - createtab in schema

REVOKE CREATEIN ON SCHEMA B FROM X

GRANT CREATEIN ON SCHEMA A TO X


Quote:
> Hi Freaks,

> I need a litty help.
> Maybe somebody had the same problem too .

> I have two schema in my database A und B.
> I want that user X has select permissions on some tables in schema A,
> but cannot create any table in schema A. This seems to be no problem.
> (grant select on table .... to user X; revoke createtab on database
> from user X)

> but also user X should to be able to create tables in schema B an
> write into this tables.
> How to do this ???
> Is there anybody with a good idea for me ???
> In advance thank you.

> Gerold



Sat, 30 Jul 2005 22:50:38 GMT
 DB2 Permissions - createtab in schema

Quote:


>>Hi Freaks,

>>I need a litty help.
>>Maybe somebody had the same problem too

>>I have two schema in my database A und B.
>>I want that user X has select permissions on some tables in schema A,
>>but cannot create any table in schema A. This seems to be no problem.
>>(grant select on table .... to user X; revoke createtab on database
>>from user X)

>>but also user X should to be able to create tables in schema B an
>>write into this tables.
>>How to do this ???

> Just revoke the CREATIN privilege from user X on schema A and grant it on
> schema B:

> REVOKE CREATEIN ON SCHEMA A FROM USER X;
> GRANT CREATEIN ON SCHEMA B TO USER X;

> Of course, you should not revoke the CREATETAB privilege on the database level
> then.

And you should revoke the implicit_schema authority on the database.
Otherwise users can create tables in any schema that doesn't already
exist.

-----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
-----==  Over 80,000 Newsgroups - 16 Different Servers! =-----



Sun, 31 Jul 2005 14:16:30 GMT
 DB2 Permissions - createtab in schema
Thank a lot for your help. Your solution works !!

first I revoke "createin" from public,
than I grant "createin" to the users who want to create a table
in a spezial schema.

regards

Gerold



Sun, 31 Jul 2005 14:39:12 GMT
 
 [ 5 post ] 

 Relevant Pages 

1. DB2 Newbie needs help with DB2 5.2 ODBC: can browse schema, but get no data

2. indirect CREATETAB authority

3. Implementation details of schema permissions?

4. NEWBIE: granting permission to entire schema (Oracle8)

5. Linked Servers to DB2 with special schema

6. DB2 to Oracle Schema

7. DB2 extract schema script

8. DB2 390 (7x) - Star Schema Join

9. DB2/AS400 ADO and Default Schema

10. DB2 UDB v.7x - XML Schema Support

11. Schema in DB2

12. db2 7.1/win2k - SET SCHEMA failure with JDBC


 
Powered by phpBB® Forum Software