Reading USER* Views with different user

Yesterday a colleague of mine asked me for assistance. He is developing an database application for a customer and the customer requests to have all the database objects in one user schema and use another user with appropriate privileges for the application. In other words, the schema owner is responsible for the data model, basically doing DDL, while the schema user takes care of the data content doing DML.
In our case the schema owner also owns and executes database jobs which the schema user should be able to view. But the schema user has only privileges to view his own jobs. There is no “select any dictionary” nor can we do something like this:

SQL> select on user_scheduler_jobs to schema_user;

So the question was, how can we read the scheduler jobs owned by the schema owner when connected as the schema user.
The solution was creating a pipelined function as the schema owner that returns the content of USER_SCHEDULER_JOBS. Within PL/SQL one can choose which privileges should be used when executing the code. By default the code runs using “definer rights” which means it is run with the owners privileges. The other option is “invokers rights”, the code would then run with the callers privileges.
So we simply created a small PL/SQL package and a view to implement the solution. We have two users:

SQL> connect system/******@mydb
Connected.
SQL> create user schema_owner identified by owner ;

User created.

SQL> create user schema_reader identified by reader;

User created.

SQL> grant create session, create view, create table, create procedure, create job to schema_owner;

Grant succeeded.

SQL> grant create session, create synonym to schema_reader;

Grant succeeded.

New create the package for reading. We used a package because we can put the necessary type definition in there and user the “rowtype” attribute which is not possible when creating the type directly outside the package.
Assuming that the schema owner also has the “create type” privilege, that would happen:

SQL> create type t_scheduler_jobs is table of user_scheduler_jobs%rowtype;
  2  /

Warning: Type created with compilation errors.

SQL> show err
Errors for TYPE T_SCHEDULER_JOBS:

LINE/COL ERROR
-------- -----------------------------------------------------------------
0/0      PL/SQL: Compilation unit analysis terminated
1/35     PLS-00329: schema-level type has illegal reference to
         PUBLIC.USER_SCHEDULER_JOBS

So this is the package specification:

SQL> connect schema_owner/owner@mydb
Connected.
SQL> create or replace package pkg_read_dict
  2  as
  3
  4    type t_scheduler_jobs is table of user_scheduler_jobs%rowtype;
  5
  6    function read_scheduler_jobs return t_scheduler_jobs pipelined;
  7  end;
  8  /

Package created.

The package body is this:

SQL> create or replace package body pkg_read_dict
  2  as
  3    function read_scheduler_jobs return t_scheduler_jobs pipelined
  4    as
  5      v_scheduler_jobs user_scheduler_jobs%rowtype;
  6      cursor c_scheduler_jobs is select * from user_scheduler_jobs;
  7    begin
  8      open c_scheduler_jobs;
  9      loop
 10        fetch c_scheduler_jobs into v_scheduler_jobs;
 11        exit when c_scheduler_jobs%notfound;
 12        pipe row(v_scheduler_jobs);
 13      end loop;
 14      close c_scheduler_jobs;
 15      return;
 16    end;
 17  end;
 18  /

Package body created.

Now we need a simple job to test the package.

SQL> begin
  2    dbms_scheduler.create_job(
  3      job_name => 'TEST_JOB',
  4      job_type => 'PLSQL_BLOCK',
  5      job_action => 'begin null; end;',
  6      start_date => systimestamp,
  7      repeat_interval => 'FREQ=MINUTELY',
  8      enabled => true
  9    );
 10  end;
 11  /

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

Ok, let’s test the function:

SQL> select job_name, enabled from table(pkg_read_dict.read_scheduler_jobs);

JOB_NAME             ENABL
-------------------- -----
TEST_JOB             TRUE

Fine, now we just need to create a view and grant select on that view to the schema reader.

SQL> create or replace force view my_scheduler_jobs
  2  as
  3  select * from table(pkg_read_dict.read_scheduler_jobs);

View created.

SQL> grant select on my_scheduler_jobs to schema_reader;

Grant succeeded.

The schema reader is now able to create a synonym for that view and query the jobs owned by schema owner:

SQL> conn schema_reader/reader@mydb
Connected.
SQL> create synonym my_scheduler_jobs for schema_owner.my_scheduler_jobs;

Synonym created.

SQL> select job_name, enabled from my_scheduler_jobs;

JOB_NAME             ENABL
-------------------- -----
TEST_JOB             TRUE

It’s as simple as that. Question answered. Maybe this can help you in similar cases. If you have any qustions or addtional hints, just put it in the comments.

Advertisements