Relativity Server with LDAP

Manfred · May 7, 2015, 12:33pm

Hi

I try to get the Autentifikation working with OpenLDAP.
Did also read thsi:
http://wiki.remobjects.com/wiki/Relativity_Server_Concepts

In the Moment i did the Setup in the Relativity Server Admin Tool
Changend under Domain > mydomain > Login > LdapLoginProvider
Did also all the Settings Host-name …

On the Client-side i did not change anyting. It was working with Static Login Provider.
I always get the Error: Session could not be found.

I do not see any entry in Syslog. Is there a separate Logfile?
In the End i would like to be able to authenticate and set the Security roles via LDAP

Would be glad for any Tips or Tutorials… or Samples.
PS: The Relativity Server runs on Ubuntu

antonk · May 7, 2015, 2:20pm

Hello

There is no need to change anything in the client app when Login Provider is changed on the Relativity Server side.

Please check that you have set all login provider properties. F.e. for ApacheDS settings would look like

Manfred · May 7, 2015, 4:18pm

Thanks Very much.
Shalom
Manfred

antonk · May 8, 2015, 11:20am

As a follow-up

I’ll create a simple LDAP settings test utility over the weekend to simplify the setup process.

Manfred · May 8, 2015, 12:45pm

That sounds interesting.
For me the sample config ( the Screenshot you posted ) was relay helpful.

If you could add a Screenshot of phpldapadmin ( like my Sample ) in addition to that, others maybe find this also usefully.

Interesting would also be how User-Roles ( Different Access Types ) can be managed with LDAP Groups.
Shalom
Manfred

antonk · May 11, 2015, 12:19pm

So, the LDAP test tool is available here: https://github.com/Phrynohyas/ldap-connect/releases

There is also a set of sources. Authentication process implemented in the https://github.com/Phrynohyas/ldap-connect/blob/master/Source/ldap-connect/LdapConnection.cs is actually close enough to the one actually used by the Relativity Server.

As for the groups:

One defines LDAP groups
User entry can be added to one or more groups
Group names are used as security group names when the user is authenticated in the Relativity Server

Manfred · May 12, 2015, 1:30pm

Did check this, it looks to me that the Client in this case has Access to the LDAP and the Client-Software sets the rights…

If for me i would like to do this on the Server on not on the Client.
In my Database:
Every Dataentry has a Group_Field C_NO ( eg I501232 ) and i would like to assign a Group or mor a Soubgroup in LDAP with this C_NO ( eg I501232 ). If the User is assigned in this Subgroup or a Group above that he would get Access to that Dataentry.

Hope this does not sound to Complicated.
I did try to make a Testsetup that looks like this:

The Entry could look like this:

In this case every User who belongs to I47710 or AREA_2 or ZONE_2 or CH and REG_3 would get Access…
Realy hope to get this running…

antonk · May 12, 2015, 2:32pm

Let’s split this into two parts. The 1st one is to authenticate user and to get his session properties filled. That’s the task of the LDAP Login Provider.

Given it is configured properly once authenticated user has a pre-filled session variable ‘SecurityRoles’.

And here comes the 2nd part - to filter data access based on the SecurityRoles value. I’ll write a separate post about this in a few.

antonk · May 12, 2015, 5:13pm

So, the next part.
Unfortunately it won’t work exactly in this form (ie some magic filters data access based on LDAP security groups tree). Main issue here is the access tree. It would be much easier in case of a limited group where user belongs to one and only one group.

Important note: If you also plan to expose additional service methods in your server you’ll need to implement a custom Data Abstract server.

Yet we still have a requirement to handle data access based on a tree of access rights. Here is how I would implement this.

The text below looks complicated and actually it is complex, as well as the problem it solves.

*Add another Domain to Relativity, working with the same target database.

*Add there a couple of tables:

Security group names:
( ID, NAME )
Security group tree (4 levels of nesting):
( ID, P0, P1, P2, P3 )
Here P0-P3 are IDs of the parent entities
Table to assign users to security group:
( USER_ID, GROUP_ID )

In this case it is possible to select all security groups the user belongs to (including ones deeper in the tree) using a query like

