10. Dezember 2014

Betriebssystem-Aufrufe mit SQL und PL/SQL - mit anderem Unix-User ...?

External calls as a different Unix user: DBMS_SCHEDULER or Java?
Am 12. und 13. Februar 2015 veranstaltet die DOAG eine SIG Development Veranstaltung. Inhalt ist die Programmierung in der Datenbank - vornehmlich also alles rund um SQL und PL/SQL. Es sollen aber auch die im Datenbankumfeld immer wichtiger werdenden Themen wie XML, JSON, Spatial oder unstrukturierte Daten betrachtet werden. Die DOAG sucht derzeit Vortragende - wenn Ihr also etwas habt, was Ihr mit der Community teilen möchtet, meldet euch bei der DOAG.
Heute geht es um das Thema "Betriebssystem-Kommandos" mit der Datenbank ausführen. Hierzu gibt es zwei grundsätzliche Ansätze:
  • Man nutzt das eingebaute PL/SQL Paket DBMS_SCHEDULER. Damit lässt sich ein Job einrichten, der einen Betriebssystem-Call ausführt. DBMS_SCHEDULER ist Teil aller Datenbankeditionen; man braucht also keinerlei Installation. Nachteil ist, dass man an STDIN, STDOUT und STDERR nicht herankommt; die Konsolen-Ausgabe der Programme sieht man in der Datenbank nicht.
  • Alternativ kann das auf Java in der Datenbank basierende Paket für Dateisystem-Zugriff und Betriebssystem-Kommandos hergenommen werden. Es bietet die Dinge an, die mit den eingebauten Features nicht möglich sind, also Ausführen eines Shell-Kommandos mit Zugriff auf STDIN, STDOUT oder STDERR oder das Abrufen eines Dateisystem-Listings als Tabelle.
Bei beiden Varianten wird der eigentliche Betriebssystem-Aufruf standardmäßig als der OS-User getätigt, unter dem die Oracle-Prozesse laufen. Mit DBMS_SCHEDULER ist es schon länger möglich, diesen User zu ändern, das ist aber nicht überall bekannt. Mit der Java-Variante war es lange Zeit überhaupt nicht möglich, den Betriebsystem-User zu ändern, das geht ab der Version 11.2.0.4. In diesem Blog Posting stelle ich beide Varianten vor - zeige aber die Anwendung in Oracle12c.

Betriebssystem-User für DBMS_SCHEDULER festlegen

Für Betriebssystem-Aufrufe, die mit DBMS_SCHEDULER realisiert werden, steht ab Version 12.1 das neue PL/SQL Paket DBMS_CREDENTIAL bereit. In Version 11 ist die im folgenden verwendete Prozedur CREATE_CREDENTIAL im Paket DBMS_SCHEDULER selbst enthalten (ab 12c deprecated). Mit CREATE_CREDENTIAL kann ein Username-Password-Paar hinterlegt werden. Diese Credentials werden verschlüsselt in der Datenbank abgelegt und zur Ausführung des Betriebssystem-Aufrufs genutzt. Zum Erzeugen eines Credentials wird das Systemprivileg CREATE CREDENTIAL benötigt. Nachdem dieses Privileg eingeräumt wurde, erzeugt ein Datenbankuser (bspw. SCOTT) neue Credentials wie folgt.
begin
  dbms_credential.create_credential(
    credential_name => 'CRED_TESTUSER',
    username        => 'testuser',
    password        => 'oracle', 
    database_role   => null,
    enabled         => true
  );
end;
/
Damit ein Datenbankuser mit DBMS_SCHEDULER überhaupt externe Jobs starten kann, braucht er das Systemprivileg CREATE EXTERNAL JOB. Mit DBMS_SCHEDULER wird das neue Credential dann wie folgt genutzt
begin
  dbms_scheduler.create_job(
    job_name        => 'JOB_TOUCH_FILE',
    job_type        => 'EXTERNAL_SCRIPT',
    job_action      => '/bin/touch /tmp/myfile',
    credential_name => 'CRED_TESTUSER',
    auto_drop       => true,
    enabled         => true,
    start_date      => systimestamp 
  );
