Database Design and Permissions questions

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
ripcurlksm
Forum Commoner
Posts: 34
Joined: Sun Aug 08, 2004 9:17 pm

Database Design and Permissions questions

Post by ripcurlksm »

Overview:
I am taking on a project to allow restricted access to 500 Microsoft Publisher files (simply a HTML page with synchronized Powerpoint + Audio presentation) online where customers will be granted directory access to certain reports that they purchased. Please note that MS Publisher is just a simple webpage that plays audio/powerpoint slides. Some users will have access to certain reports, others may have access to all reports. I want the admin of the site to be able to grant/remove access in the database to users when they purchase the report.

The key goal to the project is to have the user be able to login, see a list of the reports they purchased with a link to view them. Upon clicking to view a report, their login/key would be passed to the report page they are trying to access and pending on the database, would grant or deny access. A simple example of a directory with a report url would be http://website.com/report/report1/index.htm, http://website.com/report/report2/index.htn, etc. etc.

I have a few questions on database design and the best way to handle user privileges.


Database schema:
REPORTS
-------------
unique_id
company
description
report_url


USER
-------------
id
username
password
email
(?)permissions (?)


My Questions
1) What is the best way to link the USER table to the REPORT table? Meaning, should I create a seperate table called PERMISSIONS to handle which users are allowed to view which reports? Some users will have access to a few reports, others may have access to all of them. Will there be a list of 500 rows for each user with a '0' or '1' to keep a tally of what reports they have access to? Please advise?

2) Is .htaccess a good way to handle permissions, where a database would store permissions for each user and when they try to view a directory it would authenticate their access and allow or deny?


Any advice or suggestions on my database schema in regards to protected access or privelages would be appreciated! Thanks!
User avatar
Luke
The Ninja Space Mod
Posts: 6424
Joined: Fri Aug 05, 2005 1:53 pm
Location: Paradise, CA

Post by Luke »

That sounds like a many-to-many relationship (users can have many reports, and reports can have many users), so you need an intermediary (join) table

Table name "users"
id
username
password
email

Table name "reports"
id
company
description
report_url

Table name "users_reports"
user_id
report_id

Then you'd fill the users table with users and the reports table with reports, and then the users_reports table would be filled with every relationship between the two. So if user #42 has access to reports 3, 23, and 99, you'd add these three records to the users_reports table:
user_id, report_id
42, 3
42, 23
42, 99

Then I would read each report from a url above the root (so they aren't publicly accessible... if you need help on this just let me know)

If the only privileges you need are "read" access, then that's all you need. :)
ripcurlksm
Forum Commoner
Posts: 34
Joined: Sun Aug 08, 2004 9:17 pm

Post by ripcurlksm »

Thanks for that it makes sense! Under what circumstances would using .htaccess appropriate?
User avatar
Luke
The Ninja Space Mod
Posts: 6424
Joined: Fri Aug 05, 2005 1:53 pm
Location: Paradise, CA

Post by Luke »

well, you could use .htaccess for authentication (not to be confused with authorization), but for this, I'd just implement a simple login system:

Code: Select all

<?php
session_start();

function redirect($location)
{
    if(!headers_sent())
    {
        header('Location: ' . $location);
    }
    else
    {
        trigger_error('Cannot redirect - headers already sent', E_USER_ERROR);
    }
    exit;
}

if(isset($_POST['action'] && $_POST['action'] == 'login')
{
    $errors = array();
    if (isset($_POST['username']))
    {
        $username = $_POST['username'];
    }
    else
    {
        $errors['username'] = 'You did not enter your username';
    }

    if(isset($_POST['password']))
    {
        $password = $_POST['password'];
    }
    else
    {
        $errors['password'] = 'You did not enter your password';
    }

    if(empty($errors))
    {
        // Connect to database server
        // Select database
        $result = mysql_query("
            SELECT * FROM users WHERE username = '" . $username . "' LIMIT 1
        ");
        // Load result into an array
        if($username == $db_username && $password == $db_password)
        {
            $_SESSION['user_id'] = $db_user_id;
            session_write_close();
            redirect('http://www.mysite.com/users/home');
        }
        else
        {
            $errors[] = 'Invalid login';
        }
    }
}
?>
<!-- put the login form here and display any errors from errors array //-->
And then on the users/home/index.php page, you'd do something like:

Code: Select all

<?php
session_start();
function redirect($location)
{
    if(!headers_sent())
    {
        header('Location: ' . $location);
    }
    else
    {
        trigger_error('Cannot redirect - headers already sent', E_USER_ERROR);
    }
    exit;
}
if(!isset($_SESSION['user_id']))
{
    redirect('http://www.mysite.com/users/login');
}
This is a very simple and untested example and it's missing most of the important stuff like outputting the form, and database connection logic but hopefully it gives you an idea of what to do.

EDIT: if you need a little more security, take a look at Maugrim the Reaper's Challenge/response tutorial. In a challenge/response authentication system, no passwords are sent, but instead a sha256 hash is generated on the client side, and then generated in the exact same way on the server side, and compared for authentication (on server side). Very cool... check it out. viewtopic.php?t=38810
Post Reply