Thursday, October 26, 2006

Create objects in different schema

Suppose there is a need to create by many users objects (e.g. tables) in one, and only one specific schema. Let's call this schema OUTSCHEM. The simplest solution would be to grant 'create any table' privilege. However this is much more they need, as it allows them to create objects in any schema. Such a solution seems to be danger.
There is a very simple way to solve this. Under the OUTSCHEM schema we create procedure, in the simplest form it can look like below:

CREATE OR REPLACE procedure create_in_outchem (sql_to_run in varchar)
as
sql_stmt varchar(4000);
begin
sql_stmt := sql_to_run;
execute immediate (sql_stmt);
end create_table_in_arimr;

Then we grant execute of this procedure to users we want. As a result when in sql_to_run there will be create table statement, table will be created in OUTSCHEM instead of user.
Some remarks:
  1. The procedure above is just an example, in a form it is written it allows to run ANY sql statement as OUTSCHEM user. So if you want this procedure to be used only to create tables statements, so conditions need to be added
  2. Check carefully that OUTSCHEM user doesn't have 'create ANY ...' privilege. If so, there will be no difference between running this procedure and grant create any to users




No comments: