Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question.

Asked: February 02, 2009 - 12:22 pm UTC

Last updated: February 02, 2009 - 3:26 pm UTC

Version: 10.2.1

Viewed 10K+ times! This question is

You Asked

Hi Tom,

We have 2 schemas, UserA and UserB. I would like to grant ALTER ANY PROCEDURE on only UserA's schema. Is it possible? I know that when we grant alter any procedure system privilige, that user has this prvilige on every user's schema.

Thanks and regards,

and Tom said...

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.



Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, February 03, 2009 - 2:32 am UTC


ALTER PROCEDURE

sam, December 21, 2014 - 9:14 pm UTC


More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library