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

Managed Redo Apply using all CPU

Again and again I’m forced to setup Data Guard on Windows platforms. Windows is not my favorite platform but that does not matter sometimes. On the other hand, the Oracle command line tools are identical on every platform.
So this is the inital setup that we have:

  • 2 Servers, 2x 10 Core CPU and sufficient RAM with some TB local disks
  • servers in separate compute centers
  • Windows 2012 R2
  • Oracle Database Enterprise Edition 12.1.0.2.160119 non-CDB

Pretty obvious, that Data Guard would be a good option to decrease the unplanned outages for databases on those servers. Everything from installation to creating databases and setting up Data Guard went fine. Until we first tested a switchover. Sounds simple and in fact it is. At least in terms of syntax. But after the switchover the new standby database server was heavily loaded and did hardly respond to anything we were trying. After a while, 30 minutes or so, it was all over and the standby server was working fine again with no load at all. At this point in time there was no data nor any application load at all in the database. Just an freshly created empty database.
We then did another switchover to make the standby database primary again. And again, the new standby server was heavily loaded for quite some time. When investigating this behaviour we found the following in the alert.log:

2016-04-18 13:29:47.131000 +02:00
Started logmerger process
Managed Standby Recovery starting Real Time Apply
Parallel Media Recovery started with 40 slaves

The automatic parallel recovery which is enabled by default decided to use 40 processes. That makes sense when we look at the hardware specification. But my assumption was, that there must be something wrong with that. I had no proof that the recovery slaves where causing the high CPU load since the system was so unresponsive. We simply modified the Data Guard configuration to reduce the parallel degree.

DGMGRL> edit database testdb_a set property ApplyParallel = 8;
DGMGRL> edit database testdb_b set property ApplyParallel = 8;

Again we tried another switchover and monitored the new standby server. This time it was not loaded at all. I still don’t know why the 40 processes where causing such a heavy load, but for the time being I am happy with what we have now.

So be careful when setting up Data Guard. Do tests and monitor the systems. And if you ever experience heavy load when there should be no load at all, remember my posing.