Tablespace Quota – alles, nichts oder nur ein bisschen?

Quota – Vergabe auf Tablespace-Ebene vs. Systemprivileg

Den meisten Lesern ist sicherlich aus der Praxis bekannt, dass Quota zum Schreiben von Daten auf zwei unterschiedliche Arten vergeben werden kann. Trotzdem wird es an dieser Stelle nochmals kurz erklärt, da es für das Verständnis des folgenden Textes wichtig ist.

Wenn ein Benutzer auf der Datenbank Tabellen oder Indices anlegen möchte, benötigt er dafür Quota in dem Tablespace, wo das Objekt liegen soll. Diese Quota kann auf zwei Arten vergeben werden:

1. Mit der Zuordnung einer limitierten oder unlimitierten Quota für diesen User auf einem (oder mehreren) explizit benannten Tablespace:
   ALTER USER scott QUOTA 10M ON users;
   Oder:
2. Mit der Vergabe des Systemprivilegs UNLIMITED TABLESPACE an den User:
   GRANT UNLIMITED TABLESPACE TO SCOTT;

Beide Arten der Quota-Vergabe ermöglichen im Ergebnis dem User, Segmente (Tabellen, Indices) abzulegen (dass er das Recht hat, überhaupt Tabellen anzulegen, wird hier vorausgesetzt). Was ist nun aber der Unterschied?

In der ersten Variante bekommt ein User Quota auf einem bestimmten Tablespace. Nur hier darf er schreiben. Bei jedem Schreib- oder Löschvorgang prüft Oracle im Hintergrund, ob der User unterhalb seiner erlaubten Quota bleibt bzw. wie viel Bytes frei werden.

Bekommt ein User hingegen das Systemprivileg UNLIMITED TABLESPACE wie in Variante 2, darf er auf allen Tablespaces der Datenbank, inklusive SYSTEM- oder SYSAUX-Tablespace, Segmente erstellen und dort unbegrenzt Platz belegen. Das „Limit“ ist allein der Platz im darunter liegenden Datenfile. Oracle führt im Hintergrund keinerlei Prüfungen bezüglich Platzverbrauch des Users durch.

Um zu prüfen, wieviel Quota ein User besitzt, müssen immer zwei DBA-Views zu Rate gezogen werden: DBA_TS_QUOTAS für Quota die auf bestimmte Tablespaces besteht sowie DBA_SYS_PRIVS, um zu prüfen, ob das Systemprivileg UNLIMITED TABLESPACE vergeben wurde.

Das Systemprivileg UNLIMITED TABLESPACE is ein sehr hohes Recht. Versehentliches Anlegen von Objekten in „falschen“ Tablespacec (vor allem in SYSTEM oder SYSAUX!) wird ebensowenig unterbunden wie versehentliches Einfügen von Massendaten, die den Rahmen der eigentlich geplanten Datenmenge um ein Vielfaches übersteigt. Deshalb sollte dieses Systemprivileg in der Praxis nur in Ausnahmefällen an Nicht-DBA-User verleihen. Stattdessen sollten Nicht-DBA-User möglichst immer explizit Quota auf den benötigten Tablespaces bekommen.

In unserem Fall mussten wir UNLIMITED TABLESPACE ausnahmsweise an einen Applikations-User granten, um einen Bug zu umgehen, der zu langen Laufzeiten beim Droppen und Erstellen von Tabellen führte. Nachdem der Bug gepatcht war, entzogen wir das UNLIMITED TABLESPACE Privileg wieder. Und dabei fiel uns das im Folgenden beschriebene Verhalten auf.

Konstallation 1: limitierte QUOTA auf einem Tablespace sowie UNLIMITED TABLESPACE Systemprivileg

Zuerst werden für den Testfall der Tablespace USERS und der User SCOTT angelegt. Scott bekommt neben dem CREATE SESSION-Recht Quota von 10 Megabyte auf seinem Default Tablespace USERS:

CONNECT / AS SYSDBA
Connected.

CREATE BIGFILE TABLESPACE USERS DATAFILE '+DATA' SIZE 25M;

CREATE USER scott IDENTIFIED BY Whatever2l#ke
    DEFAULT TABLESPACE users
    TEMPORARY TABLESPACE temp;

GRANT CREATE SESSION, CREATE TABLE TO scott;

ALTER USER scott QUOTA 10M ON users;

Ein Check der relevanten Rechte ergibt:

SELECT
PRIVILEGE
FROM dba_sys_privs
WHERE grantee = 'SCOTT';

PRIVILEGE
----------------------------------------
CREATE TABLE
CREATE SESSION

SELECT tablespace_name,
       username,
       bytes,
       max_bytes
FROM dba_ts_quotas
WHERE username = 'SCOTT';

TABLESPACE_NAME USERNAME             BYTES  MAX_BYTES
--------------- --------------- ---------- ----------
USERS           SCOTT                    0   10485760

