[MySQL+PHP] User/Ranking System for Included files

[s-p-n]

Update : Partially Tested.

At some point (if you're a web developer), you'll find yourself in a spot where you need to keep some scripts bundled together, and viewed only by a certain group of users, classified by rank, username, user-group, etc. This tutorial will guide you through the process of securing "groups of scripts", or "modules", for view abilities only by a "group of users", or "rank".

There are numerous ways to go about doing this, the way I've chosen is strongly MySQL based, with light PHP scripts.

The script provides an easy yet scalable way to verify the inclusion of a directory during inclusion based on a user's username or rank. If neither username nor rank are specifically allowed, entry will be denied.

My procedures are as follows:

Spoiler:

1. Create a MySQL database with 5 tables: (`users`, `ranks`, `modules`, `module_ranks`, `module_users`) which will be used to authenticate the use of modules on the site for a given user/rank. Also, it must be compatible for multiple ranks and users.
2. Create a means of storing module files in a secure fashion.
3. Create a script to set secure user sessions, based solely on log-in information.
4. Create a script to pull data for a given module from the database, which handles security based on settings in the database.

Creating the Database
[spoiler]
The goal is to keep modules- groups of scripts secure. Since we want some users to be able to see certain scripts, and not others, we need a way to pick a users of a certain rank, and base security off of that, given that they really are who they're logged in as when they're logged in.

- Create a MySQL database.
- Create a table named `users` with at least these 4 fields:

`id`(int, auto-increment), `user`(varchar, unique), `pass`(text), `rank`(int)

- Create a table named `ranks` with at least these 2 fields:

`id`(int, auto-increment), `name`(varchar, unique)

- Create a table named `modules` with at least these 2 fields:

`id` (int, auto-increment), `directory` (varchar, unique)

- Create a table named `module_ranks` with these 3 fields:

`id` (int, auto-increment), `module_id` (int), `rank_id` (int)

- Create a table named `module_users` with these 3 fields:

`id` (int, auto-increment), `module_id` (int), `user_id` (int)

New!
Run This Query In PhpMyAdmin,

Spoiler:

-- phpMyAdmin SQL Dump
-- version 3.1.3.1
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Mar 16, 2010 at 12:21 PM
-- Server version: 5.1.33
-- PHP Version: 5.2.9

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

--
-- Database: `test`
--

-- --------------------------------------------------------

--
-- Table structure for table `modules`
--

CREATE TABLE IF NOT EXISTS `modules` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `directory` varchar(250) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `directory` (`directory`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;

--
-- Dumping data for table `modules`
--

INSERT INTO `modules` VALUES(1, 'admin/test.php');

-- --------------------------------------------------------

--
-- Table structure for table `module_ranks`
--

CREATE TABLE IF NOT EXISTS `module_ranks` (
  `module_id` int(11) NOT NULL,
  `rank_id` int(11) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Dumping data for table `module_ranks`
--

INSERT INTO `module_ranks` VALUES(1, 1);

-- --------------------------------------------------------

--
-- Table structure for table `module_users`
--

CREATE TABLE IF NOT EXISTS `module_users` (
  `module_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Dumping data for table `module_users`
--


-- --------------------------------------------------------

--
-- Table structure for table `ranks`
--

CREATE TABLE IF NOT EXISTS `ranks` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;

--
-- Dumping data for table `ranks`
--

INSERT INTO `ranks` VALUES(1, 'Administrator');
INSERT INTO `ranks` VALUES(2, 'Moderator');
INSERT INTO `ranks` VALUES(3, 'Registered');
INSERT INTO `ranks` VALUES(4, 'Guest');

-- --------------------------------------------------------

--
-- Table structure for table `users`
--

CREATE TABLE IF NOT EXISTS `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user` varchar(50) NOT NULL,
  `pass` text NOT NULL,
  `rank` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `user` (`user`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;

--
-- Dumping data for table `users`
--

INSERT INTO `users` VALUES(1, 'S-p-n', '1e6947ac7fb3a9529a9726eb692c8cc5', 1);

(Creates All of The Database Tables.)
(- Inserts 4 Ranks, 'Guest','Registered','Moderator','Administrator')
(- Inserts 1 module entry for the directory 'admin')
(- Inserts 1 Module_ranks entry, giving 'Administrator' rights to view the 'admin' directory.)
(- Inserts a user 'S-p-n' with the password 'Secret', and the rank "Administrator")

Database Breakdown

Spoiler:

This database will work to pull data for the "directory" of the module out of the `modules` table given the user's rank/id is available along with the module's id. We need a PHP script and a MySQL query that will work with this database, though.

Here's a mysql query that'll work, though it will pull all of the modules out of the database where the variables $_SESSION['users_id'] or $_SESSION['users_rank'] are the correct values for the module.

Spoiler:

$query_modules = mysql_query('SELECT `directory` FROM `modules`
     LEFT OUTER JOIN `module_ranks` ON(`module_ranks`.`module_id` = `modules`.`id`)
     LEFT OUTER JOIN `module_users` ON(`module_users`.`module_id` = `modules`.`id`)
    WHERE `rank_id` = "' . $_SESSION['users_rank'] . '"
    OR `user_id` = "' . $_SESSION['users_id'] . '"') or die(mysql_error());
while($module_row = mysql_fetch_assoc($query_modules))
{
    echo '<p>'.$module_row['directory'].'</p>'; // a list of allowed directories
} 

This doesn't solve the whole problem though.

How on earth can anyone use this to secure their scripts? First, we need to create a parent directory for all of the modules, named "modules" or whatever you like. Second, we need to create a ".htaccess" file with the contents, "deny from all". Finally, designate a single directory for each module, or group of files/scripts. Then we'll need a function to replace "require()/include()". Before we can expect any of that to work to our advantage, we need to conquer the basics- the user log-in system.

Log in System
Though this part is notoriously simple, it's important we do it the correct way that will work with this system. The end result will store a session of the user+pass, so the script can verify the log-in details every page-load. This is important for security reasons.

Spoiler:

Assuming you know how to create an HTML form, here is the "action" side of the log-in script.

//A Function To Set a Session for a User If Their User+Pass is correct.
function login_user($user,$pass)
{
    $query = mysql_query('SELECT * FROM `users` WHERE `user` = "'.mysql_real_escape_string($user).'" AND `pass` = "'.md5($pass).'"');

    if(mysql_num_rows($query)==1) 
    {
        $row = mysql_fetch_assoc($query);
        foreach($row as $key=>$val)
        {
            $_SESSION['users_'.$key] = $val;
        }
        return true;
    }
    return false;
}

//If Login Button Was Pressed,
if(isset($_POST['login_button']))
{
    login_user($_POST['user'],$_POST['pass']);
} 

NEW! Everything you need to add modules.

Spoiler:

It's really easy to add a module, I made a few functions (in examples below) to assist you. You should always know what a function does before using it. These ones are really easy to use, so let's explore how they work.

Spoiler:

(Note: the source for some of these functions are below this section.)

Add a Module entry for a directory/file.
To do this, you might want the function below:

add_module('directory/file.htm'); //create module row 

All this does, is add the string 'directory/file.htm' in the database. In order to use this to your advantage, you'll need to assign ranks/users to your module. In order to do that, you'll need two IDs (and a script to send a query).

You need the ID for :
- user or rank,
- module.

You can use the first call to assign a user to the module, the second to assign a rank.

$user_id = 1;
$module_id = 2;
assign_to_module($user_id,$module_id,'user'); //Assign user to module

$rank_id = 1;
assign_to_module($rank_id,$module_id,'rank'); //Assign rank to module 

It may be a concern to grab the IDs? If that's the case, you're sure in luck. I made a couple functions to help you along there.

To grab a module ID, you only need the value for the 'directory' field in the 'modules' table for the module Id you want to grab. Just use the function 'pull_module_id()' as follows.

$path = 'directory/file.htm'; //What's the directory/path to file?

$module_id = pull_module_id($path); //Grab module id for given path. 

TaDa!

You can use the function 'pull_rank()' to pull the ID for a given rank name (or the name for a given ID.)

$rank_id = pull_rank('Administrator'); 
//Same as $rank_id=pull_rank('Administrator',false);

$rank_name = pull_rank(1,true); 

I didn't make a function to pull the user ID, but you can simply look at the source for the function, pull_rank(), and create pull_user() by changing just a few details.

To secure the directory: "admin", use

file_put_contents('deny from all','admin/.htaccess')

To create a module for the directory "./admin" to be seen only by the rank "Administrator", do this:

//Assuming there's a rank named "Administrator" in the database, this will pull the id.
$rank = pull_rank('Administrator'); //pull rank id from db where rank name is 'Administrator'
//$rank could optionally be '1'... when the id for 'Administrator' is '1'.
$dir = 'admin'; //The local directory name

add_module($dir); //create module row

$module = pull_module_id($dir); //Grab module id

assign_to_module($rank,$module,'rank'); //Assign rank to module 

Not working?
Be sure to look in the "Examples" part for the functions you need for this to work!

Securely Requiring/Including a Module
For this next function, we're going to need to use the include or require function, but we'll be using an optional boolean variable to do so. Let's name it, pull_module().

Spoiler:

The Following Function uses security for directories in a recursive manor. For example, if the rank 'super-admin' has permission to view the '/modules/' directory, they will be able to see '/modules/anything/any_file.ext' EVEN IF the user is not permitted specifically. If a user, "someone" has permission to see '/modules/anything/', they will not have permission to see '/modules/something_else/'. A user's rank or username can grant permission. You can also store a file as a module, and the user/rank assigned to it will only be able to see that single file. A user/rank can of course have permissions to multiple modules. Also, a module can be assigned to various users/rank, allowing ultimate flexibility.

/*
You can use include, require, or either with the '_once' parameter. You can additionally use file_get_contents, which returns the contents of a file in a string, rather than including it in the script.
//$file=filename.
//$type = include{0/unknown}, require{1}, string{2}
//$once = use _once, 0/unknown=no, 1=yes // Not applicable with the string return type.
*/
function pull_module($file, $type=0,$once=0)
{
    if(strpos('/',$file)!==false)
    {
        $directories = explode('/',$file);
        $dir_clause = '';
        $dir_root = '';
        foreach($directories as $level)
        {
            $dir_clause.='`directory` = "'.$dir_root.$level.'" OR ';
            $dir_root.=$level.'/';
        }
        $dir_clause = substr($dir_clause,0,strlen($dir_clause)-4);
    } else {
        $dir_clause = '`directory` = "'.$file.'"';
    }
    $query_modules = mysql_query('SELECT `directory` FROM `modules`
          LEFT OUTER JOIN `module_ranks` ON(`module_ranks`.`module_id` = `modules`.`id`)
          LEFT OUTER JOIN `module_users` ON(`module_users`.`module_id` = `modules`.`id`)
         WHERE ('.$dir_clause.')
        AND (
            `rank_id` = "' . $_SESSION['users_rank'] . '"
            OR `user_id` = "' . $_SESSION['users_id'] . '"
        )') or die(mysql_error());

    if(mysql_num_rows($query_modules)==0)
    {
        return false; //die('Permission Denied For: '.$dir_clause);
    }
    if($type==2)
    {
        return file_get_contents($file);
    }else if($type==1)
    {
        if($once==1)
        {
            return require_once($file);
        } else
        {
            return require($file);
        }
    } else
    {
        if($once==1)
        {
            return include_once($file);
        } else
        {
            return include($file);
        }
    }
} 

Well that does it. You can now (with the correct values in the database), pull modules out in a secure fashion with the above function. I know you have allot of questions, please read through the comments in the scripts below for additional help.


The following scripts should aid you in using this new database style,

Spoiler:

//Add Typical Ranks to DB
mysql_query('INSERT INTO `ranks` (`name`) VALUES("Administrator")') or die(mysql_error());
mysql_query('INSERT INTO `ranks` (`name`) VALUES("Moderator")') or die(mysql_error());
mysql_query('INSERT INTO `ranks` (`name`) VALUES("Registered")') or die(mysql_error());
mysql_query('INSERT INTO `ranks` (`name`) VALUES("Guest")') or die(mysql_error());


//rank: name from id or id from name 
function pull_rank($rank,$type=false)
{
    //[type=true{id->name}]
    //[type=false{name->id}]

    if($type)
    {
        $query = mysql_query('SELECT `name` FROM `ranks` WHERE `id`="'.$rank.'"') or die(mysql_error());
    } else {
        $query = mysql_query('SELECT `id` FROM `ranks` WHERE `name`="'.$rank.'"') or die(mysql_error());
    }
    $row = mysql_fetch_row($query);
    return $row[0];
}


//Register a User
function register($user,$pass,$rank=3)
{
    return mysql_query('INSERT INTO `users` (`user`,`pass`,`rank`) VALUES("'.mysql_real_escape_string($user).'","'.md5($pass).'","'.$rank.'")') or die(mysql_error());
}
register('S-p-n','Secret',pull_rank('Registered'));

//Be sure to run the sign-in directly after register ;)


//A Function To Set a Session for a User If Their User+Pass is correct.
function login_user($user,$pass)
{
    $query = mysql_query('SELECT * FROM `users` WHERE `user` = "'.mysql_real_escape_string($user).'" AND `pass` = "'.md5($pass).'"') or die(mysql_error());

    if(mysql_num_rows($query)==1) 
    {
        $row = mysql_fetch_assoc($query);
        foreach($row as $key=>$val)
        {
            $_SESSION['users_'.$key] = $val;
        }
        return true;
    }
    return false;
}

//Database a Directory/Module
function add_module($file)
{
    mysql_query('INSERT INTO `modules` (`directory`) VALUES("'.mysql_real_escape_string($file).'")') or die(mysql_error());
}

function pull_module_id($dir)
{
    $query = mysql_query('SELECT `id` FROM `modules` WHERE `directory` = "'.mysql_real_escape_string($dir).'" LIMIT 1') or die(mysql_error());
    $row = mysql_fetch_row($query);
    return $row[0];
}

//Assign a Rank/User To a Module
function assign_to_module($data, $module,$type='rank')
{
//$data is either the rank id, or user id; decided by $type.
//$module is the module's id.
//$type must be a string, either 'rank' or 'user'. 

    if($type=='rank')
    {
        return mysql_query('INSERT INTO `module_ranks` (`module_id`,`rank_id`) VALUES("'.mysql_real_escape_string($module).'","'.mysql_real_escape_string($data).'")') or die(mysql_error());
    } else if($type=='user')
    {
        return mysql_query('INSERT INTO `module_users` (`module_id`,`user_id`) VALUES("'.mysql_real_escape_string($module).'","'.mysql_real_escape_string($data).'")') or die(mysql_error());
    } else return false;
} 

Example of usage,

Spoiler:

Let's say I have a directory called "admin". I want only Administrators (users with the rank "Administrator") to use the scripts pulled from the "admin" folder.

Well, let's give a little "example installer".

Run this script one time, and then delete it. (Be sure to create the database first ;)
Create a file named "install.php" or something, and run it, then delete it if it says "Everything Worked!". You may have to create a database. *This will only work 1 time.*

UPDATE: If you ran the query above from PhpMyAdmin, you don't need to make/run this install script.

<?php
mysql_connect('localhost','root','XXXXX');
mysql_select_db('test');

/* Run the below script only one time! */
//Add Typical Ranks to DB
mysql_query('INSERT INTO `ranks` (`name`) VALUES("Administrator")') or die(mysql_error());
mysql_query('INSERT INTO `ranks` (`name`) VALUES("Moderator")') or die(mysql_error());
mysql_query('INSERT INTO `ranks` (`name`) VALUES("Registered")') or die(mysql_error());
mysql_query('INSERT INTO `ranks` (`name`) VALUES("Guest")') or die(mysql_error());

register('S-p-n','Secret',pull_rank('Administrator'));

//Assuming there's a rank named "Administrator" in the database, this will pull the id.
$rank = pull_rank('Administrator',false); //pull rank id from db where rank name is 'Administrator'
$dir = 'admin'; //The local directory name

add_module($dir); //create module row

$module = pull_module_id($dir); //Grab module id

assign_to_module($rank,$module,'rank'); //Assign rank to module
echo '<p>Everything Worked!</p>';
?>

If you installed the data with no issues, you just created the user 's-p-n' with the password 'Secret', and the rank 'Administrator'. Be aware of that and delete it when you're done testing, please.

You also created a few default ranks, "Guest", "Registered", "Moderator", and "Administrator".

On top of that, you've just added a module entry for the directory "admin".

Create these two files somewhere on your site/web server.
name_me_anything.php

<?php
session_name('test');
session_start();
mysql_connect('localhost','root','XXXXX');
mysql_select_db('test');


if(isset($_GET['logout']))
{
    session_destroy();
    session_name('test');
    session_start();
    echo '<p>Logged Out.</p>';
}
require_once('module_settings.php');
if(isset($_POST['login_btn']))
{
    login_user($_POST['user'],$_POST['pass']); //Run this to Log In
}

pull_module('admin/test.php');

if(!isset($_SESSION['users_user']))
{
    ?>
    <form method="post" action="<?php echo $_SERVER['PHP_SELF']; ?>">
    Username: <input type="text" name="user" />
    <br />
    Password: <input type="password" name="pass" />
    <br />
    <input type="submit" name="login_btn" value="Log In" />
    </form>
    <?php
}
?>

module_settings.php

<?php
/*
You can use include, require, or either with the '_once' parameter. You can additionally use file_get_contents, which returns the contents of a file in a string, rather than including it in the script.
//$file=filename.
//$type = include{0/unknown}, require{1}, string{2}
//$once = use _once, 0/unknown=no, 1=yes // Not applicable with the string return type.
*/
function pull_module($file, $type=0,$once=0)
{
    if(strpos('/',$file)!==false)
    {
        $directories = explode('/',$file);
        $dir_clause = '';
        $dir_root = '';
        foreach($directories as $level)
        {
            $dir_clause.='`directory` = "'.$dir_root.$level.'" OR ';
            $dir_root.=$level.'/';
        }
        $dir_clause = substr($dir_clause,0,strlen($dir_clause)-4);
    } else {
        $dir_clause = '`directory` = "'.$file.'"';
    }
    $query_modules = mysql_query('SELECT `directory` FROM `modules`
            LEFT OUTER JOIN `module_ranks` ON(`module_ranks`.`module_id` = `modules`.`id`)
            LEFT OUTER JOIN `module_users` ON(`module_users`.`module_id` = `modules`.`id`)
            WHERE ('.$dir_clause.')
            AND (
                `rank_id` = "' . $_SESSION['users_rank'] . '"
                OR `user_id` = "' . $_SESSION['users_id'] . '"
            )') or die(mysql_error());

    if(mysql_num_rows($query_modules)==0)
    {
        return false; //die('Permission Denied For: '.$dir_clause);
    }
    if($type==2)
    {
        return file_get_contents($file);
    } 
    else if($type==1)
    {
        if($once==1)
        {
            return require_once($file);
        } else
        {
            return require($file);
        }
    } else 
    {
        if($once==1)
        {
            return include_once($file);
        } else
        {
            return include($file);
        }
    }
}

//rank: name from id or id from name 
function pull_rank($rank,$type=false)
{
    //[type=true{id->name}]
    //[type=false{name->id}]

    if($type)
    {
        $query = mysql_query('SELECT `name` FROM `ranks` WHERE `id`="'.$rank.'"') or die(mysql_error());
    } else {
        $query = mysql_query('SELECT `id` FROM `ranks` WHERE `name`="'.$rank.'"') or die(mysql_error());
    }
    $row = mysql_fetch_row($query);
    return $row[0];
}


//Register a User
function register($user,$pass,$rank=3)
{
    return mysql_query('INSERT INTO `users` (`user`,`pass`,`rank`) VALUES("'.mysql_real_escape_string($user).'","'.md5($pass).'","'.$rank.'")') or die(mysql_error());
}

//Be sure to run the sign-in directly after register ;)


//A Function To Set a Session for a User If Their User+Pass is correct.
function login_user($user,$pass)
{
    $query = mysql_query('SELECT * FROM `users` WHERE `user` = "'.mysql_real_escape_string($user).'" AND `pass` = "'.md5($pass).'"') or die(mysql_error());

    if(mysql_num_rows($query)==1) 
    {
        $row = mysql_fetch_assoc($query);
        foreach($row as $key=>$val)
        {
            $_SESSION['users_'.$key] = $val;
        }
        return true;
    }
    return false;
}

//Database a Directory/Module
function add_module($file)
{
    mysql_query('INSERT INTO `modules` (`directory`) VALUES("'.mysql_real_escape_string($file).'")') or die(mysql_error());
}

function pull_module_id($dir)
{
    $query = mysql_query('SELECT `id` FROM `modules` WHERE `directory` = "'.mysql_real_escape_string($dir).'" LIMIT 1') or die(mysql_error());
    $row = mysql_fetch_row($query);
    return $row[0];
}

//Assign a Rank/User To a Module
function assign_to_module($data, $module,$type='rank')
{
//$data is either the rank id, or user id; decided by $type.
//$module is the module's id.
//$type must be a string, either 'rank' or 'user'. 

    if($type=='rank')
    {
        return mysql_query('INSERT INTO `module_ranks` (`module_id`,`rank_id`) VALUES("'.mysql_real_escape_string($module).'","'.mysql_real_escape_string($data).'")') or die(mysql_error());
    } else if($type=='user')
    {
        return mysql_query('INSERT INTO `module_users` (`module_id`,`user_id`) VALUES("'.mysql_real_escape_string($module).'","'.mysql_real_escape_string($data).'")') or die(mysql_error());
    } else return false;
}  
?>

Now, in the same directory as the other files, create a folder named "admin".
Add these two files,
.htaccess

deny from all

test.php

<?php
    echo '<p>Hello, '.$_SESSION['users_user'].' ('.pull_rank($_SESSION['users_rank'],true).')</p>';
    echo '<a href="?logout">Log Out</a>?';
?>

Open the file, "name_me_anything.php" (or whatever you named it), and you should see a form to log in. You can log in as "S-p-n" with the password "Secret". Hopefully, viewing the PHP code and reviewing the comments help should help you to get a feel for how it works, and how you can utilize it to suit your needs. It gives you the ability to include scripts, with much less worry about security.

[It's likely to be buggy the first few days, as I haven't tested everything yet. The concept is key though, it's a proven technique.]

Sure hope all this helps somebody....

[MentaL]

[holthelper]

seems interesting.

thanks s-p-n for this tut.

[Arcelor]

Thanks Spence :]

[EliteGM]

Awesome, Spence.