SELECT DISTINCT group_id FROM GROUP_TREE WHERE
   id = :user_id
OR p0 = :user_id
OR p1 = :user_id
OR p2 = :user_id
OR p3 = :user_id

*Write some small app to manage users and their groups

*Modify Schema definitions for tables that have to be filtered:

Set their Statement type to SQL, let Schema Modeler to auto-generate that SQL for you and then modify it from something like

SELECT
FieldA,
FieldB
FROM
Table
WHERE
{WHERE}

to

SELECT
  FieldA,
  FieldB
FROM
  Table
WHERE
  {WHERE}
AND {filter}

The next task is to properly form the {filter} string. Luckily user session already contains LDAP data associated with the user. So all we need to do is to handle the ‘filter’ macro using Scripting:

// Called when an unknown macro is used from within an sql statement.

function onUnknownSqlMacroIdentifier(name)
{
 if (name === 'filter')
  return "filter_field IN (SELECT DISTINCT group_id FROM GROUP_TREE WHERE
   id = '" + session['Login.sn'] + "' OR p0 = '" + ....;
}

IOW we add a filter SQL statement to the table data request statement at runtime.

Manfred · May 13, 2015, 9:04am

Hi antonk
Thank you very much for your extensively Tip.
In this way i would have to manage 2 User List’s.
My LDAP Setup is still fresh, so it looks maybe more efficient to adapt the LDAP to the needs of Relativity Server.
I think about your Idea:

It would be much easier in case of a limited group where user belongs to one and only one group.

I keep the Tree in LDAP and assign 1 Group per User.
In the Relativity Server ( like you suggested ) i could create Group-Role Tables and assign to Grouprolle in the Relativity Server.
Almost the same as you suggested with the difference the only the Groups are Managed in the Relativity Server and not the user…

Will try to adapt your Idea.

Shalom
Manfred

Manfred · May 15, 2015, 1:58pm

Struggling with the way of getting the Name of a LDAP Group not the gidNumber.
I have now Tables like you Mentioned and with this SQL i would get any C_NO witch Groupd Members of CH are suposed to see. Until now i did not find a way of getting the LDAP Group name inset of the gidNumber.

SELECT DISTINCT CAMP_NO FROM RPL$GROUPS_RIGHTS WHERE GROUP_NO = ‘CH’

So i made this in Business Rouls an Server:

function onUnknownSqlMacroIdentifier(name)
{
 if (name === 'filter')
  return "filter_field IN (SELECT DISTINCT CAMP_NO FROM RPL$GROUPS_RIGHTS WHERE
   GROUP_NO = '" + session['Login.group-name']")';
}

If i do this:

function onUnknownSqlMacroIdentifier(name)
{
 if (name === 'filter')
  return "filter_field IN (SELECT DISTINCT CAMP_NO FROM RPL$GROUPS_RIGHTS WHERE
   GROUP_NO = 'CH' )";
}

I get the Error Message Unknown colum ‘Filter’ in ‘where clause’

antonk · May 15, 2015, 3:14pm

Hmm

session['Login.group-name']

Does your LDAP setup expose the ‘group-name’ attribute on user entry?

Re error - could you show what’s in the SQL statement of the table that fails?

TIA

Manfred · May 15, 2015, 3:22pm

If i call this SQL in phpMyAdmin

SELECT DISTINCT CAMP_NO FROM RPL$GROUPS_RIGHTS WHERE
   GROUP_NO = 'CH'

I get a List of CAMP_NO like this:

Every Dataentry in the Database has a C_NO Filed so i can filter with that:

Does your LDAP setup expose the ‘group-name’ attribute on user entry?

That is still a Problem… i dont now that in he moment, i am trying to figure this out. ( OPENLDAP )
Beqause of that i just tryed to do this as Test:

function onUnknownSqlMacroIdentifier(name)
{
 if (name === 'filter')
  return "filter_field IN (SELECT DISTINCT CAMP_NO FROM RPL$GROUPS_RIGHTS WHERE
   GROUP_NO = 'CH' )";
}

But i got the Error message, mentioned above.

Manfred · May 15, 2015, 3:48pm

It is defined like this:

