We can use a stored procedure for this. There is no granular privilege for "alter any procedure in schema_x", but we can 'invent' one.
We do need to be careful when inventing it as we will have to use dynamic sql and will have to accept inputs...
We'll use a small table to store who is allowed to compile whose procedures...
ops$tkyte%ORA11GR1> create user util identified by util quota unlimited on users;
User created.
ops$tkyte%ORA11GR1> grant create session, create procedure, create table, alter any procedure to util;
Grant succeeded.
ops$tkyte%ORA11GR1> grant select on dba_objects to util;
Grant succeeded.
ops$tkyte%ORA11GR1>
ops$tkyte%ORA11GR1> connect util/util;
Connected.
util%ORA11GR1>
util%ORA11GR1> create table user_mapping
2 ( from_schema varchar2(30),
3 to_schema varchar2(30),
4 primary key(from_schema,to_schema)
5 ) organization index
6 /
Table created.
util%ORA11GR1>
util%ORA11GR1>
util%ORA11GR1> create or replace procedure alter_procedure
2 ( p_schema in varchar2,
3 p_name in varchar2,
4 p_debug in boolean default FALSE,
5 p_comp_params in varchar2 default NULL,
6 p_reuse_settings in boolean default FALSE
7 )
8 as
9 l_n number;
10 l_sql long;
11 begin
12
13 -- check to see if we were 'granted' access to this schema...
14 begin
15 select 1 into l_n
16 from user_mapping
17 where from_schema = sys_context('userenv','session_user')
18 and to_schema = p_schema;
19 exception
20 when no_data_found
21 then raise_application_error
22 ( -20001, '"' || sys_context('userenv','session_user') ||
23 '" does not have access to the schema "' || p_schema || '"' );
24 end;
25
26 -- validate the schema and procedure name
27 begin
28 select 1 into l_n
29 from dba_objects
30 where owner = p_schema
31 and object_name = p_name
32 and object_type = 'PROCEDURE';
33 exception
34 when no_data_found
35 then raise_application_error
36 ( -20002, 'Cannot find "' || p_schema ||
37 '"."' || p_name || '"' );
38 end;
39
40 l_sql := 'alter procedure "' || p_schema || '"."' || p_name || '" compile ';
41 if (p_debug)
42 then
43 l_sql := l_sql || ' debug ';
44 end if;
45
46 -- this part is RISKY. compiler arguments are free form:
47 -- name = value
48 -- you might not include this bit, or you might include
49 -- validation code. Currently, with the alter procedure
50 -- command as of 11.1.0.7 - this is 'safe', the command
51 -- cannot be SQL injected using this (modified to do
52 -- something it was not intended to do)
53 if (p_comp_params is not null)
54 then
55 l_sql := l_sql || p_comp_params;
56 end if;
57
58 if (p_reuse_settings)
59 then
60 l_sql := l_sql || ' resuse settings ';
61 end if;
62 execute immediate l_sql;
63 end;
64 /
Procedure created.
util%ORA11GR1> create or replace procedure add_mapping( p_from_schema in varchar2, p_to_schema in varchar2 )
2 as
3 begin
4 insert into user_mapping(from_schema,to_schema) values ( p_from_schema, p_to_schema );
5 end;
6 /
Procedure created.
util%ORA11GR1>
util%ORA11GR1> grant execute on add_mapping to ops$tkyte;
Grant succeeded.
util%ORA11GR1> grant execute on alter_procedure to scott;
Grant succeeded.
util%ORA11GR1>
util%ORA11GR1> connect /
Connected.
ops$tkyte%ORA11GR1> alter user util account lock;
User altered.
ops$tkyte%ORA11GR1>
ops$tkyte%ORA11GR1> connect scott/tiger
Connected.
scott%ORA11GR1> exec util.alter_procedure( 'OPS$TKYTE', 'P' );
BEGIN util.alter_procedure( 'OPS$TKYTE', 'P' ); END;
*
ERROR at line 1:
ORA-20001: "SCOTT" does not have access to the schema "OPS$TKYTE"
ORA-06512: at "UTIL.ALTER_PROCEDURE", line 21
ORA-06512: at line 1
scott%ORA11GR1> connect /
Connected.
ops$tkyte%ORA11GR1> exec util.add_mapping( 'SCOTT', 'OPS$TKYTE' );
PL/SQL procedure successfully completed.
ops$tkyte%ORA11GR1> connect scott/tiger
Connected.
scott%ORA11GR1> exec util.alter_procedure( 'OPS$TKYTE', 'P' );
PL/SQL procedure successfully completed.