Secure Application Roles

Um Berechtigungen zu verwalten, stellt die Oracle Datenbank das Konzept der Rollen zur Verfügung. Über Rollen lassen sich Berechtigungen in Gruppen zusammenfassen. Eine Rolle kann dabei beliebige System- oder Objektprivilegien beinhalten oder auch weitere Rollen. Eine Rolle wird dann einem Benutzer zugeordnet, der dadurch alle in der Rolle enthaltenen Privilegien erhält. Änderungen an der Rolle wirken sich entsprechend auf die verfügbaren Rechte der Nutzer aus, die diese Rolle zugeordnet haben. Normalerweise ist eine zugeordnete Rolle sofort nach der Anmeldung des Nutzers aktiv, dann ist es eine "default role". Schließt man Rollen aus den Default Roles aus, dann muss der Nutzer diese Rollen explizit durch SET ROLE aktivieren, um die entsprechenden Berichtigungen zu erlangen. Diese Aktivierung kann man zusätzlich über verschiedene Methoden einschränken. 

Die einfachste Variante, die Rechte einer Rolle nicht direkt zu aktivieren, ist, die Rolle von den Default Roles auszuschließen. Dazu werden nach dem Granten der Rolle die Default Roles neu definiert. Für dieses und die folgenden Beispiele wird zuerst einmal ein Benutzer angelegt, der Leserechte auf die EMPLOYEES Tabelle im HR-Schema erhalten soll. 

[oracle@db23ctest ~]$ sql / as sysdba


SQLcl: Release 23.2 Production on Fri Jan 19 20:45:15 2024

Copyright (c) 1982, 2024, Oracle.  All rights reserved.

Connected to:
Oracle Database 23c EE High Perf Release 23.0.0.0.0 - Production
Version 23.3.0.23.09

SYS[db23c_pdb1]> create user hr_user identified by "&pwd" quota unlimited on users;
Enter value for pwd: 

User HR_USER created.

SYS[db23c_pdb1]> grant create session to hr_user;

Grant succeeded.

SYS[db23c_pdb1]> create role hr_role ;

Role HR_ROLE created.

SYS[db23c_pdb1]>  grant select any table, insert any table, update any table, delete any table on schema hr to hr_role;

Grant succeeded.

SYS[db23c_pdb1]> grant hr_role to hr_user;

Grant succeeded.

SYS[db23c_pdb1]> alter user hr_user default role none;

User HR_USER altered.

 Der letzte Befehl definiert die Rollen, die nach der Anmeldung des HR_USER aktiv sein sollen, in diesem Fall eben gar keine. Mal schauen, wie sich das nun verhält, wenn sich der HR_USER anmeldet.

SYS[db23c_pdb1]> conn hr_user@db23ctest/db23c_pdb1.snrsbctl.vcnrsbctl.oraclevcn.com
Password? (**********?) **********
Connected.
HR_USER[db23ctest/db23c_pdb1]> select * from session_roles;

no rows selected

HR_USER[db23ctest/db23c_pdb1]> select count(*) from hr.employees;

Error starting at line : 1 in command -
select count(*) from hr.employees
Error at Command Line : 1 Column : 25
Error report -
SQL Error: ORA-00942: table or view does not exist
Help: docs.oracle.com/error-help/db/ora-00942/
00942. 00000 -  "table or view does not exist"
*Cause:
*Action:

Der Nutzer kann sich anmelden, aber nicht die Tabelle EMPLOYEES abfragen, weil die Rolle HR_ROLE und damit die darin definierten Berechtigungen noch nicht aktiv sind. Also weiter geht's:

HR_USER[db23ctest/db23c_pdb1]> set role hr_role;

Role HR_ROLE succeeded.

HR_USER[db23ctest/db23c_pdb1]> select count(*) from hr.employees;

   COUNT(*)
___________
        107

Nachdem die Rolle explizit aktiviert wurde, kann nun auch die Tabelle EMPLOYEES erfolgreich selektiert werden. Wie findet man aber heraus, welche Rollen man überhaupt gegrantet hat, um sie dann auch aktivieren zu können?

HR_USER @ DB23C:DB23C_PDB1:>select granted_role, default_role from user_role_privs;

GRANTED_ROLE    DEFAULT_ROLE
_______________ _______________
HR_ROLE         NO

1 row selected.

So weit, so gut. Aber möglicherweise möchte man die Rolle noch etwas absichern. Dazu kann man für die Aktivierung der Rolle ein Passwort definieren. 

Um eine Rolle mit einem Passwort zu schützen, muss die Rolle nicht extra neu angelegt werden. Man kann auch durch Ändern der Rolle ein Passwort für deren Aktivierung setzen.

SYSTEM @ DB23C:DB23C_PDB1:>alter role hr_role identified by "S3cr3T##";

Role HR_ROLE altered.

Wenn nun der HR_USER versucht, die Rolle wie oben zu aktivieren, dann passiert folgendes:

HR_USER[db23ctest/db23c_pdb1]> select * from session_roles;

no rows selected

HR_USER[db23ctest/db23c_pdb1]> set role hr_role;

Error starting at line : 1 in command -
set role hr_role
Error report -
ORA-01979: missing or invalid password for role "HR_ROLE"
Help: docs.oracle.com/error-help/db/ora-01979/
01979. 00000 -  "missing or invalid password for role '%s'"
*Cause:    An attempt was made to enable a role without giving
           the proper password.
*Action:   Use the IDENTIFIED BY clause in SET ROLE to specify
           the correct password.

