Friday, July 07, 2017

The importance of being CURRENT_USER

Say I am user FLAVIO.
SCHEMA1 is another schema.
I have been granted EXECUTE and DEBUG privileges on SCHEMA1.TEST_PROC through a role.

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

If I perform a query like this:

  select s.owner, s.name, s.type, s.line, s.text
    from all_source s
   where s.owner = upper('SCHEMA1') and
         s.name = upper('TEST_PROC') and
         s.type = upper('PROCEDURE');

the query returns the source lines.

If I perfom the same query inside an anonymous PL/SQL block:

declare
  type source_line is record (
    owner  varchar2(30),
    name   varchar2(30),
    type   varchar2(30),
    line   integer,
    text   varchar2(4000)
  );
  type source_lines is table of source_line;
  sources   source_lines;
begin
  select s.owner, s.name, s.type, s.line, s.text
    bulk collect into sources
    from all_source s
   where s.owner = upper('SCHEMA1') and
         s.name = upper('TEST_PROC') and
         s.type = upper('PROCEDURE');
         
  dbms_output.put_line(sources.count);
    
end;
/

It works as well.

But if I create a procedure equal to the block above:

create or replace procedure Proc1 as
  type source_line is record (
    owner  varchar2(30),
    name   varchar2(30),
    type   varchar2(30),
    line   integer,
    text   varchar2(4000)
  );
  type source_lines is table of source_line;
  sources   source_lines;
begin
  select s.owner, s.name, s.type, s.line, s.text
    bulk collect into sources
    from all_source s
   where s.owner = upper('SCHEMA1') and
         s.name = upper('TEST_PROC') and
         s.type = upper('PROCEDURE');
         
  dbms_output.put_line(sources.count);
    
end;
/

It returns no rows.

Privileges granted through roles do not work in this case unless you add AUTHID CURRENT_USER in the procedure declaration, as follows:

create or replace procedure Proc1 AUTHID CURRENT_USER as...

No comments:

yes you can!

Two great ways to help us out with a minimal effort. Click on the Google Plus +1 button above or...
We appreciate your support!

latest articles