SqlResolver and Schema

Hi,

I’m trying to configure a sqlresolver for DB2, but I don’t see a way of specifying a schema.
Is it possible to specify a schema ?

Hi d2d,

welcome to privacyIDEA!

Users in a DB2? What is this? Customers in a backing site? :wink:

privacyIDEA uses SQLAlchemy, so you need to take a look at SQLAlchemy dialects: https://docs.sqlalchemy.org/en/13/dialects/

An alternative could be to go via ODBC. I think I saw this 6 years ago, s.o. connecting SQLAlchemy via odbc to DB2.

Hi Community: Anyone reading this with some experience on that and the willingness to share?

Connecting to DB2 via the SQLAlchemy dialect ibm_db_sa works just fine. The issue is that it cannot find the users table, because there is no field in privacyIdea sqlresolver where to specify the schema.
Even for a standard supported dialect like mysql, I don’t see a schema option in privacyIdea.

PS: Hardcoding the schema in SQLIdResolver.py fixes the issue

What do You mean by schema? Since privacyIDEA does not know the table setup, there is the Mapping field that defines which column in the specified table maps to which attribute in privacyIDEA.

@plettich I’m referring to Database schema. A database can have multiple schemas. Specifying the table name as “schema.table” doesn’t seem to work (at least for db2)

@d2b honestly we did not encounter this. SQL Resolvers are not used as often as ldap resolvers. Also it looks like other database types are more often used (like mysql, having the schema=database-name) or maybe in some cases there is a default schema.

We could set the schema after the SQLSoup call.

self.db = SQLSoup(self.engine, session=Session)
self.db.schema = self.schema

@d2b If this looks sensible to you, would you mind opening an issue at github? So you will be fine with updates in the future.

1 Like