问Oracle如何授予创建任何目录的权限，限制所有目录都必须在给定目录内创建？

EN

alter system set utl_file_dir = '/foo/bar' scope=spfile;
shutdown immediate;
startup open;

sqlplus kjohnston

create or replace procedure mydircreate (p_dir varchar2)
as
  ex_custom EXCEPTION;
  PRAGMA EXCEPTION_INIT( ex_custom, -20001 );
begin
  if lower(p_dir) not like '/foo/bar/%' then
    raise_application_error( -20001, 'Not authorized' );
  end if;

  execute immediate 'create or replace directory mydir as ''' || p_dir || '''';
end mydircreate;

create user testuser identified by <password>;
grant create session to testuser;
grant execute on kjohnston.mydircreate to testuser;

exit;

sqlplus testuser

SQL> exec kjohnston.mydircreate('mydir', '/randomdir');
ORA-20001: Not authorized

SQL> exec kjohnston.mydircreate('mydir', '/foo/bar/baz');
PL/SQL procedure successfully completed.

CREATE OR REPLACE TRIGGER trg_before_ddl 
BEFORE DDL ON DATABASE
declare 
    v_sql ORA_NAME_LIST_T;
   v_ddl varchar2(4000);
   v_cnt BINARY_INTEGER;
   is_valid number;
begin
   if  ora_sysevent in ('CREATE') and ora_dict_obj_type = 'DIRECTORY' then 
       v_cnt := ora_sql_txt (v_sql);
       FOR i IN 1..v_cnt LOOP     
          v_ddl := v_ddl || RTRIM (v_sql (i), CHR (0));
       END LOOP;
       v_ddl :=  regexp_substr(v_ddl,'AS ''(.*)''', 1, 1, 'i', 1 ); -- get path from ddl_statement             
       -- check valid directory here, path is in v_ddl ;
       is_valid := REGEXP_instr(v_ddl,'^/valid_dir/.*$');
       if (is_valid = 0) then 
         raise_application_error(-20000,'Directory is not valid' || v_ddl);
       end if; 
   end if;   
END;
/

CREATE DIRECTORY valid_dir AS '/valid_dir/xyz';

CREATE DIRECTORY invalid_dir AS '/invalid_dir/xyz';