pl/sql - Vergleich der Anzahl der Datensätze in den Tabellen von 2 Benutzern

Question

Ich habe eine Tabelle "stf_table" unter dem Schema "staff" mit dem Benutzernamen "staff".

Ich habe eine Tabelle "emp_table" unter dem Schema "employee" mit dem Benutzernamen "employee".

Beide befinden sich unter derselben Datenbank-SID/demselben Host/Port, aber der Benutzer "Mitarbeiter" hat keine Berechtigung zur Abfrage von Tabellen unter dem Schema "Mitarbeiter" und umgekehrt (d. h. der Benutzer "Mitarbeiter" hat keine Berechtigung zur Abfrage von Tabellen unter dem Schema "Mitarbeiter"). Aber jeder Benutzer kann die Tabelle in seinem eigenen Schema abfragen. Hinweis: Ich kann keine zusätzlichen Berechtigungen gewähren und möchte nicht beide Tabellen in ein lokales Schema auslagern müssen.

Ist es möglich, eine PL/SQL-Proc oder ein Paket zu schreiben, das Folgendes tut?

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

dann beide Zählungen in eine Datei (d.h. eine .txt- oder .dat-Datei, die nicht betroffen ist) wie folgt ausgeben:

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

Answer 1

Wenn Sie keinem der beiden Benutzer zusätzliche Privilegien gewähren wollen, haben Sie nicht viele Möglichkeiten.

Sie können eine Prozedur erstellen, die einem Superuser gehört, z.B. einem Konto, das SELECT ANY TABLE hat, (aber ノット SYS oder SYSTEM), der beide Zählungen vornimmt und die Differenz berechnet.

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

Das Entscheidende ist hier also der AUTHID DEFINER. Das bedeutet, dass die Funktion mit den Rechten des Benutzers ausgeführt wird, der die Funktion besitzt; da er die notwendigen Rechte hat, um beide Tabellen abzufragen, kann dieser Benutzer die Funktion ausführen, ebenso wie jeder andere Benutzer, dem er die EXECUTE ON GET_DIFF .

Das ist wirklich das Minimum an Bewilligung, mit dem Sie auskommen können.

---

NB: Ich bin nicht auf die Frage des Schreibens in eine Datei eingegangen, weil ich nicht glaube, dass das der Kern Ihrer Frage ist. Die Hauptsache sind die Berechtigungen.

---

"Gibt es keine Möglichkeit, dass der Proc sich als Benutzer 'staff' verbindet und die Zählung in einer Datei speichern und dann als Benutzer 'Mitarbeiter' die Zählung in der gleichen Datei speichern und vergleichen?"

Nr. T

Die Daten des PERSONALs sind Eigentum des PERSONALs und die Daten des ARBEITNEHMERS sind Eigentum des ARBEITNEHMERS. Standardmäßig kann STAFF die Daten von EMPLOYEE nicht sehen und umgekehrt. Um diese Situation zu ändern, haben Sie zwei Möglichkeiten:

1. Lassen Sie STAFF SELECT auf ihre Tabelle an EMPLOYEE gewähren und lassen Sie EMPLOYEE SELECT auf ihre Tabelle an STAFF gewähren.
2. Verwenden Sie ein Konto, das für die Tabellen beider Schemas berechtigt ist, um die Arbeit zu erledigen.

Natürlich kann es bei der ersten Variante zu Datenschutzproblemen kommen. Wenn dies Ihr Anliegen ist, gibt es einige Möglichkeiten, die Sie nutzen können:

1. Lassen Sie jedes Schema eine Ansicht besitzen, die lediglich eine Anzahl ihrer Datensätze auswählt, und gewähren Sie Berechtigungen für die Ansichten.
2. Wenn Sie über Enterprise Edition 10gR2 oder höher verfügen, können Sie VPD auf Spaltenebene verwenden: Gewähren Sie Select für die Tabelle und legen Sie eine Richtlinie fest, um alle sensiblen Daten zu verbergen. Weitere Informationen .

Answer 2

Die Erteilung von Genehmigungen ist das Richtige, aber wie Sie sagen, ist dies nicht möglich:

Eine Lösung könnte darin bestehen, dies über die Befehlszeile mit Hilfe von Skripten zu erledigen. (Der größte Nachteil ist, dass der Benutzername und das Kennwort in das Skript eingebettet werden und für alle sichtbar sind; stellen Sie also sicher, dass die Skripte entsprechend gesichert sind).

erstellen Sie zunächst 2 Skripte:

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

Erstellen Sie nun eine Skriptdatei extract.bat, um die Ausgabe zu erhalten:

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

jetzt einfach ausführen extract.bat an der Eingabeaufforderung

Answer 3

APC hat insofern Recht, als man dieses Problem nicht einfach lösen kann, ohne zusätzliche Berechtigungen zu erteilen. Vielleicht gibt es einen einfacheren Weg als den, den er vorschlägt.

Ich würde vorschlagen, eine Ansicht zu erstellen und dem anderen Schema Zugriff auf diese Ansicht zu gewähren.

Im STAFF-Schema können Sie einen View stf_table_count erstellen:

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

Dann gewähren Sie dem Mitarbeiterschema eine Auswahl in dieser Ansicht:

grant select on stf_table_count to employee;

Aus dem Mitarbeiterschema können Sie dann das Ergebnis ermitteln, ohne die Daten zu erhalten

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

Sie können diesen Vorgang auch wiederholen, um die Anzahl der Mitarbeiter im Personalschema zu ermitteln.

Ich bin kein wirklicher Oracle-Datenbank-Sicherheitsexperte, daher kann diese Antwort unterschiedlich ausfallen, aber ich denke, sie sollte funktionieren.