Monthly Archives: August 2015

I came across another example today where I was positive I knew how something worked, but was proven wrong. This is why it is so critical to be able to make quality test cases. This specific case had to do with definer rights in PL/SQL. Let me set up an example to explain.

First as user A, I will create a dummy table with 10 rows:

SQL> create table cm2_priv_test
as
select rownum rn
from dual
connect by level <= 10

Table created.

Then I will create a role, grant select on my table to the role, then grant the role to user B:

SQL> create role cm2_priv_test_role

Role created.

SQL> grant select on cm2_priv_test to cm2_priv_test_role

Grant complete.

SQL> grant cm2_priv_test_role to b

Grant complete.

Now, I log in as user B, and use my privilege from the role to select from that table:

SQL> select count(*)
from a.cm2_priv_test

  COUNT(*)
----------
        10
1 row selected.

Pretty straight-forward so far. But what happens when you try to put that into PL/SQL?

SQL> create or replace procedure cm2_test
as
cnt PLS_INTEGER;
begin
    select count(*)
    INTO cnt
    from a.cm2_priv_test;

    dbms_output.put_line('Count: '||cnt);
end;
Warning: compiled but with compilation errors
SQL> show errors
Errors for PROCEDURE CM2_TEST

LINE/COL ERROR                                                            
-------- -----------------------------------------------------------------
7/19     PL/SQL: ORA-00942: table or view does not exist                  
5/5      PL/SQL: SQL Statement ignored

We get an error that the table doesn't exist, because we only have access to it through a role. This trips up a lot of people, but I have run into it enough times that I recognize what is happening pretty quickly when I see this behavior.

We can get past the compilation error by changing this to dynamic SQL, but that doesn't get you very far:

SQL> create or replace procedure cm2_test
as
cnt PLS_INTEGER;
begin
    execute immediate '
    select count(*)
    from a.cm2_priv_test'
    INTO cnt;

    dbms_output.put_line('Count: '||cnt);
end;
Procedure created.


SQL> exec cm2_test()
>> BEGIN cm2_test(); END;
Error at line 2
ORA-00942: table or view does not exist
ORA-06512: at "B.CM2_TEST", line 5
ORA-06512: at line 1

We could grant the select directly to user B instead of through a role, or we could have our procedure use Invokers Rights by declaring the procedure with the "AUTHID CURRENT_USER" clause.
Those would work, but that isn't the point of this post. The point is to go over what I had no idea would work as another option...

For this example, we will now log on to a different database, and create a database link back to DB1 as user B:

SQL> create database link b_db1
connect to b
identified by "tiger"
using 'DB1'

Database link created.

So now we can test to ensure we can still get to our table using this database link:

SQL> select count(*)
from a.cm2_priv_test@b_db1

  COUNT(*)
----------
        10

1 row selected.

Makes sense so far, but this is where it gets strange. On this new database, if I create the same procedure as above only now using the database link, it works fine:

SQL> create or replace procedure cm2_test
as
cnt PLS_INTEGER;
begin
    select count(*)
    INTO cnt
    from a.cm2_priv_test@b_db1;

    dbms_output.put_line('Count: '||cnt);
end;

Procedure created.

I can even run it, and get my desired results:

SQL> set serverout on
SQL> exec cm2_test()
Count: 10

 PL/SQL procedure successfully completed.

This works for the dynamic SQL version of the procedure as well:

SQL> create or replace procedure cm2_test
as
cnt PLS_INTEGER;
begin
    execute immediate '
    select count(*)
    from a.cm2_priv_test@b_db1'
    INTO cnt;

    dbms_output.put_line('Count: '||cnt);
end;

Procedure created.

SQL> set serverout on
SQL> exec cm2_test()
Count: 10

 PL/SQL procedure successfully completed.

I would have never guessed that coming in over a db_link changes how you can use privileges granted through a role. I am sure this behavior is mentioned in the documentation, but I have yet to find it. It kind of makes sense if you think about the db_link usage as logging in and running the query in the SQL domain only, so the PL/SQL rules don't come into play on the remote database.. just something I wouldn't have thought about until I came across it.

My example was on version 11.2.0.2, but I would bet it works the same on 12c as well.

I learned something new, so it was a good day! 🙂