How to use LDAP to authenticate ?

Report · Nov 17, 2019

Hi,

I am using ColdFusion 2018 and an Oracle 12c Database.

I would like to use LDAP to authenticate my users. As of now the users are being authenticated by their Usernames in an Oracle Database, upon logging-in their roles are checked to see if they have an adequate role and then they are made available different screens depending on their roles. I would like to do the same but within an LDAP authentication method. How can I do this ?

Regards

IQ

Report · Nov 18, 2019

Any updates for me ?

Report · Nov 18, 2019

IQ, to be clear, this is not a forum where you should EXPECT an answer. If folks seeing your question can offer an answer, they will. It may be days before someone (motivated to offer the answer) does reply. Sometimes that's influenced by the nature of the question.

In your case, you could help us by clarifying what you have looked into already for doing that integration. A simple search of:

coldfusion ldap

will find many results. You would more likely get an answer with a direct question, such as "i tried x and got y as a result, but I expected z. what went wrong?"

I will point out that CF offers the CFLDAP tag, and it is also possible to perform authentication (against ldap) in your web server and have that information passed into CF. I'd start with the cfldap tag, if I were you. Let us know if you still have questions.

/Charlie (troubleshooter, carehart. org)

Report · Nov 18, 2019

ok you are right.

So I have tried using the syntax below

in the application.cfm file a single line entry

and code in the program similar to , a detailed code copy is attached.

My problem is thats this LDAP connectivity test worked successfully for my Network Idanda few other colleagues, the only problem or workaround is that the Network password of the user should match the Oracle password. We are using Oracle 10G database and plan on upgrading to Oracle 12c soon.

Is there a way I can avoid doing this, I would like it to be directly able to login using LDAP and then based on roles assigned just display the relevant pages ? So this way even if the ORacle password changes it does not impact as it validates only using LDAP.

<cfparam name="isAuthenticated" default="false">  

<cfparam name="logindomain" default="tw">  

<cfparam name="ldapServer" default="org.ad.city.aa">  

<cfparam name="dcStart" default="DC=city,DC=aa">    

<cfif IsDefined("form.username") AND form.username is not "" AND IsDefined("form.password") AND form.password is not "">      

   <cftry>          

         <cfldap action="QUERY"          

               name="auth"          

               attributes="samAccountName"          

               start="#dcStart#"          

               scope="SUBTREE"          

               maxrows="1"          

               server="#ldapServer#"          

               username="#logindomain#\#form.username#"          

               password="#form.password#">          

         <cfset isAuthenticated="true">             

      <cfcatch type="ANY">              

         <cfset isAuthenticated="no">          

      </cfcatch>      

   </cftry>  

</cfif>    

<cfoutput>      

   <cfif isAuthenticated>          
         <p>You are authenticated:</p> #isauthenticated#      
    #Form.UserName#



<cfset Session.UserName = #Form.UserName#>
<cfset Session.password = #LCase(Form.Password)#>
<cfset Session.back = 0>
     <!---  --->	         <cfif dbtype is 'oracle'>
				<cfquery name="FindRole" datasource="#datasource#" username="#Session.UserName#" password="#Session.password#">
                SELECT * FROM DBA_ROLE_PRIVS WHERE GRANTEE = '#Ucase(form.username)#' AND (GRANTED_ROLE = 'PMD_ADMIN' OR GRANTED_ROLE = 'OPERATOR' OR GRANTED_ROLE = 'MANAGER' OR GRANTED_ROLE = 'VIEWER') 
                </cfquery>
				<cfelseif dbtype is 'sqlserver'>
 				<cfquery name="FindRole" datasource="#datasource#" username=#Session.UserName# password=#Session.password#>
                select name 
				from sysusers 
				where uid in 
				     (select groupuid from sysmembers where memberuid = (select uid from sysusers where LOWER(name) = '#form.username#'))
				</cfquery>
				</cfif>
			
<cfif dbtype is 'oracle'>
	  <CFIF findrole.granted_role is 'MANAGER'>
		<cfset session.role = "manager">
	  <CFELSEIF Session.Username is 'dbo'>
		<cfset session.role = "owner">
	  <CFELSEIF findrole.granted_role is 'OPERATOR'>
		<cfset session.role = "operator">
	  <CFELSEIF findrole.granted_role is 'VIEWER'>
		<cfset session.role = "viewer">
	  <CFELSEIF findrole.granted_role is 'ADMIN'>
		<cfset session.role = "admin">
	  <CFELSE>	  