end;
/
Dieser Job läuft sofort los und führt das touch Kommando als Unix-Nutzer testuser aus. Schaut man danach ins /tmp Verzeichnis, so ist die Datei mit dem richtigen User angelegt.
$ ls -ltrh

total 28K
:
srwxr-xr-x 1 oracle   oinstall    0 Nov 10 13:42 mapping-oracle
drwx------ 2 oracle   oinstall 4.0K Nov 10 13:42 orbit-oracle
drwxr-xr-x 2 oracle   oinstall 4.0K Nov 13 16:18 hsperfdata_oracle
-rw-rw-rw- 1 testuser testuser    0 Dec  9 11:17 myfile

Betriebssystem-User für Aufrufe mit Java in der Datenbank festlegen

Das Java-Beispiel erläutere ich anhand meines oben erwähnten Java- und PL/SQL-Pakets für Dateisystem-Zugriff und Betriebssystem-Kommandos. Ein Betriebssystem-Kommando führt man mit dem Paket OS_COMMAND aus. Wie bereits erwähnt, hat man hier zusätzlich Zugriff auf die Konsole - das Beispiel mit ls zeigt das.
SQL> select os_command.exec_clob('/bin/ls -ltrh /tmp') from dual;

OS_COMMAND.EXEC_CLOB('/BIN/LS-LTRH/TMP')
--------------------------------------------------------------------------------
total 32K
drwx------ 2 oracle   oinstall 4.0K Jul 22 15:33 keyring-bGJZmr
drwx------ 2 oracle   oinstall 4.0K Aug 18 14:56 keyring-zSeMy3
drwx------ 2 oracle   oinstall 4.0K Nov 10 13:42 ssh-wUZJXQ1701
drwx------ 2 oracle   oinstall 4.0K Nov 10 13:42 keyring-7dHMKl
srwxr-xr-x 1 oracle   oinstall    0 Nov 10 13:42 mapping-oracle
drwx------ 2 oracle   oinstall 4.0K Nov 10 13:42 orbit-oracle
drwxr-xr-x 2 oracle   oinstall 4.0K Nov 13 16:18 hsperfdata_oracle
-rw-rw-rw- 1 testuser testuser    0 Dec  9 11:17 myfile
drwx------ 2 testuser testuser 4.0K Dec  9 11:23 tmpdir
In das Verzeichnis tmpdir kommt man so einfach nicht rein - denn es gehört dem Unix-Nutzer testuser und nur er selbst kann es lesen, schreiben oder hineinwechseln.
SQL> select os_command.exec_clob('/bin/ls -ltrh /tmp/tmpdir') from dual;

OS_COMMAND.EXEC_CLOB('/BIN/LS-LTRH/TMP/TMPDIR')
----------------------------------------------------------------------------
/bin/ls: /tmp/tmpdir: Permission denied
Für Java in der Datenbank steht die Prozedur SET_RUNTIME_EXEC_CREDENTIALS im Package DBMS_JAVA bereit. Auch hier wird ein Username/Password-Paar hinterlegt. Allerdings werden diese nicht, wie bei DBMS_CREDENTIAL, unter einem Namen abgelegt, sondern mit dem Datenbankuser verknüpft - und diese Verknüpfung wird dauerhaft gespeichert. Folgerichtig kann auch nur SYS diese Mappings einrichten. Der folgende Aufruf bewirkt also, dass alle mit Java ausgeführten Betriebssystem-Kommandos des Datenbankusers SCOTT als Unix-User testuser ausgeführt werden. Zuzätzlich braucht der User SCOTT natürlich noch Java-Privilegien, um überhaupt ein Betriebssystem-Kommando ausführen zu können. Beides muss als SYS durchgeführt werden.

begin
  dbms_java.set_runtime_exec_credentials(
    dbuser => 'SCOTT',
    osuser => 'testuser',
    ospass => 'oracle'
  );
end;
/

begin
  dbms_java.grant_permission(
    grantee           => 'SCOTT',
    permission_type   => 'SYS:java.io.FilePermission', 
    permission_name   => '/bin/ls',
    permission_action => 'execute'
  );
end;
/
Nun kann der User SCOTT mit Java in der Datenbank das Betriebssystem-Kommando /bin/ls ausführen. Dieser Aufruf findet dann als Betriebssystem-User testuser statt - ein Listing des auf Unix-Ebene geschützten Verzeichnisses klappt nun also.
SQL> select os_command.exec_clob('/bin/ls -ltrh /tmp/tmpdir') from dual

