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

Tuesday, July 04, 2017

Wednesday, March 29, 2017

Apex 5.1.1 released

The latest version of Oracle Application Express has been made available yesterday for download from Oracle as reported by Joel Kallman.

As usual a number of issues and bugs have been fixed, read more in Apex 5.1.1 patch set notes.

Enjoy.

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