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

From: Andy Klock <andy_at_oracledepot.com>
Date: Wed, 8 Feb 2012 17:25:12 -0500
Message-ID: <CADo_RaOK-Q_LexJ5saF3qt6fM4ERA_JOkhMYeNEHxZYTBAFw3w_at_mail.gmail.com>

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_at_innout.com> 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

Received on Wed Feb 08 2012 - 16:25:12 CST