Wir sehen: User SCOTT hat kein UNLIMITED TABLESPACE Systemprivileg. Er hat aber 10 Megabyte Quota auf dem Tablespace USERS.

Dass SCOTT damit Tabellen erstellen kann, zeigt der Test:

CONNECT scott/Whatever2l#ke
Connected.
CREATE TABLE t1 ( bemerkung    VARCHAR2 (100) );

Table created.

Wir verbinden uns erneut als User mit DBA-Rechten und granten dem User SCOTT das Systemprivileg UNLIMITED TABLESPACE:

CONNECT / AS SYSDBA
Connected.

GRANT UNLIMITED TABLESPACE TO SCOTT;
Grant succeeded.

Überprüfung der Rechte:

SELECT
PRIVILEGE
FROM dba_sys_privs
WHERE grantee = 'SCOTT';

PRIVILEGE
----------------------------------------
CREATE TABLE
CREATE SESSION
UNLIMITED TABLESPACE

SELECT tablespace_name,
       username,
       bytes,
       max_bytes
FROM dba_ts_quotas
WHERE username = 'SCOTT';

TABLESPACE_NAME USERNAME             BYTES  MAX_BYTES
--------------- --------------- ---------- ----------
USERS           SCOTT                65536   10485760

Wir sehen: der User SCOTT besitzt das UNLIMITED TABLESPACE Systemprivileg sowie explizit die 10 Megabyte Quota auf dem USERS Tablespace. Hiervon sind – bedingt durch das Anlegen der Tabelle t1 – mittlerweile 65536 Bytes belegt. Interessant ist, dass die Vergabe von UNLIMITED TABLESPACE überhaupt keine Anpassung der View DBA_TS_QUOTAS nach sich zieht. Offensichtlich wird das von Oracle als zwei komplett voneinander unabhängige Dinge gesehen. Das diese Betrachtungsweise nicht durchgängig der Fall ist, sehen wir im nächsten Schritt.

Hier entziehen wir nämlich das UNLIMITED TABLESPACE Systemprivileg wieder (auf unseren produktiven Datenbanken wurde ein Patch eingespielt, so dass dieses Recht nicht mehr für den Workaround benötigt wird). Annahme war, dass auch beim Entzug des Systemprivilegs die View DBA_TS_QUOTAS unabhängig behandelt wird. Sehen wir also, was wirklich passiert:

REVOKE UNLIMITED TABLESPACE FROM SCOTT;

Revoke succeeded.

Ein Check der Privilegien ergibt nun Folgendes:

SELECT
PRIVILEGE
FROM dba_sys_privs
WHERE grantee = 'SCOTT';

PRIVILEGE
----------------------------------------
CREATE TABLE
CREATE SESSION

Das UNLIMITED TABLESPACE Systemprivileg ist wie gewünscht entzogen.

Aber was ist das?:

SELECT tablespace_name,
       username,
       bytes,
       max_bytes
FROM dba_ts_quotas
WHERE username = 'SCOTT';

no rows selected

Auch die Quota auf den Tablespace USERS ist verschwunden! Auch das Anlegen einer Tabelle klappt tatsächlich nicht mehr:

CONNECT scott/Whatever2l#ke
Connected.
CREATE TABLE t2 ( bemerkung    VARCHAR2 (100) );
CREATE TABLE t2 ( bemerkung    VARCHAR2 (100) )
*
ERROR at line 1:
ORA-01536: space quota exceeded for tablespace 'USERS'

Erst nach dem erneuten Vergeben von Quota auf den USERS Tablespace kann SCOTT seine Tabelle anlegen:

CONNECT / AS SYSDBA
Connected.
ALTER USER scott QUOTA 10M ON users;
CONNECT scott/Whatever2l#ke
Connected.
SQL> CREATE TABLE t2 ( bemerkung    VARCHAR2 (100) );

Table created.

Erkenntnis aus diesem Test: bei der Vergabe des Systemprivilegs UNLIMITED TABLESPACE bleiben Einträge in DBA_TS_QUOTAS unberührt, beim Entzug des Systemprivilegs werden die Einträge in DBA_TS_QUOTAS entfernt und auch tatsächlich Quota-Rechte mit entzogen.

Natürlich haben wir uns an dieser Stelle gefragt, ob es von Oracle wirklich gewollt ist, sämtliche separat vergebene Tablespace Quotas für den jeweiligen User mit zu entziehen, wenn das Systemprivileg UNLIMITED TABLESPACE entzogen wird. Die Antwort auf diese Frage ist jedoch auch nicht praxisrelevant. Denn: die Quota wurde entzogen, also müssen wir sie neu vergeben, wenn das gewünscht ist. – Aber – auch diese Erkenntnis stimmt nicht immer, wie der folgende, leicht abgewandelte Test zeigt.

Konstallation 2: unlimitierte QUOTA auf einem Tablespace sowie UNLIMITED TABLESPACE Systemprivileg

Dieser Testcase ist nahezu identisch mit dem obigen. Einzige Abwandlung: statt einer Quota von 10 Megabyte wird dem User SCOTT an der entsprechenden Stelle QUOTA UNLIMITED auf den USERS Tablespace gegeben:

