23ai: Schema Level Grants

Vor Kurzem ging es in unserem Blog um die Trennung von Schema und Nutzern bzw. Entwickleraccounts. Der heutige Blogbeitrag addressiert ein ähnliches Thema. Eine Applikation sollte niemals mit dem Schemanutzer arbeiten, sondern einen separaten Nutzer zur Anmeldung an der Datenbank verwenden. Dieser Nutzer benötigt dann natürlich entsprechende Rechte auf den Objekten des Schemas. Bisher mussten diese Berechtigungen alle einzeln vergeben werden, entweder direkt an den entsprechendenen Benutzer oder an eine Rolle, die wiederum den Nutzern zugewiesen werden kann. Je nach Umfang des Schemas können das eine ganze Menge Objekte werden, auf die Berechtigungen verteilt werden müssen. Das lässt sich zwar mit relativ einfachen Mitteln automatisieren, aber auch nur für vorhandene Objekte. Für jedes dem Schema neu hinzugefügte Objekt müssen ebenso wieder Berechtigungen verteilt werden. 

Dieses Prozedere kann dazu führen, dass eben doch der Schemanutzer Verwendung findet oder schlimmer noch, dass ANY-Privilegien vergeben werden. Beides ist aus Security-Sicht eine mittlere Katastrophe und sollte unbedingt vermieden werden.

Daher ist das neue Feature der Schema Grants in 23ai durchaus einen Blick wert. Dieses ermöglicht es, ANY-Privilegien auf ein bestimmtes Schema zu beschränken. Folgendes Beispiel: Es gibt einen Schemaeigner namens "MARCO", auf all dessen Objekte sollen Leserechte an den Account "READER" vergeben werden. Mit welchem Account gerade gearbeitet wird, ergibt sich aus dem SQL-Prompt. Bisher wurde ein neues Objekt im Schema angelegt, im Beispiel eine Tabelle, und dann darauf die Leserechte vergeben. An der Stelle sollte wirklich das READ-Recht verwendet werden, da dieses tatsächlich nur lesenden Zugriff erlaubt. Das SELECT-Recht dagegen erlaubt zwar auch keine Änderungen an den Daten, ermöglicht aber ein SELECT ... FOR UPDATE, dass Datensätze sperren und somit andere Aktivitäten be- oder verhindern kann.

MARCO @ DB23AI:PDB1:>create table t1 (id number, txt varchar2(10));

Table created.

MARCO @ DB23AI:PDB1:>insert into t1 values (1, 'One');

1 row created.

MARCO @ DB23AI:PDB1:>commit;

Commit complete.

MARCO @ DB23AI:PDB1:>grant read on t1 to reader;

Grant succeeded.

 Der Lesenutzer hat nun folgerichtig die Rechte, um die Inhalte der Tabelle anzuzeigen.

READER @ DB23AI:PDB1:>select * from marco.t1;

   ID TXT
_____ ______
    1 One

Wird nun eine neue Tabelle im Schema angelegt ohne die Berechtigungen zu vergeben, so kann der Lesenutzer auch nicht auf die Inhalte zugreifen.

MARCO @ DB23AI:PDB1:>create table t2 (id number, txt varchar2(10));

Table created.

MARCO @ DB23AI:PDB1:>insert into t2 values (2, 'Two');

1 row created.

MARCO @ DB23AI:PDB1:>commit;

Commit complete.

READER @ DB23AI:PDB1:>select * from marco.t2;

Error starting at line : 1 in command -
select * from marco.t2
Error at Command Line : 1 Column : 21
Error report -
SQL Error: ORA-00942: table or view "MARCO"."T2" does not exist
Help: docs.oracle.com/error-help/db/ora-00942/
00942. 00000 -  "table or view%s does not exist"
*Cause:    The specified table or view did not exist, or a synonym
           pointed to a table or view that did not exist.
           To find existing user tables and views, query the
           ALL_TABLES and ALL_VIEWS data dictionary views. Certain
           privileges may be required to access the table. If an
           application returned this message, then the table that the
           application tried to access did not exist in the database, or
           the application did not have access to it.
*Action:   Check each of the following
           - The spelling of the table or view name is correct.
           - The referenced table or view name does exist.
           - The synonym points to an existing table or view.

More Details :
docs.oracle.com/error-help/db/ora-00942/

Man sieht hier übrigens auch, dass das SQLcl im Gegensatz zum althergebrachten SQL*Plus nicht nur die Fehlermeldung ausspuckt, sondern auch gleich die möglichen Ursachen und die Maßnahmen aus der Fehlerdokumentation sowie einen Link in die Online-Dokumentation.

Jetzt kommen die Schema-Privilegien ins Spiel. Es wird das READ ANY TABLE Recht an den Lesenutzer gegeben, aber eben beschränkt auf das Schema und nicht auf die ganze Datenbank.

MARCO @ DB23AI:PDB1:>grant read any table on schema marco to reader;

Grant succeeded.

 Nun kann der Lesenutzer auch auf die zweite Tabelle zugreifen, ohne dafür einen direkten Grant oder einen Grant über eine Rolle erhalten zu haben.

READER @ DB23AI:PDB1:>select * from marco.t2;

   ID TXT
_____ ______
    2 Two

Auch auf eine weitere Tabelle, die im Schema angelegt wird, hat der Lesenutzer nun sofort Zugriff.

MARCO @ DB23AI:PDB1:>create table t3 (id number, txt varchar2(10));

Table created.

MARCO @ DB23AI:PDB1:>insert into t3 values (3, 'Three');

1 row created.

MARCO @ DB23AI:PDB1:>commit;

Commit complete.

READER @ DB23AI:PDB1:>select * from marco.t3;

   ID TXT
_____ ________
    3 Three

Alles in allem eine sehr nützliche neue Funktion, die das Trennen von Schemas und Nutzern enorm vereinfacht und vor allem auch die Sicherheit innerhalb der Datenbank erhöht.