Die Datenbank vermisst das erforderliche Passwort zur Aktivierung und gibt eine entsprechende Fehlermeldung. Nebenbei, man sieht hier auch ein nettes Feature des SQLcl, es zeigt nicht nur die Fehlermeldung an, sondern auch gleich Ursache und die möglichen Maßnahmen, analog zum Tool "oerr". Folgt man dem Hinweis und gibt das Passwort beim Aktivieren der Rolle mit an, klappt dann auch wieder der Zugriff auf die EMPLOYEES Tabelle.

HR_USER[db23ctest/db23c_pdb1]> set role hr_role identified by "S3cr3T##";

Role HR_ROLE succeeded.

HR_USER[db23ctest/db23c_pdb1]> select * from session_roles;

ROLE
__________
HR_ROLE

1 row selected.

HR_USER[db23ctest/db23c_pdb1]> select count(*) from hr.employees;

   COUNT(*)
___________
        107

1 row selected.

Das ist schon mal eine recht gute Absicherung, aber es geht noch besser.

Statt der Absicherung der Rolle durch ein Passwort kann eine Rolle auch durch ein Stück PL/SQL Code abgesichert werden. Dann lässt sich die Rolle nicht mehr via SET ROLE aktivieren, sondern nur noch über den Aufruf der PL/SQL Prozedur. Innerhalb dieser Prozedur kann man dann alle möglichen Prüfungen durchführen, um die Rolle nur unter bestimmten Bedingungen zu aktivieren. Dazu muss man zuerst die Prozedur mit der gewünschten Funktionalität erstellen. Im Beispiel wird der Zugang zur Rolle auf ein bestimmes Netzerwerksegment beschränkt, sowie die Aktivierung nur zwischen 8:00 und 17:59 erlaubt. Außerdem braucht der HR_USER Ausführungsrechte auf diese Prozedur, um die Rolle aktivieren zu können.

SYS[db23c_pdb1]> create or replace procedure hr.prc_check_hr_role
  2  authid current_user
  3  as
  4  begin
  5    if (sys_context ('userenv','ip_address') like '10.0.30%'
  6    and to_char (sysdate, 'hh24') between 8 and 17)
  7    then
  8      execute immediate 'set role hr_role ';
  9    end if;
 10   end;
 11* /

Procedure PRC_CHECK_HR_ROLE compiled

SYS[db23c_pdb1]> grant execute on hr.prc_check_hr_role to hr_user;

Grant succeeded.

Als nächstes wird die Rolle entsprechend geändert und die neu erstellte Prozedur zum Aktivieren der Rolle festgelegt.

SYS[db23c_pdb1]>alter role hr_role identified using hr.prc_check_hr_role;

Role HR_ROLE altered.

Nun kann der HR_USER die Rolle nur noch durch den Aufruf dieser Prozedur aktivieren. 

HR_USER[db23ctest/db23c_pdb1]> select * from session_roles;

no rows selected

HR_USER[db23ctest/db23c_pdb1]> set role hr_role;

Error starting at line : 1 in command -
set role hr_role
Error report -
ORA-28201: An invalid command was used to enable secure application role "HR_ROLE".
Help: docs.oracle.com/error-help/db/ora-28201/
28201. 00000 -  "invalid command to enable secure application role \'%s\'"
*Cause:    An attempt to enable the secure application role outside of the
           scope of the designated package failed.
*Action:   Enable the role using the designated package.

Schnell noch mal prüfen, ob die Bedinungen auch erfüllt sind, dann die Rolle aktivieren.

HR_USER @ DB23C:DB23C_PDB1:>select sys_context ('userenv','ip_address');

SYS_CONTEXT('USERENV','IP_ADDRESS')
______________________________________
10.0.30.110

1 row selected.

HR_USER[db23ctest/db23c_pdb1]> select to_char(sysdate, 'hh24');

TO_CHAR(SYSDATE,'HH24')
__________________________
15

Die in der Prozedur definierten Bedingungen sind also erfüllt und die Rolle sollte sich aktivieren lassen. Nächster Fun Fact, bei Oracle 23c ist der FROM-Clause optional, man braucht also nicht mehr immer "from dual" verwenden, um den Wert einer Funktion zu ermitteln.

HR_USER[db23ctest/db23c_pdb1]> exec hr.prc_check_hr_role;

PL/SQL procedure successfully completed.

HR_USER[db23ctest/db23c_pdb1]> select * from session_roles;

ROLE
__________
HR_ROLE

HR_USER[db23ctest/db23c_pdb1]> select count(*) from hr.employees;

   COUNT(*)
___________
        107

 Wie erwartet, ließ sich die Rolle aktivieren und entsprechend lässt sich dann die Tabelle EMPLOYEES auch wieder selektieren. Was passiert aber, wenn man das später am Tag versucht?

HR_USER[db23ctest/db23c_pdb1]> select to_char(sysdate, 'hh24');

TO_CHAR(SYSDATE,'HH24')
__________________________
21

HR_USER[db23ctest/db23c_pdb1]> select * from session_roles;

no rows selected

HR_USER[db23ctest/db23c_pdb1]> exec hr.prc_check_hr_role;

PL/SQL procedure successfully completed.

HR_USER[db23ctest/db23c_pdb1]> select * from session_roles;

no rows selected

Entsprechend der Bedinungen in der Prozedur wird das SET ROLE Statement uhrzeitbedingt nicht mehr ausgeführt und daher lässt sich die Rolle auch nicht mehr aktivieren. 

Passwortgeschützte Rollen erhöhen die Sicherheit in der Datenbank ein ganzes Stück. Aber gerade die Secure Application Roles bieten eine schöne Möglichkeit, die Verwendung von Rollen feingranular einzuschränken. Weitere Informationen finden sich in der Dokumentation von CREATE ROLE und im Security Guide.