Creating objects in different schemas.. 1

[greaver]

Hi Guys,

I have the following situtation. I have 3 schemas - lets call them - schema1, schema2, and schema3. Each schema has its own set of objects, ie: schema1.table1, schema2.table2, etc...

My production DBA wants to use only one login to manage all of these schemas. I created an 'admin' user with "Create Any Table" priviledge. This allowed 'admin' to create each of the above schemas from within that login. However, when admin attempts to grant 'select/update/delete/insert' priviledges on these schemas, I get the "Insufficient Priviledges" error. Is it possible to give admin the priviledges to do these grants? Connecting as the schema1 / schema2 / schema3 user to execute a:

Grant Select on object to admin with grant option

is to be avoided, since in production it would necessitate the production DBA changing logins and defeats the purpose of even having an admin account.

I know there has to be a way to do this... otherwise the System account would be flooded with database objects from every application installed....

 

[Turkbear]

Hi,
Sorry - in order to grant anything on objects outside your schema, the schema owner must have granted you that right..
( This applies to DBAs just like any other user)

As a schema owner, I do not want anyone that I have not identified to have the ability to manipulate objects in my schema..Not even my DBA.