<script>
alert('Your user account has not been assigned a role, please contact your eAS administrator.');
document.location = 'default.cfm';
</script>
	  </CFIF>
<cfelseif dbtype is 'sqlserver'>

<cfset checkforrole = 0>

<cfloop query="FindRole">
<cfif Find("viewer",name) GT 0>
<cfset Session.role = "viewer">
<cfset checkforrole = 1>
</cfif>
</cfloop>

<cfloop query="FindRole">
<cfif Find("operator",name) GT 0>
<cfset Session.role = "operator">
<cfset checkforrole = 1>
</cfif>
</cfloop>

<cfloop query="FindRole">
<cfif Find("manager",name) GT 0>
<cfset Session.role = "manager">
<cfset checkforrole = 1>
</cfif>
</cfloop>

<cfloop query="FindRole">
<cfif Find("administrator",name) GT 0>
<cfset Session.role = "admin">
<cfset checkforrole = 1>
</cfif>
</cfloop>

<cfloop query="FindRole">
<cfif Find("db_owner",name) GT 0>
<cfset Session.role = "admin">
<cfset checkforrole = 1>
</cfif>
</cfloop>

<cfif checkforrole is 0>
<script>
alert('Your user account has not been assigned a role, please contact your eAS administrator.');
document.location = 'default.cfm';
</script>
</cfif>

</cfif>

<CFLOGINUSER name="#Session.UserName#" password="#Session.Password#" roles="">

<cfif dbtype is 'oracle'>
 <cfquery name="LookUp" datasource="#datasource#" username=#Form.UserName# password=#Form.password#>
  SELECT max(ipn) FROM dbo.look_up 
 </cfquery>
<cfelseif dbtype is 'sqlserver'>
 <cfquery name="LookUp" datasource="#datasource#" username="#Form.UserName#" password="#Form.password#">
  SELECT * FROM dbo.look_up
 </cfquery>
</cfif>
<-- -->
<cftry>
<script>
document.location = "main.cfm"
</script>
 <cfcatch type="any">
        Error: <cfoutput>#cfcatch.message#</cfoutput>
    </cfcatch>
</cftry>
		 

		 
		 
		 
   <cfelse>          
   #isauthenticated#  
      <p>Welcome !      
    
   </cfif>      

<form action="#cgi.script_name#" method="POST">      

   <p>Enter your login and pwd to see if you authenticate      

   <p>Username #logindomain#\<input type="Text" name="username" <cfif (IsDefined("form.username") AND form.username is not "")>value="#form.username#"</cfif>>      

   <br>password<input type="password" name="password" <cfif (IsDefined("form.password") AND form.password is not "")>value="#form.password#"</cfif>>      

   <br><input type="Submit" value="Login" name="">      

</form>  

</cfoutput>

Report · Nov 18, 2019

Someone with more experience in LDAP and Oracle may have a better answer, but looking at that code I would question whether you should be using the USER's username and password in the CFQUERY user and password attributes. That's about connecting TO the DB server, DB and/or table (depending on your security).

