Home » RDBMS Server » Security » Create table others schema (Oracle 10g)
Create table others schema [message #479645] Mon, 18 October 2010 10:18 Go to next message
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 #479646 is a reply to message #479645] Mon, 18 October 2010 10:21 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You have the 2 existing privilege CREATE TABLE and CREATE ANY TABLE, nothing else.

Why do you need, you shouldn't?

Regards
Michel
Re: Create table others schema [message #479648 is a reply to message #479645] Mon, 18 October 2010 10:22 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>how can i grant a user permission to create tables and indexes in other user schema.
CREATE PROCEDURE (MAKE_NEW_TABLE) in other schema that does EXECUTE IMMEDIATE CREATE TABLE
GRANT EXECUTE ON MAKE_NEW_TABLE TO <first_schema>
Re: Create table others schema [message #479678 is a reply to message #479646] Mon, 18 October 2010 12:05 Go to previous messageGo to next message
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 Go to previous messageGo to next message
cookiemonster
Messages: 13917
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 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
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 Go to previous messageGo to next message
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 #479729 is a reply to message #479714] Tue, 19 October 2010 00:58 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If Oracle does not provide it it is because you should not need to have it.
Either you are a owner and can make DDL on your object.
Either you are a DBA and can make DDL on other schemas.
These are the 2 cases Oracle has considered.

How can your developer work if one makes DDL on and change the column of a table that another is currently developing a procedure taking its information from the modified table?

You have an organization problem not an Oracle one.

Regards
Michel
Re: Create table others schema [message #479731 is a reply to message #479714] Tue, 19 October 2010 01:11 Go to previous message
Littlefoot
Messages: 21806
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.
Previous Topic: PL SQL Compilation Session
Next Topic: Encrypt & Decrypt
Goto Forum:
  


Current Time: Fri Mar 29 06:52:08 CDT 2024