Need help! combine AD with Oracle

[wbaeck]

Hi

I am a total beginner with LDAP so please be patient with me.

I need to extend user attributes from an Active Directory with data from an Oracle DB.

I'm not able to upload a picture of my AD structure but the DN of one user is distinguishedName: CN=Administrator,CN=Users,DC=mb,DC=local

My DB table structure is as following:

column name	data type	nullable
USERID	VARCHAR2(20 BYTE)	No
DEPARTMENT	VARCHAR2(20 BYTE)	No
ATTR1	VARCHAR2(20 BYTE)	Yes
ATTR2	VARCHAR2(20 BYTE)	Yes

The DB data are as following:

USERID	DEPARTMENT	ATTR1	ATTR2
administrator	dept1	attr1_value	attr2_value

In my test lab I installed a plain AD (mb.local, IP:10.0.4.4), an Oracle server (IP: 10.0.4.40) and a centos server (IP: 10.0.4.41) with latest version of myVirtualDirectory (commit: 5d71c5c).

This is the config I currently have:

server.listener.port=389

#Configure global chains
#server.globalChain=

server.nameSpaces=Root,AD,DB

#Define RootDSE
server.Root.chain=RootDSE
server.Root.nameSpace=
server.Root.weight=0
server.Root.RootDSE.className=net.sourceforge.myvd.inserts.RootDSE
server.Root.RootDSE.config.namingContexts=o=ad|o=db
server.Root.RootDSE.config.supportedControls=2.16.840.1.113730.3.4.18,2.16.840.1.113730.3.4.2,1.3.6.1.4.1.4203.1.10.1,1.2.840.113556.1.4.319,1.2.826.0.1.334810.2.3,1.2.826.0.1.3344810.2.3,1.3.6.1.1.13.2,1.3.6.1.1.13.1,1.3.6.1.1.12
server.Root.RootDSE.config.supportedSaslMechanisms=NONE

#AD insert
server.AD.chain=adinsert
server.AD.nameSpace=o=ad
server.AD.weight=0
server.AD.mapguid.className=net.sourceforge.myvd.inserts.ad.ObjectGuidToString
server.AD.adinsert.className=net.sourceforge.myvd.inserts.ad.ADInsert
server.AD.adinsert.config.host=10.0.4.4
server.AD.adinsert.config.port=389
server.AD.adinsert.config.adbase=CN=Users
server.AD.adinsert.config.remoteBase=DC=mb,DC=local
server.AD.adinsert.config.ignoreRefs=true
#server.AD.adinsert.config.type=ldap
server.AD.adinsert.config.proxyDN=CN=Administrator,CN=Users,DC=mb,DC=local
server.AD.adinsert.config.proxyPass=xxxxxxxxx

# JDBC Insert
server.DB.chain=dbinsert
server.DB.nameSpace=o=db
server.DB.wieght=0
server.DB.dbinsert.className=net.sourceforge.myvd.inserts.jdbc.JdbcInsert
server.DB.dbinsert.config.driver=com.oracle.jdbc.Driver
server.DB.dbinsert.config.url=jdbc:oracle:thin:@10.0.4.40:1521:xe
server.DB.dbinsert.config.user=system
server.DB.dbinsert.config.password=xxxxxxxxx
server.DB.dbinsert.config.rdn=userid
server.DB.dbinsert.config.mapping=userid=cn,abteilung=abteilung,attribute1=attr1,attribute2=attr2
server.DB.dbinsert.config.objectClass=dbPerson
server.DB.dbinsert.config.sql=SELECT * FROM usertab
server.DB.dbinsert.config.useSimple=true

I would expect to see at leased the two sources in myVD but I don't see any of them.

So my question is:

1. What I'm doing wrong?
2. How to combine them based on "userid".
3. Is there a possibility to add access control to it, so AD user1 sees all attributes and AD user2 does not see DB attributes and AD user3 sees only part of the DB attributes?

I am grateful for any help.

Thanks in advance.

[wbaeck]

Finally we are now able to join Active Direcorty with DB - one step further, but still open issues:

#Listen on port 389
server.listener.port=389

