Database as Identity Provider

This section demonstrates how to use a database as the source for an identity provider.

Prerequisites

  1. The database connection is available as a backend connection of type data_source. The ID of the backend connection component is dataSourceName.

  2. The user, role, and group tables have already been created in the database

    CREATE TABLE BPC_USERS (USERNAME VARCHAR(255) PRIMARY KEY NOT NULL, PASSWORD VARCHAR(255) NOT NULL,FIRSTNAME VARCHAR(255),LASTNAME VARCHAR(255),EMAIL VARCHAR(255));

CREATE TABLE BPC_ROLES (USERNAME VARCHAR(255) NOT NULL, ROLENAME VARCHAR(255) NOT NULL, PRIMARY KEY (USERNAME,ROLENAME));

CREATE TABLE BPC_GROUPS (USERNAME VARCHAR(255) NOT NULL, GROUPNAME VARCHAR(255) NOT NULL, PRIMARY KEY (USERNAME,GROUPNAME));

    or, alternatively, the same instructions for running the query directly in the Karaf console (be sure to enter the correct dataSourceName first!)

    jdbc:execute <dataSourceName> "CREATE TABLE BPC_USERS (USERNAME VARCHAR(255) PRIMARY KEY NOT NULL, PASSWORD VARCHAR(255) NOT NULL,FIRSTNAME VARCHAR(255),LASTNAME VARCHAR(255),EMAIL VARCHAR(255))"

jdbc:execute <dataSourceName> "CREATE TABLE BPC_ROLES (USERNAME VARCHAR(255) NOT NULL, ROLENAME VARCHAR(255) NOT NULL, PRIMARY KEY (USERNAME,ROLENAME))"

jdbc:execute <dataSourceName> "CREATE TABLE BPC_GROUPS (USERNAME VARCHAR(255) NOT NULL, GROUPNAME VARCHAR(255) NOT NULL, PRIMARY KEY (USERNAME,GROUPNAME))"

Configuration (JAAS DB)

A new backend Connection of type identity_provider is created. Set the following values there.

Setting (Key) Group Value Description

Module_Name
(module_name)

module

idp-oracle

Assign a descriptive and unique name.

IdentityProvider
(identityProvider)

config

jdbc

Set the value to jdbc so that JAAS DB is used.

IdentityProvider_Configuration
(identityProvider_configuration)

Only for BPC versions < 4.2.13

config

 
{
  "datasource": "oracle-xe-vpma"
}

Under datasource, enter the ID of the backend Connection of type data_source to be used.

IdentityProvider_JDBC_DataSource
(identityProvider_jdbc_dataSource)

Only for BPC version >= 4.2.13

jdbc

oracle-xe-vpma

At datasource, enter the ID of the backend Connection of type data_source that you want to use.

IdentityProvider_Mappings
(identityProvider_Mappings)

config

 
{
  "organisations": {
    "DEFAULT": {
      "assignedRoles": [],
      "assignedOrganisations": [],
      "assignedRights": []
    }
  },
  "roles": {
    "bpcuser": {
      "assignedRoles": [],
      "assignedRights": [ "loadModule_blank", "loadModule_account", "loadModule_dashboard" ]
    }
  },
  "rights": {}
}

The rights, roles, and organizations must be defined here.

These must then also be created in the Karaf Console. To do this, assign each right to at least ONE user as described below. This is the only way to assign the respective object to a user in the BPC. It makes sense to use, for example, the bpcadmin user for this.

virtimo@bpc()> jaas:role-add user_x role_y
... siehe unten ...

Database Queries

If a table layout other than the one shown above is used, adjust the database queries accordingly. These can be found at <BPC-INSTALL-DIR>/karaf/etc/de.virtimo.bpc.core.auth.jaas.jdbc.queries.cfg.

de.Virtimo.BPC.core.auth.jaas.jdbc.queries.cfg

