I've table 'stf_table' underneath the schema 'staff' with username 'staff'.

I've table 'emp_table' underneath the schema 'employee' with username 'employee'.

These two they are under exactly the same database SID/host/port but user 'staff' does not have privelege to question tables under schema 'employee' and the other way around (ie. user 'employee' does not have privelege to question tables under schema 'staff'). But each user can query the table under their very own schema. Note: I am unable to grant extra rights and I'd rather not need to dump both tables right into a local schema.

Can you really write a PL/SQL proc or package that will perform the following?

 select count(*) from stf_table;
 select count(*) from emp_table;

then output both counts to some file (ie b .txt or .dat not concerned) like so:

stf_table count: 47830
emp_table count: 36462
difference: 11368
counts match: FALSE

If you won't want to grant additional rights either to user, you do not have several choices.

You may create a process possessed with a super user, say a free account that has Choose ANY TABLE, (but not SYS or SYSTEM) which does both counts and computes the main difference.

create function get_diff 
    return pls_integer
    authid definer
    c1 pls_integer;
    c2 pls_integer;
    select count(*) 
    into c1
    from staff.stf_table;
    select count(*)
    into c2
    from employee.emp_table;

    return abs(c1-c2);
end get_diff;

Therefore the crucial factor this is actually the AUTHID DEFINER. Which means the function runs using the permissions from the user who is the owner of the function simply because they possess the neecssary permissions to question both tables that user can run the function, and thus can every other user to whom they grant EXECUTE ON GET_DIFF.

That actually may be the minimum about of granting you will get away with.

NB: I've not addressed the question of conntacting personal files, because I do not think this is the thrust of the question. The primary factor is permissions.

"Can there be not a way the proc could connect as user 'staff' save the count to some file. then connect as user 'employee' save the count towards the same file and compare?"

No. That isn't how a security model works.

STAFF's information is possessed by STAFF and EMPLOYEE's information is possessed by Worker. Automatically, STAFF cannot see EMPLOYEE's data and the other way around. To alter this case you've two options:

  1. Have STAFF grant Choose on their own table to Worker and also have Worker grant Choose on their own table to STAFF.
  2. Make use of an account that has rights on schemas' tables to complete the job.

Clearly, there may be data protection difficulties with the very first approach. If that's your concern you will find some unique features accessible to you:

  1. Have each schema own a view which just chooses a count of the records, and grant rights around the sights.
  2. For those who have Enterprise Edition 10gR2 or greater, you should use column-level VPD: grant choose up for grabs put put an insurance policy in position to cover all of the sensitive data. Find out more.

Granting permissions may be the correct factor to complete however while you condition no chance:

one solution would be to get it done in the command line using scripts. (the greatest drawback may be the username &lifier password could be baked into the script for those to determine, so make certain the scripts are properly guaranteed)

first create 2 scripts:


set timing off
set feedback off
set pages 0
select count(*) from stf_table;


set timing off
set feedback off
set pages 0
select count(*) from emp_table;

now produce a script file extract.softball bat to obtain the output:

 @echo off
 FOR /F "usebackq delims=!" %%i IN (`sqlplus -s username/password@db_a @a.sql`) DO set resulta=%%i 

 FOR /F "usebackq delims=!" %%i IN (`sqlplus -s username/password@db_b @b.sql`) DO set resultb=%%i 

 set /a diff=%resulta%-%resultb%

 echo stf_table count %resulta% 
 echo emp_table count %resultb%
 echo difference %diff%

 IF %diff%=0 (
 echo Counts match TRUE
 ) ELSE (
 echo Counts match FALSE)

now simply execute extract.bat in the command prompt