Database as identity provider

It is shown how a database is used as a 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 users, roles and group tables are already 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 for execution directly in the Karaf console (insert the correct dataSourceName beforehand!)

    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 is created Backend Connection of the type identity_provider is created. The following values must be set there.

Setting (Key) Group Value Description

Module_Name
(module_name)

module

idp-oracle

Assign a meaningful and unique name.

IdentityProvider
(identityProvider)

config

jdbc

The value jdbc so that JAAS DB is used.

IdentityProvider_Configuration
(identityProvider_configuration)

Only BPC version < 4.2.13

config

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

Unter datasource die ID der zu verwendenden Backend Connection vom Typ data_source eintragen.

IdentityProvider_JDBC_DataSource
(identityProvider_jdbc_dataSource)

Only BPC version >= 4.2.13

jdbc

oracle-xe-vpma

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

IdentityProvider_Mappings
(identityProvider_Mappings)

config

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

Hier müssen die Rechte, Rollen und Organisationen hinterlegt werden.

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 the bpcadmin user, for example.

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

Database queries

If a table layout other than the one above is used, adapt the database queries accordingly. These can be found at [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 user via Karaf console

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

Example to create the bpcadmin user via Karaf console

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

Transfer user passwords from JAAS file

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

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

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

Use

See Create, configure and use an identity provider.

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