OS_COMMAND.EXEC_CLOB('/BIN/LS-LTRH/TMP/TMPDIR')
----------------------------------------------------------------------------
total 0
-rw-rw-r-- 1 testuser testuser 0 Dec  9 11:24 f3
-rw-rw-r-- 1 testuser testuser 0 Dec  9 11:24 f2
-rw-rw-r-- 1 testuser testuser 0 Dec  9 11:24 f1

Wie bereits erwähnt, ist das Mapping zwischen dem Datenbankuser SCOTT und dem Betriebsystem-User testuser nun persistent gespeichert. Also auch nach dem Aus- und erneuten Einloggen des Users SCOTT finden dessen Betriebssystem-Aufrufe als Unix-User testuser statt. Das ist ein wesentlicher Unterschied zwischen Java in der Datenbank und DBMS_SCHEDULER - letzterer verwendet, wie oben beschrieben, benamte Credentials, die von einem Datenbankuser unabhängig sind.
Dabei möchte ich es für dieses Mal belassen; ich hoffe, das dieses Posting etwas Licht ins Thema "Betriebssystem-Aufrufe mit der Datenbank und was ist der dabei verwendete Unix-User" bringen konnte.
This blog posting will be about "executing operating system commands" from within the Oracle database. Generally, there are two approaches for this.
  • You can use the built-in package DBMS_SCHEDULER to create a job which launches an operating system executable or a shell script. DBMS_SCHEDULER is built-in, available in all database editions (including XE), so there are no special prerequisites. A disadvantage is, that you cannot access STDIN, STDOUT or STDERR - so there is no "handle" for you to get the console output of your operating system command.
  • As an alternative, you can use the package for filesystem access and operating system commands, which is based on the Java VM inside the database. It allows to do those things, the built-in packages cannot do - like accessing console input and output or retrieving folder contents as a virtual table.
In both variants, the actual operating system call is being executed as the user, which also runs the Oracle processes. DBMS_SCHEDULER allows changing this for a longer time, but this is widely unknown. With the Java-based approach, it was impossible to change the operating system user in the past - but the latest database versions 11.2.0.4 and 12.1 allow this as a new feature. In this blog posting I'll show, how to execute an operating system command as a different user - based on the most current 12.1 release of the Oracle database.

Change the operating system user for the DBMS_SCHEDULER approach

Beginning with version 12.1, the Oracle database contains the new package DBMS_CREDENTIAL. Before Oracle 12.1, the DBMS_CREDENTIAL functions and procedures were part of DBMS_SCHEDULER itself (deprecated with 12.1). With the CREATE_CREDENTIAL procedure, a new credential (a username/password pair) is being created in the database. A database user needs the CREATE CREDENTIAL system privilege for this. The following PL/SQL call creates a credential named CRED_TESTUSER containing username and password for the Unix-User testuser.
begin
  dbms_credential.create_credential(
    credential_name => 'CRED_TESTUSER',
    username        => 'testuser',
    password        => 'oracle', 
    database_role   => null,
    enabled         => true
  );
end;
/
This credential can then be utilized with DBMS_SCHEDULER. But in order to create a job executing an operating system call, the system privilege CREATE EXTERNAL JOB is needed. The simple CREATE JOB is not sufficient. The following PL/SQL code creates the scheduler job JOB_TOUCH_FILE which executes some shell code - as the user testuser.
begin
  dbms_scheduler.create_job(
    job_name        => 'JOB_TOUCH_FILE',
    job_type        => 'EXTERNAL_SCRIPT',
    job_action      => '/bin/touch /tmp/myfile',
    credential_name => 'CRED_TESTUSER',
    auto_drop       => true,
    enabled         => true,
    start_date      => systimestamp 
  );
end;
/
This job starts immediately and executes the touch command. If you look into the /tmp directory on your database server, there should be one new file myfile - owned by testuser. It worked.
$ ls -ltrh

