pl/sql - 比较 2 个用户表的记录计数

Question

我在模式“staff”下有一个用户名为“staff”的表“stf_table”。

我在模式“employee”下有表“emp_table”，用户名为“employee”。

这两个都位于相同的数据库 SID/主机/端口下，但用户“staff”没有查询模式“employee”下的表的权限，反之亦然（即用户“employee”没有查询模式“employee”下的表的权限）模式“员工”）。但每个用户都可以在自己的模式下查询该表。注意：我无法授予额外的权限，并且我不想将两个表转储到本地模式中。

是否可以编写一个 PL/SQL 过程或包来执行以下操作？

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

然后将两个计数输出到文件（即不涉及的 .txt 或 .dat），如下所示：

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

Answer 1

如果您不想向任一用户授予额外权限，则没有太多选择。

您可以创建一个由超级用户拥有的过程，例如具有 SELECT ANY TABLE（但不是 SYS 或 SYSTEM）的帐户，该帐户既进行计数又计算差异。

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;
/

所以这里最关键的是AUTHID DEFINER。这意味着该函数以拥有该函数的用户的权限运行；因为他们拥有查询两个表所需的权限，因此用户可以运行该函数，并且他们授予 EXECUTE ON GET_DIFF 的任何其他用户也可以。

这确实是你能逃脱惩罚的最低要求。

---

注意：我还没有解决写入文件的问题，因为我真的不认为这是你问题的主旨。最主要的是权限。

---

“有没有办法让进程以用户‘staff’的身份连接保存
计数到一个文件。然后以用户“employee”连接，将计数保存到
相同的文件并比较？”

不是安全模型的工作方式。STAFF

的数据归 STAFF 所有，EMPLOYEE 的数据归 EMPLOYEE 所有。默认情况下，STAFF 无法看到 EMPLOYEE 的数据，反之亦然。要改变这种情况，您有两个方法选项：

1. 让 STAFF 将其表上的 SELECT 授予 EMPLOYEE，并让 EMPLOYEE 将其表上的 SELECT 授予 STAFF
2. 使用具有以下权限的帐户 。

。显然，第一种方法很可能存在数据保护问题，您可以进行一些改进：

1. 让每个模式拥有一个仅选择计数的视图 如果您有 Enterprise Edition 10gR2 或更高版本
2. ，则可以使用列级 VPD：在表上授予 select 放置策略以隐藏所有敏感数据。 href="http://download.oracle.com/docs/cd/B13789_01/network.101/b10773/apdvcntx.htm#1008486" rel="nofollow">了解更多信息。

Answer 2

授予权限是正确的做法，但是正如您所说这是不可能的：

一种解决方案可能是使用脚本在命令行中执行此操作。
（最大的缺点是用户名和密码将嵌入脚本中供所有人查看，因此请确保脚本得到适当的保护）

首先创建 2 个脚本：

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

现在创建一个脚本文件提取。 bat 获取输出：

 @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)

现在只需在命令提示符下执行 extract.bat

Answer 3

APC 是对的，因为如果不授予额外的权限，您无法轻松解决此问题。可能有比他建议的更简单的方法。

我建议创建一个视图并将对该视图的访问权限授予其他模式。

在 STAFF 模式中，您可以创建一个视图 stf_table_count：

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

然后将该视图上的 select 授予员工模式：

grant select on stf_table_count to employee;

然后从员工模式中您可以找到结果，而无需获取数据

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

您也可以重复此过程以获取员工中的员工计数架构。

我并不是真正的 Oracle 数据库安全专家，因此您对这个答案的理解可能会有所不同，但我认为它应该有效。