I would think instead you would use one username and password (defined in fact in the CF Admin for the datasource, whatever #datasource# is defined as--as you don't show it in that code), and then the select against its sysusers table would then pass in the username to lookup, and you'd use the results of that lookup for the rest of your processing.

But i'm afraid it's all a little convoluted for me to readily say "you should do X". Again, perhaps someone else will have more to say. But as I said originally, there's no guarantee anyone else will answer, or within any amount of hours or days (or weeks). It all depends on the nature of the questions (and answers offered in reply to follow-ups), as to whether anyone feels they can help.

I will point out that there are other support options, from a Facebook CF Programmers group, to the CFML Slack channel, and more. See my listing of those, with links for more, at http://www.cf411.com/cfhelp.

/Charlie (troubleshooter, carehart. org)

Report · Nov 21, 2019

Thanks Bkbk, I will try your code. The dbtype value is defined in application.cfm file, so it comes from there.

Report · Nov 23, 2019

IQ1 wrote:

The dbtype value is defined in application.cfm file, so it comes from there.

In that case, I would assume the variable is for use application-wide. If so, you should redefine the variable (in Application.cfm) as:

<cfset application.dbtype="type_of_DB">

Oh, I would also suggest you consider using Application.cfc instead of the outdated Application.cfm.

Report · Nov 20, 2019

Hi Iq11,

You're on the right track. You will have to change the following code:

<cfif IsDefined("form.username") AND form.username is not "" AND IsDefined("form.password") AND form.password is not "">      
<cftry>          
         <cfldap action="QUERY"          
               name="auth"          
               attributes="samAccountName"          
               start="#dcStart#"          
               scope="SUBTREE"          
               maxrows="1"          
               server="#ldapServer#"          
               username="#logindomain#\#form.username#"          
               password="#form.password#">          
         <cfset isAuthenticated="true">             
      <cfcatch type="ANY">              
         <cfset isAuthenticated="no">          
      </cfcatch>      
   </cftry> 
</cfif>

For two reasons. Firstly, the try-catch is unnecessary. Secondly, you should use the query to verify whether or not the user is authenticated.

<cfif IsDefined("form.username") AND form.username is not "" AND IsDefined("form.password") AND form.password is not "">      
           
 <cfldap action="QUERY"          
       name="auth"          
       attributes="samAccountName"          
       start="#dcStart#"          
       scope="SUBTREE"          
       maxrows="1"          
       server="#ldapServer#"          
       username="#logindomain#\#form.username#"          
       password="#form.password#">   
              
 <cfif auth.recordcount gt 0>
 	  <cfset isAuthenticated=true>
 </cfif>        
           
</cfif>

Report · Nov 20, 2019

Where does the variabnle dbtype come from? I have reviewed your code as much as I can, adding some corrections here and there. The result is:

<cfparam name="isAuthenticated" default="false">  
<cfparam name="logindomain" default="tw">  
<cfparam name="ldapServer" default="org.ad.city.aa">  
<cfparam name="dcStart" default="DC=city,DC=aa">  

<cfif IsDefined("form.username") AND form.username is not "" AND IsDefined("form.password") AND form.password is not "">      
           
 <cfldap action="QUERY"          
       name="auth"          
       attributes="samAccountName"          
       start="#dcStart#"          
       scope="SUBTREE"          
       maxrows="1"          
       server="#ldapServer#"          
       username="#logindomain#\#form.username#"          
       password="#form.password#">   
              
 <cfif auth.recordcount gt 0>
 	  <cfset isAuthenticated=true>
 </cfif>        
           
</cfif>    

    
<cfif isAuthenticated>          
    <p>You are authenticated:</p>      
    <cfoutput>#Form.UserName#</cfoutput>  


	<cfset Session.userName = Form.userName>
	<cfset Session.password = LCase(Form.Password)>
	<cfset Session.back = 0>
	
	<cfif dbtype is 'oracle'>
		<cfquery name="FindRole" datasource="#datasource#" username="#Session.UserName#" password="#Session.password#">
		SELECT * FROM DBA_ROLE_PRIVS WHERE GRANTEE = <cfqueryparam cfsqltype="cf_sql_varchar" value="#uCase(form.username)#"> AND (GRANTED_ROLE = 'PMD_ADMIN' OR GRANTED_ROLE = 'OPERATOR' OR GRANTED_ROLE = 'MANAGER' OR GRANTED_ROLE = 'VIEWER') 
		</cfquery>
	<cfelseif dbtype is 'sqlserver'>
		<cfquery name="FindRole" datasource="#datasource#" username="#Session.UserName#" password="#Session.password#">
		select name 
		from sysusers 
		where uid in 
		     (select groupuid from sysmembers where memberuid = (select uid from sysusers where LOWER(name) = <cfqueryparam cfsqltype="cf_sql_varchar" value="#form.username#">))
		</cfquery>
	</cfif>
			
	<cfif dbtype is 'oracle'>
		  <CFIF findrole.granted_role is 'MANAGER'>
			<cfset session.role = "manager">
		  <CFELSEIF Session.Username is 'dbo'>
			<cfset session.role = "owner">
		  <CFELSEIF findrole.granted_role is 'OPERATOR'>
			<cfset session.role = "operator">
		  <CFELSEIF findrole.granted_role is 'VIEWER'>
			<cfset session.role = "viewer">
		  <CFELSEIF findrole.granted_role is 'ADMIN'>
			<cfset session.role = "admin">
		  <CFELSE>	  
			<script>
			alert('Your user account has not been assigned a role, please contact your eAS administrator.');
			document.location = 'default.cfm';
			</script>
		  </CFIF>
	<cfelseif dbtype is 'sqlserver'>
	
		<cfset checkforrole = 0>
		
		<cfloop query="FindRole">
		<cfif Find("viewer",name) GT 0>
		<cfset Session.role = "viewer">
		<cfset checkforrole = 1>
		</cfif>
		</cfloop>
	
		<cfloop query="FindRole">
		<cfif Find("operator",name) GT 0>
		<cfset Session.role = "operator">
		<cfset checkforrole = 1>
		</cfif>
		</cfloop>
	
		<cfloop query="FindRole">
		<cfif Find("manager",name) GT 0>
		<cfset Session.role = "manager">
		<cfset checkforrole = 1>
		</cfif>
		</cfloop>
	
		<cfloop query="FindRole">
		<cfif Find("administrator",name) GT 0>
		<cfset Session.role = "admin">
		<cfset checkforrole = 1>
		</cfif>
		</cfloop>
	
		<cfloop query="FindRole">
		<cfif Find("db_owner",name) GT 0>
		<cfset Session.role = "admin">
		<cfset checkforrole = 1>
		</cfif>
		</cfloop>
	
		<cfif checkforrole is 0>
		<script>
		alert('Your user account has not been assigned a role, please contact your eAS administrator.');
		document.location = 'default.cfm';
		</script>
		</cfif>
	
	</cfif>

	<CFLOGINUSER name="#Session.UserName#" password="#Session.Password#" roles="">
	
	<cfif dbtype is 'oracle'>
		 <cfquery name="LookUp" datasource="#datasource#" username=#Form.UserName# password=#Form.password#>
		  SELECT max(ipn) FROM dbo.look_up 
		 </cfquery>
	<cfelseif dbtype is 'sqlserver'>
		 <cfquery name="LookUp" datasource="#datasource#" username="#Form.UserName#" password="#Form.password#">
		  SELECT * FROM dbo.look_up
		 </cfquery>
	</cfif>


	<cftry>
		<script>
		document.location = "main.cfm"
		</script>
	<cfcatch type="any">
	    Error: <cfoutput>#cfcatch.message#</cfoutput>
	</cfcatch>
	</cftry>
		 	 
 <cfelse>          
    <!--- User NOT authenticated --->
      <p>Welcome !      
    
 </cfif>      

<cfoutput>
<form action="#cgi.script_name#" method="POST">      

   <p>Enter your login and pwd to see if you authenticate      

   <p>Username #logindomain#\<input type="Text" name="username" <cfif (IsDefined("form.username") AND form.username is not "")>value="#form.username#"</cfif>>      

   <br>password<input type="password" name="password" <cfif (IsDefined("form.password") AND form.password is not "")>value="#form.password#"</cfif>>      

   <br><input type="Submit" value="Login" name="">      

</form>  

</cfoutput>

Report · Nov 24, 2019

Sure Bkbk, I will try your suggestions and let u know, how it goes. Thanks again.

Report · Mar 08, 2020

Hi there, I am reopening this discussion, instead of doing this in the pages. I would like to do this in the ColdFusion Administrator, while defining Database connectivity instead of using Server names , I would like to use LDAP connectivity string , please let me know ,how to do this ?

Thanks

IQ

Report · Mar 08, 2020

I'm pretty sure you can't do this in CF Administrator. CF doesn't support the use of LDAP attributes to connect to Oracle databases. The best you can do is collect the username and password directly from the user, verify them against LDAP, then send them to the database via CFQUERY username and password.

Dave Watts, Eidolon LLC

Report · Mar 09, 2020

Thanks Dave, Do I have to do this piece of code for configuring LDAP on one main page like Applications.cfc or across many pages in CF. Please let me know ?

Report · Mar 09, 2020

If your goal is to get all users to authenticate first before you can run any queries, you could check for variables in Application.cfc/cfm and if those variables don't exist, send them to a login form to collect them.

While we're talking about this, I'll mention that it can be kind of dangerous to design a web application where each user has their own database credentials, because you won't be able to take advantage of any shared queries, query caching, JDBC statement reuse, etc. I guess you have your reasons for doing this, but just be aware that it can be a problem.

Dave Watts, Eidolon LLC

How to use LDAP to authenticate ?

Photos