total 28K
:
srwxr-xr-x 1 oracle   oinstall    0 Nov 10 13:42 mapping-oracle
drwx------ 2 oracle   oinstall 4.0K Nov 10 13:42 orbit-oracle
drwxr-xr-x 2 oracle   oinstall 4.0K Nov 13 16:18 hsperfdata_oracle
-rw-rw-rw- 1 testuser testuser    0 Dec  9 11:17 myfile

Change the operating system user for calls done with the database JVM

I'll show the java example based on the above mentioned package for filesystem access and operating system commands. Within that package, OS_COMMAND allows to launch an executable. As already mentioned, this package allows to access STDOUT, STDIN and STDERR - the following example with the ls command illustrates this.
SQL> select os_command.exec_clob('/bin/ls -ltrh /tmp') from dual;

OS_COMMAND.EXEC_CLOB('/BIN/LS-LTRH/TMP')
--------------------------------------------------------------------------------
total 32K
drwx------ 2 oracle   oinstall 4.0K Jul 22 15:33 keyring-bGJZmr
drwx------ 2 oracle   oinstall 4.0K Aug 18 14:56 keyring-zSeMy3
drwx------ 2 oracle   oinstall 4.0K Nov 10 13:42 ssh-wUZJXQ1701
drwx------ 2 oracle   oinstall 4.0K Nov 10 13:42 keyring-7dHMKl
srwxr-xr-x 1 oracle   oinstall    0 Nov 10 13:42 mapping-oracle
drwx------ 2 oracle   oinstall 4.0K Nov 10 13:42 orbit-oracle
drwxr-xr-x 2 oracle   oinstall 4.0K Nov 13 16:18 hsperfdata_oracle
-rw-rw-rw- 1 testuser testuser    0 Dec  9 11:17 myfile
drwx------ 2 testuser testuser 4.0K Dec  9 11:23 tmpdir
As you can see, there is the directory tmpdir - which is owned by testuser and which other Unix users cannot access. By default, an operating system command executed via OS_COMMAND, also cannot access it (it's being run as the owner of the Oracle database processes).
SQL> select os_command.exec_clob('/bin/ls -ltrh /tmp/tmpdir') from dual;

OS_COMMAND.EXEC_CLOB('/BIN/LS-LTRH/TMP/TMPDIR')
----------------------------------------------------------------------------
/bin/ls: /tmp/tmpdir: Permission denied
To change the user, as which the external calls are being made, there is the SET_RUNTIME_EXEC_CREDENTIALS procedure within the DBMS_JAVA package. It also allows to store a username/password pair. But, as opposed to DBMS_CREDENTIAL, this will be linked to the database user and not be stored under its own name. Therefore, only a DBA (SYS) can use that procedure. The following PL/SQL code does two things: First, it maps the database user SCOTT to the Unix user testuser - so all external calls, done by SCOTT with Java in the database, are being executed as testuser. Second, SCOTT needs the privilege to execute an external call at all - the call to DBMS_JAVA.GRANT_PERMISSION does this. Both calls have to be executed as DBA (as SYS).

begin
  dbms_java.set_runtime_exec_credentials(
    dbuser => 'SCOTT',
    osuser => 'testuser',
    ospass => 'oracle'
  );
end;
/

begin
  dbms_java.grant_permission(
    grantee           => 'SCOTT',
    permission_type   => 'SYS:java.io.FilePermission', 
    permission_name   => '/bin/ls',
    permission_action => 'execute'
  );
end;
/
After that, we can use OS_COMMAND as SCOTT to execute the ls command on the protected Unix directory. And now, since we are actually executing as testuser, it works.
SQL> select os_command.exec_clob('/bin/ls -ltrh /tmp/tmpdir') from dual

OS_COMMAND.EXEC_CLOB('/BIN/LS-LTRH/TMP/TMPDIR')
----------------------------------------------------------------------------
total 0
-rw-rw-r-- 1 testuser testuser 0 Dec  9 11:24 f3
-rw-rw-r-- 1 testuser testuser 0 Dec  9 11:24 f2
-rw-rw-r-- 1 testuser testuser 0 Dec  9 11:24 f1

As already pointed out, the mapping between SCOTT and the Unix credentials for testuser is being persited in the database. It will remain even when the session end. So one database user can have only one Unix credential, for external calls by the Java VM, at the same time. That is an important difference between the Java based approach and DBMS_SCHEDULER.

Beliebte Postings