SELECT 	
	`ID`, `C_NO`, `U_NO`, 
	`GIDEON_NO`, `AUX_NO`, `TITLE_G`, 
	`SALUT`, `FN_G`, `FN_G_SEC`, 
	`TITLE_A`, `FN_A`, `FN_A_SEC`, 
	`F_NAME`, `STR`, `POBOX`, 
	`PLZ`, `CITY`, `POS_LAT`, 
	`POS_LNG`, `FIRMA`, `JOB`, 
	`TELB`, `TELP`, `TELM_G`, 
	`TELM_A`, `FAXP`, `FAXB`, 
	`C_C`, `C_C_CITY`, `B_DAY_G`, 
	`EMAIL_G`, `CERT_G`, `LTM_G`, 
	`G_ED`, `CHAIRMEN_G`, `CABINET_G`, 
	`CABINET_G_S`, `NAT_C_G`, `NAT_C_G_S`, 
	`CS_S_T`, `CS_S_T_DAT`, `CS_LANG`, 
	`CS_S_T_AUX`, `CS_S_T_DAT_AUX`, `CS_LANG_AUX`, 
	`NMDM_G`, `NMDM_A`, `PAB_G`, 
	`PAB_A`, `B_DAY_A`, `EMAIL_A`, 
	`CERT_A`, `LTM_A`, `A_ED`, 
	`CHAIRMEN_A`, `CABINET_A`, `CABINET_A_S`, 
	`NAT_C_A`, `NAT_C_A_S`, `NOTE`, 
	`CRSP`, `INFO`, `OPT`, 
	`L_UPDATE`, `STAT_UPDATE`, `STAT_DEL`, 
	`RPL$LOCAL` 
FROM 
	`MEMBER` 
WHERE 
	{WHERE} 
AND {$filter$}

now with the AND {$filter$} i get the Error an exception was raised on the Server [1:]: Syntax Error

The Script is still ( for the testing only )
function onUnknownSqlMacroIdentifier(name)

{
 if (name === 'filter')
  return "filter_field IN (SELECT DISTINCT CAMP_NO FROM RPL$GROUPS_RIGHTS WHERE
   GROUP_NO = 'CH' )";
}

antonk · May 15, 2015, 3:55pm

So now what is sent to the DB server is

SELECT 	
	`ID`, `C_NO`, `U_NO`, 
	`GIDEON_NO`, `AUX_NO`, `TITLE_G`, 
	`SALUT`, `FN_G`, `FN_G_SEC`, 
	`TITLE_A`, `FN_A`, `FN_A_SEC`, 
	`F_NAME`, `STR`, `POBOX`, 
	`PLZ`, `CITY`, `POS_LAT`, 
	`POS_LNG`, `FIRMA`, `JOB`, 
	`TELB`, `TELP`, `TELM_G`, 
	`TELM_A`, `FAXP`, `FAXB`, 
	`C_C`, `C_C_CITY`, `B_DAY_G`, 
	`EMAIL_G`, `CERT_G`, `LTM_G`, 
	`G_ED`, `CHAIRMEN_G`, `CABINET_G`, 
	`CABINET_G_S`, `NAT_C_G`, `NAT_C_G_S`, 
	`CS_S_T`, `CS_S_T_DAT`, `CS_LANG`, 
	`CS_S_T_AUX`, `CS_S_T_DAT_AUX`, `CS_LANG_AUX`, 
	`NMDM_G`, `NMDM_A`, `PAB_G`, 
	`PAB_A`, `B_DAY_A`, `EMAIL_A`, 
	`CERT_A`, `LTM_A`, `A_ED`, 
	`CHAIRMEN_A`, `CABINET_A`, `CABINET_A_S`, 
	`NAT_C_A`, `NAT_C_A_S`, `NOTE`, 
	`CRSP`, `INFO`, `OPT`, 
	`L_UPDATE`, `STAT_UPDATE`, `STAT_DEL`, 
	`RPL$LOCAL` 
FROM 
	`MEMBER` 
WHERE 
	(1 = 1)
AND filter_field IN (SELECT DISTINCT CAMP_NO FROM RPL$GROUPS_RIGHTS WHERE
   GROUP_NO = 'CH' )

