Object access separation

Database
Enthusiast

Object access separation

I need to create a database that gives users the ability to create and then edit/sel on only objects that they created. Users would only have access to objects they created unless another user grants specific access to another user. Does anyone know how I'd go about this in terms of which access to grant? For tables, maybe something like CT to all but DT, sel, update, insert, etc. to creator only. Is that possible?
6 REPLIES
Senior Apprentice

Re: Object access separation

Of course,
you do it exactly as your proposed:
Only grant the CREATE, but not the DROP right.
The creator of an object automatically gets all access rights.

Dieter
Enthusiast

Re: Object access separation

Nice, so it's much easier that I thought. Thanks again Dieter.

Re: Object access separation

When users created new table, they automatically have access to it right.

 

But when we give SELECT access on the same database for other users; this means they can also access the same new table created by other user.

 

How can we separate the access that only user who created the table will have access to it? 

Senior Apprentice

Re: Object access separation

Only grant the CREATE right on database level, no other right.
The creator of an object automatically gets all access rights.

Re: Object access separation

Thanks Dieter!

 

Just to be clear if this will be my scenario,

 

I will create a new user with CREATE rights only.

 

He will have SELECT access to all other table while having DML (CREATE) access to objects user will create. 

 

Thanks again. 

Senior Apprentice

Re: Object access separation

A user with only the CREATE TABLE right will get all rights (DDL & DML & DCL) on the tables he creates, but is not able to access any other object in any way.