insert.user = INSERT INTO BPC_USERS VALUES(?,?,?,?,?)
insert.role = INSERT INTO BPC_ROLES VALUES(?,?)
insert.group = INSERT INTO BPC_GROUPS VALUES(?,?)
update.pwd = UPDATE BPC_USERS SET PASSWORD=? WHERE USERNAME=?
update.user = UPDATE BPC_USERS SET FIRSTNAME=?, LASTNAME=?, EMAIL=? WHERE USERNAME=?
query.pwd = SELECT PASSWORD FROM BPC_USERS WHERE USERNAME=?
query.user = SELECT USERNAME, FIRSTNAME, LASTNAME, EMAIL FROM BPC_USERS WHERE USERNAME=?
query.users = SELECT USERNAME, FIRSTNAME, LASTNAME, EMAIL FROM BPC_USERS
query.roles_of_user_or_group = SELECT ROLENAME FROM BPC_ROLES WHERE USERNAME=?
query.roles = SELECT ROLENAME FROM BPC_ROLES
query.groups_of_user = SELECT GROUPNAME FROM BPC_GROUPS WHERE USERNAME=?
query.groups = SELECT GROUPNAME FROM BPC_GROUPS
delete.user = DELETE FROM BPC_USERS WHERE USERNAME=?
delete.role = DELETE FROM BPC_ROLES WHERE USERNAME=? AND ROLENAME=?
delete.roles = DELETE FROM BPC_ROLES WHERE USERNAME=?
delete.group = DELETE FROM BPC_GROUPS WHERE USERNAME=? AND GROUPNAME=?
delete.groups = DELETE FROM BPC_GROUPS WHERE USERNAME=?

Create a user via the Karaf console

This is a JAAS implementation and can be accessed via the Karaf console.

Example of creating the user “ bpcadmin ” via the Karaf console

virtimo@bpc()> jaas:realm-list
Index | Realm Name | Login Module Class Name
-----------------------------------------------------------------------------------
1     | inubit     | de.virtimo.bpc.core.auth.jaas.inubit.InubitLoginModule
2     | jdbc       | de.virtimo.bpc.core.auth.jaas.jdbc.JDBCLoginModule
3     | karaf      | org.apache.karaf.jaas.modules.properties.PropertiesLoginModule
4     | karaf      | org.apache.karaf.jaas.modules.publickey.PublickeyLoginModule
5     | karaf      | org.apache.karaf.jaas.modules.audit.FileAuditLoginModule
6     | karaf      | org.apache.karaf.jaas.modules.audit.LogAuditLoginModule
7     | karaf      | org.apache.karaf.jaas.modules.audit.EventAdminAuditLoginModule

# Nun den Benutzer 'bpcadmin' mit dem Passwort 'bpcadmin' anlegen. Dabei wird das Passwort verschlüsselt (SHA-512, hexadezimal) in der Datenbank abgelegt.
# Diesem die Rolle 'bpcadmin' zuweisen und zur Gruppe 'admingroup' hinzufügen.

virtimo@bpc()> jaas:realm-manage --realm jdbc
# Falls die Selektion über realm nicht funktioniert, über den index selektieren:
virtimo@bpc()> jaas:realm-manage --index 2
virtimo@bpc()> jaas:user-add bpcadmin bpcadmin
virtimo@bpc()> jaas:role-add bpcadmin bpcadmin
virtimo@bpc()> jaas:group-add bpcadmin admingroup
virtimo@bpc()> jaas:update

Import user passwords from a JAAS file

It is also possible to migrate existing passwords from other systems via SQL. This has the advantage that users do not have to set a new password.

For example: If the JAAS file was previously used for login, you can use this Karaf console command to create the users and passwords in the JAAS database:

jdbc:execute datasourcename "insert into bpc_users (username, password) values ('user_x', 'BASE64-Kodiertes-Passwort aus users.properties')"

Usage

See Creating, Configuring, and Using an Identity Provider.

Keywords:
Press Esc to exit full screen
Modal image placeholder
Press Esc to exit full screen