Re: [QUARANTINE] Re: developer access to alter procedure

Here is a test case I had regarding doing something similar to this.  After
just reviewing it, it looks like you can have duplicates in the "object
table" depending on case.  I must have thought about that when I wrote it
because I coded a l_exists > 0 :)
User beware.

create table compile_objects (object_owner varchar2(30), object_name
varchar2(30), object_type varchar2(30),
CONSTRAINT compile_objects PRIMARY KEY (object_owner, object_name,
object_type)
);


create or replace procedure compile_object(p_owner in varchar2, p_name in
varchar2, p_type in varchar2)
is
 l_exists number;
begin
 select count(*) into l_exists
 from compile_objects where
  upper(object_owner)= upper(p_owner) and
  upper(object_name) = upper(p_name) and
  upper(object_type) = upper(p_type);

 if l_exists > 0 then
   execute immediate 'alter '||p_type||' '||p_owner||'.'||p_name||'
compile';
 else
   RAISE_APPLICATION_ERROR(-20000, 'No permissions to compile '||p_type||'
'||p_owner||'.'||p_name);
 end if;
end;
/


SQL> exec compile_object('ANDY','C_TEST','PROCEDURE');
BEGIN compile_object('ANDY','C_TEST','PROCEDURE'); END;

*
ERROR at line 1:
ORA-20000: No permissions to compile PROCEDURE ANDY.C_TEST
ORA-06512: at "ANDY.COMPILE_OBJECT", line 14
ORA-06512: at line 1


SQL> insert into compile_objects values ('ANDY','C_TEST','PROCEDURE');

1 row created.

SQL> commit;

Commit complete.

SQL> exec compile_object('ANDY','C_TEST','PROCEDURE');

PL/SQL procedure successfully completed.

SQL> create user test identified by test;

User created.

SQL> grant create session to test;

Grant succeeded.

SQL> grant execute on andy.compile_object to test;

Grant succeeded.

SQL> conn test/test
Connected.

SQL> exec andy.compile_object('ANDY','C_TEST','PROCEDURE');

PL/SQL procedure successfully completed.


Andy

On Wed, Feb 8, 2012 at 4:49 PM, Jeff Chirco <JChirco@xxxxxxxxxx> wrote:
> I am not sure if I understand what you mean.  Can you give me more detail.
> Thanks.
>


--
http://www.freelists.org/webpage/oracle-l

Other related posts: