I have a situation in which i have a db which is common for all developers. They can create tables from other prod tables in this common DB. These prod tables may contain some npi data and they are governed by appropiate roles which a dvelooper gets access to according to process. When the developer(call it 1st) copies this data to common database(say db1), by creating a table, he is the owner of that table and he has implicit rights (Select and others..) with grant. 1st developer gives access select access to another developer(call it 2nd), who now can see this data. The same 2nd developer didn't have access to the prod table from where this table ( in db1) was created. But, now he can access this data. So, this is a breach to the security.
I did one small test:
i created a table in db1.mytab with userid - samir.singh. i created another user-test.samir.singh and didn't give any access on db db1 to test.samir.singh. Then from user-samir.singh, i ran grant select on db1.mytab to test.samir.singh. Now, the user test.samir.singh has access to db1.mytab. but, ideally i dont want this, but since samir.singh has rights on db1.mytab, it an transferred to test.samir.singh (and this is well understood)
So, my question is :
1. What is the appropiate design for such scenarios.
2. Is there a way we can restrict the 1st developer to give the implicit access on his created tables to other developers ?