CONNECT / AS SYSDBA
Connected.

CREATE BIGFILE TABLESPACE USERS DATAFILE '+DATA' SIZE 25M;

CREATE USER scott IDENTIFIED BY Whatever2l#ke
    DEFAULT TABLESPACE users
    TEMPORARY TABLESPACE temp;

GRANT CREATE SESSION, CREATE TABLE TO scott;

Hier kommt nun die Abweichung zum obigen Test. Anstelle eines festen Wertes granten wir die maximal mögliche Quota auf dem Tablespace USERS and den User SCOTT:

ALTER USER scott QUOTA UNLIMITED ON users;

Ein Check der relevanten Rechte ergibt:

SELECT
PRIVILEGE
FROM dba_sys_privs
WHERE grantee = 'SCOTT';

PRIVILEGE
----------------------------------------
CREATE TABLE
CREATE SESSION

SELECT tablespace_name,
       username,
       bytes,
       max_bytes
FROM dba_ts_quotas
WHERE username = 'SCOTT';

TABLESPACE_NAME USERNAME             BYTES  MAX_BYTES
--------------- --------------- ---------- ----------
USERS           SCOTT                    0         -1

Wir sehen: User SCOTT hat kein UNLIMITED TABLESPACE Systemprivileg. Er hat aber unbegrenzt Quota ("-1" bei MAX_BYTES) auf dem Tablespace USERS.

Weiter geht es mit dem Test wie oben.

Test, dass Scott wirklich Tabellen erstellen kann:

CONNECT scott/Whatever2l#ke
Connected.
CREATE TABLE t1 ( bemerkung    VARCHAR2 (100) );

Table created.

Verbinden mit DBA-Rechten, grant  UNLIMITED TABLESPACE an den User SCOTT:

CONNECT / AS SYSDBA
Connected.

GRANT UNLIMITED TABLESPACE TO SCOTT;

Grant succeeded.

Und eine Überprüfung der Rechte:

SELECT
PRIVILEGE
FROM dba_sys_privs
WHERE grantee = 'SCOTT';

PRIVILEGE
----------------------------------------
CREATE TABLE
CREATE SESSION
UNLIMITED TABLESPACE

SELECT tablespace_name,
       username,
       bytes,
       max_bytes
FROM dba_ts_quotas
WHERE username = 'SCOTT';

TABLESPACE_NAME USERNAME             BYTES  MAX_BYTES
--------------- --------------- ---------- ----------
USERS           SCOTT                65536         -1

Entzug des UNLIMITED TABLESPACE Systemprivileg:

REVOKE UNLIMITED TABLESPACE FROM SCOTT;

Revoke succeeded.

Ein Check der Privilegien ergibt nun Folgendes:

SELECT
PRIVILEGE
FROM dba_sys_privs
WHERE grantee = 'SCOTT';

PRIVILEGE
----------------------------------------
CREATE TABLE
CREATE SESSION

Das UNLIMITED TABLESPACE Systemprivileg ist wie gewünscht entzogen.

Jetzt kommt das für uns Überraschende. Wir erinnern uns - beim ersten Test war mit dem Entzug des UNLIMITED TABLESPACE auch die Quota auf dem USERS Tablespace verschwunden. Dies sieht bei diesem Test anders aus:

SELECT tablespace_name,
       username,
       bytes,
       max_bytes
FROM dba_ts_quotas
WHERE username = 'SCOTT';

TABLESPACE_NAME USERNAME             BYTES  MAX_BYTES
--------------- --------------- ---------- ----------
USERS           SCOTT                65536         -1

Der User SCOTT hat diesmal seine QUOTA UNLIMITED behalten. Und er kann dementsprechend auch Tabellen erstellen:

CONNECT scott/Whatever2l#ke
Connected.
CREATE TABLE t2 ( bemerkung    VARCHAR2 (100) );

Table created.

Ergebnis dieses Tests: User SCOTT hatte – im Gegensatz zur ersten Konstellation – unlimitierte Quota auf einem Tablespace. Wenn dies der Fall ist und ein zusätzlich vergebenes Systemprivileg UNLIMITED TABLESPACE entzogen wird, bleiben Einträge in DBA_TS_QUOTAS sowie Quota erhalten. Die Tests zeigen: Das Verhalten von Oracle ist an dieser Stelle nicht durchgängig gleich.

Fazit

Vorsorge ist besser als Nachsorge. Vor dem Entzug von UNLIMITED TABLESPACE am besten immer die Scripts zum nachträglichen Geradeziehen der Quota auf separate Tablespaces der User vorbereiten und die Quota neu vergeben, wenn das gewünscht ist.

Und: Auch so simple Dinge, wie der Entzug des Systemprivilegs UNLIMITED TABLESPACE zeigen nicht unbedingt erwartbare Effekte und sollten besser erst auf einer Testumgebung durchgespielt werden.