How to use LDAP to authenticate ?
Copy link to clipboard
Copied
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
Copy link to clipboard
Copied
Any updates for me ?
Copy link to clipboard
Copied
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)
Copy link to clipboard
Copied
ok you are right.
So I have tried using the syntax below
in the application.cfm file a single line entry
<CFAPPLICATION NAME="eOPS" SESSIONMANAGEMENT="Yes">
and code in the program similar to , a detailed code copy is attached.
<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>
Copy link to clipboard
Copied
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)
Copy link to clipboard
Copied
Thanks Bkbk, I will try your code. The dbtype value is defined in application.cfm file, so it comes from there.
Copy link to clipboard
Copied
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.
Copy link to clipboard
Copied
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>
Copy link to clipboard
Copied
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>
Copy link to clipboard
Copied
Sure Bkbk, I will try your suggestions and let u know, how it goes. Thanks again.
Copy link to clipboard
Copied
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
Copy link to clipboard
Copied
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
Copy link to clipboard
Copied
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 ?
Copy link to clipboard
Copied
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

