Hey guys! Who every thought about minimizing the size of your database? Especially for the security section. I had a project come up where the company said, "We need a security system where each user can access certain controls. Some of the controls on a page we want disabled for some users." Most sites I have worked on has had controls on pages, so if their secrity clearance wasnt high enough to access the page, then it denied them.

So, I started thinking. Hmmm.....they have approximentaly 150 admin controls, and they have approximentaly 350 employees. At first I thought, why not give each control a value number which would relate to an id in a database table called security. The table would have fields: id, user_id, & clearance. All these fields being INTEGER. Then I started thinking, this is going to be a huge database. This could have reached a recordcount of 150,000 easily. So I said thats way too big! Hmmmmm.......maybe arrays could be useful in this. And yes they were! (You may want to read the basic concepts of arrays before reading this tutorial.)

Lets create a simple Application.cfm File
----------------------------------------------------------------------------------
<cfapplication name="array_tutorial" sessionmanagement="Yes" sessiontimeout="#CreateTimeSpan(0,2,0,0)#">
----------------------------------------------------------------------------------

Lets create a simple database (MySQL):
-----------------------------------------------------------------------------------
<cfset #ddsn# = "data_source">
<cfquery name="Create_Table" datasource="#ddsn#">
CREATE TABLE members (
id INTEGER Auto_Increment Primary Key,
username VARCHAR(20),
password VARCHAR(20),
security MEDIUMTEXT
)
</cfquery>
-----------------------------------------------------------------------------------

So now we have the fields: id, username, password, & security.

Lets insert some data into them.
------------------------------------------------------------------------------------
<cfset ddsn = "data_source">
<cfset USERNAME = "wes">
<cfset PASSWORD = "wes_password">
<cfset SECURITY = "1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,25,50,75,100,150">
<cfquery name="Create_Table" datasource="#ddsn#">
INSERT INTO members
(username, password, security)
VALUES
('#USERNAME#', '#PASSWORD#', '#SECURITY#')
</cfquery>
------------------------------------------------------------------------------------

Now we have some data in our database. Lets create a login for this database (Code Only):
------------------------------------------------------------------------------------
<cfset ddsn = "data_source">
<cfset USERNAME = "wes">
<cfset PASSWORD = "wes_password">
<cfquery name="qLoginUser" datasource="#ddsn#">
SELECT username,password,security
FROM members
WHERE username = '#USERNAME#' AND password = '#PASSWORD#'
</cfquery>

<cfif #val(qLoginUser.recordcount)# EQ 1>
<!--- If a login record is found, process this code --->
<cfset session.username = "#username#">
<!--- Create a session.username --->
<cfset session.security = ArrayNew(1)>
<!--- Create a 1 dimensional array which will be stored as a session --->
<cfset number = 0>
<cfloop list="#qLoginUser.security#" delimiters="," index="i">
<!--- Start a loop seperating each number by a "," --->
<cfset number = (#val(number)# + 1)>
<!--- Make number increase by 1 on each loop --->
<cfset session.security[#val(number)#] = "#i#">
<!--- Insert values into the session array --->
</cfloop>
</cfif>
---------------------------------------------------------------------------------

OK! Now we have a session created (#session.security#) which is storing our array and its values. Lets act like we are on a page which has 2 controls which are named with the values of 7 and 777. Our security says we have access to 7, but not 777. So how do we keep the control 777 from showing up on the page? Lets write a code which will check to see if that number is listed in their array.
---------------------------------------------------------------------------------
<!--- Check Security: We would be allowed to view this one --->
<cfset security = 7> <!---Security must include Level 7 --->
<cfloop from="1" to="#ArrayLen(session.security)#" index="i">
<cfif refind(#session.security[i]#,#security#) AND len(#session.security[i]#) EQ len(#security#)>
<!--- Feature to include --->
<a href="employee_directory.cfm">EMPLOYEE DIRECTORY</a>
<!--- END Feature to include --->
</cfif>
</cfloop>
<!--- END CHECK SECURITY --->

<!--- Check Security: This one wouldnt show up --->
<cfset security = 777> <!---Security must include Level 777 --->
<cfloop from="1" to="#ArrayLen(session.security)#" index="i">
<cfif refind(#session.security[i]#,#security#) AND len(#session.security[i]#) EQ len(#security#)>
<!--- Feature to include --->
<a href="edit_employee_directory.cfm">EDIT EMPLOYEE DIRECTORY</a>
<!--- END Feature to include --->
</cfif>
</cfloop>
<!--- END CHECK SECURITY --->
-----------------------------------------------------------------------------------

Thats it! Arrays are pretty cool indeed! They can help you out greatly. This tutorial requires that you understand what an array is, what arrays are able to do, and what commands control arrays. This tutorial is not something for you to copy and paste, but to give you the understanding of what an array can be used for. Also in the queries, dont forget to use <cfqueryparam> and on an actual page, use the loop to deny access to it as well. OK! Enjoy guys!