| Create table others schema [message #479645] |
Mon, 18 October 2010 10:18  |
preet_kumar
Messages: 204
Registered: March 2007
|
Senior Member |
|
|
how can i grant a user permission to create tables and indexes in other user schema.
Grant create table permits the user to create tables in his own schema which is part of Resource role.
Grant any table will permit him to create table in any schema including system which i don't want.
Any suggestion.
|
|
|
|
|
|
|
|
| Re: Create table others schema [message #479678 is a reply to message #479646] |
Mon, 18 October 2010 12:05  |
preet_kumar
Messages: 204
Registered: March 2007
|
Senior Member |
|
|
Thanks Michel..as you have mentioned that one shouldn't but when we have a setup where we need auditing of user actions then we need to create individual users to monitor their actions including create table.
There is a master schema in which we have all objects and 10 of our users need to create tables and indexes in this master schema which needs to be monitored.
|
|
|
|
| Re: Create table others schema [message #479679 is a reply to message #479678] |
Mon, 18 October 2010 12:10 |
cookiemonster
Messages: 13961
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Do you have any source control for your db objects? Or a dev server? Or a test server?
Cause if you have those this level of auditing really shouldn't be necessary.
Since nothing should go in main schema in prod without being tested and checked into source control.
|
|
|
|
| Re: Create table others schema [message #479689 is a reply to message #479678] |
Mon, 18 October 2010 12:51 |
 |
Michel Cadot
Messages: 68722
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
Quote:There is a master schema in which we have all objects and 10 of our users need to create tables and indexes in this master schema which needs to be monitored.
Bad design, no one should be able to create (or drop or alter) any object in the master schema.
Why your user should be able to create a table in master schema and not in his own?
Regards
Michel
[Updated on: Mon, 18 October 2010 12:52] Report message to a moderator |
|
|
|
| Re: Create table others schema [message #479714 is a reply to message #479645] |
Mon, 18 October 2010 16:45 |
preet_kumar
Messages: 204
Registered: March 2007
|
Senior Member |
|
|
Sorry Michel its really not a bad design but this is designed as per our requirement and this is not a payroll or billing system where a dedicated DBA runs all the command and start the nightly batch jobs.
We have 15 developers who work on the online application and they get input from the customers regularly which they need to keep updating and modifying for which they should have access to all DML and DDL on application master schema which consists of almost 600 tables.If the developers start creating tables in their own schema it does not make sense as the application uses tables from master schema and all developers need to have access to master schema.
If Oracle provide the feature of DML on other schema then it should also provide the feature of DDL on other schema which is more secure than providing the DDL on any schema ( Create table any) which risks the user creating tables in system schema.
|
|
|
|
|
|
| Re: Create table others schema [message #479731 is a reply to message #479714] |
Tue, 19 October 2010 01:11 |
 |
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
preet_kumar wrote on Mon, 18 October 2010 23:45
If Oracle provide the feature of DML on other schema then it should also provide the feature of DDL on other schema which is more secure than providing the DDL on any schema ( Create table any) which risks the user creating tables in system schema.
Not really.
There's the O7_DICTIONARY_ACCESSIBILITY parameter which is set to FALSE by default so CREATE ANY TABLE will make it possible to create tables in any schema except SYS. You probably don't want to set it to TRUE.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