Could you run this this SQL statement directly using any DB management tool (like phpMyAdmin) and find out what’s wrong with it (maybe wrong quote symbols are used)?

Manfred · May 15, 2015, 4:04pm

I get the Error

#1054 - Unknown column ‘filter_field’ in ‘IN/ALL/ANY subquery’
Without the filter_field IN
like this:
AND (SELECT DISTINCT CAMP_NO FROM RPL$GROUPS_RIGHTS WHERE
GROUP_NO = ‘CH’ )

I get this Error:

1242 - Subquery returns more than 1 row

antonk · May 15, 2015, 4:48pm

So even on DB level you table doesn’t have the ‘filter_field’ defined. This should be the field that contains group id or other stuff you need to filter upon.

Manfred · May 18, 2015, 1:48pm

Sorry this was my mistake…

SELECT 	
	`ID`, `C_NO`, `U_NO`, 
	`GIDEON_NO`, `AUX_NO`, `TITLE_G`, 
	`SALUT`, `FN_G`, `FN_G_SEC`, 
	`TITLE_A`, `FN_A`, `FN_A_SEC`, 
	`F_NAME`, `STR`, `POBOX`, 
	`PLZ`, `CITY`, `POS_LAT`, 
	`POS_LNG`, `FIRMA`, `JOB`, 
	`TELB`, `TELP`, `TELM_G`, 
	`TELM_A`, `FAXP`, `FAXB`, 
	`C_C`, `C_C_CITY`, `B_DAY_G`, 
	`EMAIL_G`, `CERT_G`, `LTM_G`, 
	`G_ED`, `CHAIRMEN_G`, `CABINET_G`, 
	`CABINET_G_S`, `NAT_C_G`, `NAT_C_G_S`, 
	`CS_S_T`, `CS_S_T_DAT`, `CS_LANG`, 
	`CS_S_T_AUX`, `CS_S_T_DAT_AUX`, `CS_LANG_AUX`, 
	`NMDM_G`, `NMDM_A`, `PAB_G`, 
	`PAB_A`, `B_DAY_A`, `EMAIL_A`, 
	`CERT_A`, `LTM_A`, `A_ED`, 
	`CHAIRMEN_A`, `CABINET_A`, `CABINET_A_S`, 
	`NAT_C_A`, `NAT_C_A_S`, `NOTE`, 
	`CRSP`, `INFO`, `OPT`, 
	`L_UPDATE`, `STAT_UPDATE`, `STAT_DEL`, 
	`RPL$LOCAL` 
FROM 
	`MEMBER` 
WHERE 
	(1 = 1)
AND C_NO IN (SELECT DISTINCT CAMP_NO FROM RPL$GROUPS_RIGHTS WHERE
   GROUP_NO = 'CH' )

**AND C_NO IN** (SELECT DISTINCT CAMP_NO FROM RPL$GROUPS_RIGHTS WHERE
   GROUP_NO = 'CH' )

The filter Field is C_NO with this SQL in phpMyAdmin the correct filtered result but in my App i get this:

antonk · May 18, 2015, 3:50pm

This error means that something in the script is not actually a valid JavaScript. Could you send me your .daSchema file via support@ so I’ll be able to check its scripts?

Thanks in advance

Manfred · May 19, 2015, 12:18pm

Ok finally got it.
If someone else has this Problem.
Here is my solution.

Make 2 Tables:

Then use this Script:

function onUnknownSqlMacroIdentifier(name)
{
//	log('sn = ' + session['Login.sn'] + '');
//	log('uidNumber = ' + session['Login.uidNumber'] + '');
//	log('cn =' + session['Login.cn'] + '');
//	log('gidNumber = ' + session['Login.gidNumber'] + '');
	
	if (name === 'filter')
		return "C_NO IN (SELECT DISTINCT CAMP_NO FROM RPL$GROUPS_RIGHTS WHERE
				 GROUP_NO = (SELECT GROUP_NO FROM RPL$GROUPS WHERE `gidNumber` = '" + session['Login.gidNumber'] + "') )";
}

Shalom
Manfred

PS: Thanks again for the Support Crew