pl/sql - compare record counts across 2 user's tables

I have table 'stf_table' under the schema 'staff' with username 'staff'.

I have table 'emp_table' under the schema 'employee' with username 'employee'.

Both of these are under the same database SID/host/port but user 'staff' doesn't have privelege to query tables under schema 'employee' and vice versa (ie. user 'employee' doesn't have privelege to query tables under schema 'staff'). But each user can query the table under their own schema. Note: I cannot grant extra privileges and I don't want to have to dump both tables into a local schema.

Is it possible to write a PL/SQL proc or package that would do 开发者_Python百科the following?

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

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

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

If you don't want to grant additional privileges to either user, you don't have many options.

You can create a procedure owned by a super user, say an account which has SELECT ANY TABLE, (but not SYS or SYSTEM) which does both counts and calculates the difference.

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

    return abs(c1-c2);
end get_diff;
/

So the crucial thing here is the AUTHID DEFINER. That means the function runs with the permissions of the user who owns the function; because they have the neecssary permissions to query both tables that user can run the function, and so can any other user to whom they grant EXECUTE ON GET_DIFF.

That really is the minimum about of granting you can get away with.

NB: I haven't addressed the question of writing to a file, because I don't really think that's the thrust of your question. The main thing is permissions.

"Is there no way that the proc could connect as user 'staff' save the count to a file. then connect as user 'employee' save the count to the same file and compare?"

No. That's not the way the security model works.

STAFF's data is owned by STAFF and EMPLOYEE's data is owned by EMPLOYEE. By default, STAFF cannot see EMPLOYEE's data and vice versa. To change this situation you have two options:

1. Have STAFF grant SELECT on their table to EMPLOYEE and have EMPLOYEE grant SELECT on their table to STAFF.
2. Use an account which has privileges on both schemas' tables to do the work.

Obviously, there may well be data protection issues with the first approach. If that is your concern there are some refinements open to you:

1. Have each schema own a view which just selects a count of their records, and grant privileges on the views.
2. If you have Enterprise Edition 10gR2 or higher, you can use column-level VPD: grant select on the table put put a policy in place to hide all the sensitive data. Find out more.

Granting permissions is the correct thing to do however as you state this is not possible:

one solution could be to do it at the command line using scripts. (the biggest drawback is the username & password would be embedded in the script for all to see, so make sure the scripts are appropriately secured)

first create 2 scripts:

a.sql:

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

b.sql:

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

now create a script file extract.bat to get 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 at the command prompt

APC is right in that you can't solve this problem easily without granting additional permissions. There might be a simpler way than what he is suggesting.

I would suggest creating a view and granting access to that view to the other schema.

In the STAFF schema you can create a view stf_table_count:

create or replace view stf_table_count
as
select count(*) as num_rows
from stf_count;

Then grant select on that view to the employee schema:

grant select on stf_table_count to employee;

Then from the employee schema you can find the result without getting the data

select num_rows from staff.stf_table_count
union all
select count(*) from emp_table;

You can also repeat this process to get the employee counts in the staff schema.

I'm not really an Oracle database security expert so your mileage may vary on this answer but I think it should work.