#Configure global chains
server.globalChain=LogAllTransactions
server.globalChain.LogAllTransactions.className=net.sourceforge.myvd.inserts.DumpTransaction
server.globalChain.LogAllTransactions.config.logLevel=info
server.globalChain.LogAllTransactions.config.label=Global

#Configure namespaces
server.nameSpaces=Root,DB,AD,Joiner

#Define RootDSE
server.Root.chain=RootDSE
server.Root.nameSpace=
server.Root.weight=0
server.Root.RootDSE.className=net.sourceforge.myvd.inserts.RootDSE
server.Root.RootDSE.config.namingContexts=o=db|o=ad|o=joined

# database
server.DB.chain=DB
server.DB.nameSpace=o=db
server.DB.weight=0
server.DB.DB.className=net.sourceforge.myvd.inserts.jdbc.JdbcInsert
server.DB.DB.config.driver=oracle.jdbc.OracleDriver
server.DB.DB.config.url=jdbc:oracle:thin:@10.0.4.40:1521:xe
server.DB.DB.config.user=system
server.DB.DB.config.password=xxxxxxxx
server.DB.DB.config.rdn=cn
server.DB.DB.config.mapping=cn=userid,abteilung=department,attribute1=attr1,attribute2=attr2
server.DB.DB.config.objectClass=dbPerson
server.DB.DB.config.sql=SELECT * FROM usertab
server.DB.DB.config.useSimple=true

# AD
server.AD.chain=adinsert
server.AD.nameSpace=o=ad
server.AD.weight=0
server.AD.mapguid.className=net.sourceforge.myvd.inserts.ad.ObjectGuidToString
server.AD.adinsert.className=net.sourceforge.myvd.inserts.ad.ADInsert
server.AD.adinsert.config.host=10.0.4.4
server.AD.adinsert.config.port=389
server.AD.adinsert.config.remoteBase=DC=mb,DC=local
server.AD.adinsert.config.proxyDN=CN=Administrator,CN=Users
server.AD.adinsert.config.proxyPass=xxxxxxx
server.AD.adinsert.config.ignoreRefs=true

#The Joiner
server.Joiner.chain=joiner
server.Joiner.nameSpace=o=joined
server.Joiner.weight=0
server.Joiner.joiner.className=net.sourceforge.myvd.inserts.join.Joiner
server.Joiner.joiner.config.primaryNamespace=o=ad
server.Joiner.joiner.config.joinedNamespace=o=db
server.Joiner.joiner.config.joinedAttributes=cn,abteilung,attribute1,attribute2
server.Joiner.joiner.config.joinFilter=(cn=ATTR.cn)

Still not working:

1. Looks like join insert is case sensitive. Is there a way to compare case insensitive?
2. Is there a possibility to add access control to it, so AD user1 sees all attributes and AD user2 does not see DB attributes and AD user3 sees only part of the DB attributes?

I am still happy for any help.

Thanks in advance.

[wbaeck]

Issue 1 - case insensitive join (solved)

This solution is not perfect, but works fine for our use case.

In class JdbcInsert we changed the following in method stringFilter (aroundline 936)

from

filter.append(attribName);
filter.append("=?");

to

filter.append(" lower(");
filter.append(attribName);
filter.append(")=lower(?) ");

Maybe this could be solved with a config setting?

Issue 2 is still unresolved for us

Is there a possibility to add access control to it, so AD user1 sees all attributes and AD user2 does not see DB attributes and AD user3 sees only part of the DB attributes?

[mlbiam]

For issue 1 - we avoid doing this because we don't want to override the databases' index. i'm not an Oracle expert, but I've done this with SQL Server and MySQL/MariaDB where you can define your index to be case insensitive. Is this an option?

For issue 2 - yes, you can use the ACLs insert (look for Access Control in Access Controls in https://www.tremolosecurity.com/docs/tremolosecurity-docs/myvd/1.0.6/myvd.html) . Here's an example - https://github.com/TremoloSecurity/MyVirtualDirectory/blob/master/test/TestServer/testACLs.props

[wbaeck]

@mlbiam thanks for your reply!

issue 1: We are not able to change anything on the Oracle DB so that's why this change needed for us. Would be great to have a setting for this so other users can also solve such issues.

issue 2: Thanks for the examples. Solved now for us.