GRANT DDL in Oracle to specific user

Question

Hi,

How to grant DDL privileges in oracle ?

On database I've users SCHEMA_1, SCHEMA_2 and SCHEMA_3

and now i want to from schema_1 be able to do DDL only on SCHEMA_2

Is the grant is possible from SCHEMA_2 level or system only ?

Answer 1

Oracle doesn't work that way. You'd have to grant CREATE ANY [OBJECT_TYPE] to that user and have a system event trigger which restricts them from working in the schemas you don't want them to.

Warning: Undocumented / underdocumented features of DBMS_STANDARD are used.

CREATE OR REPLACE TRIGGER schema_1_on_schema_2
  before DDL on DATABASE
as
  has_dba_priv number;
  n            number;
  stmt         ora_name_list_t;
BEGIN
  -- exit if user is object owner
  if ora_dict_obj_owner = ora_login_user then
    return
  end if;

  -- exit if user has dba directly
  select count(*)
    into has_dba_priv
    from dba_role_privs 
   where granted_role = 'DBA'
     and grantee = ora_login_user;

  if has_dba_priv <> 0 then
    return;
  end if;

  -- exit if action is an automatic recompile
  stmt := null;
  n := ora_sql_txt(sql_text);
  FOR i IN 1..n LOOP
    stmt := stmt || sql_text(i);
  END LOOP;

  if stmt like 'ALTER % COMPILE REUSE SETTINGS%' then
    return;
  end if;

  -- you should probably organize this into a database table of permitted
  -- schema_x can affect schema_y, but this is a "basic" example
  if     (ora_dict_obj_owner = 'SCHEMA_2')
     and (ora_login_user = 'SCHEMA_1') then 
    null;
  else
    raise_application_error (-20000, 'User ' || ora_login_user || 
         ' is not permitted to execute DDL against ' || ora_dict_obj_owner);
  end if;
end;

Answer 2

A better way might be to embed the schema_2 DDL into procedures and grant execute on those procedures to schema_1. A fuller explanation of your requirements may lead to fuller / better answers.