I have a table with columns:
the last 4 columns are the access levels (user, support, accounts and admin)
here is the code i am using to check the permissions:
$permission_sql="SELECT * from admin_permissions where page_name = '".$_SERVER["REQUEST_URI"]."' ";
$permission_rs=mysql_query($permission_sql,$conn);
if(mysql_num_rows($permission_rs) == 0)
{
echo '<h2 align="center">An Error has occurred!</h2>';
exit();
}
else
{
$permission_result=mysql_fetch_array($permission_rs);
if($usertype_user != $permission_result["level_user"])
{
echo '<h2 align="center">Access Denied</h2>';
echo '<h2 align="center">Please contact your administrator quoting \'Permission Error\' and number \''.$permission_result["sequence"].'\'</h2>';
exit();
}
if($usertype_support != $permission_result["level_support"])
{
echo '<h2 align="center">Access Denied</h2>';
echo '<h2 align="center">Please contact your administrator quoting \'Permission Error\' and number \''.$permission_result["sequence"].'\'</h2>';
exit();
}
if($usertype_admin != $permission_result["level_admin"])
{
echo '<h2 align="center">Access Denied</h2>';
echo '<h2 align="center">Please contact your administrator quoting \'Permission Error\' and number \''.$permission_result["sequence"].'\'</h2>';
exit();
}
if($usertype_accounts != $permission_result["level_accounts"])
{
echo '<h2 align="center">Access Denied</h2>';
echo '<h2 align="center">Please contact your administrator quoting \'Permission Error\' and number \''.$permission_result["sequence"].'\'</h2>';
exit();
}
}
i am accessing: /admin/index.php so my query is saying
select * from admin_permissions where page_name = '/admin/index.php'
then my if statements run and the user i am logged in as has $usertype_admin set as 'yes' and $permission_result["level_admin"] is equal to 'yes' but its displaying the Access denied error which shouldn't be as $usertype_admin does == $permission_result["level_admin"]
what am i doing wrong?
You could have a table called permission_table which would store the pages that each user is allowed to access, pages they aren't allowed to access would not appear here
+---------------+--------------+----------+
| permission_id | page | user_id |
+---------------+--------------+----------+
| 1 | home.php | 33 |
| 2 | accounts.php | 2 |
| 3 | support.php | 67 |
+---------------+--------------+----------+
Then the query for each page would be
SELECT * from permissions_table where user_id = :user_id AND page = :page
You'd get the currently logged in user's ID from a stored session like $_SESSION['user_id']. You could even store the pages they are allowed to visit in a session array to save database queries
You should use PDO instead of the depretiated "mysql_". The full code would be...
session_start(); //This is needed when working with PHP sessions
//The mysql database details
$dbhost = 'your db host';
$dbuser = 'your db username';
$dbpass = 'your db password';
$dbname = 'your db name';
//PDO connection
try {
$con = new PDO("mysql:host=$dbhost;dbname=$dbname", $dbuser, $dbpass);
$con->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch(PDOException $e) {
echo 'ERROR: ' . $e->getMessage();
}
//Permissions query
$stmt = $con->prepare('SELECT * from permissions_table WHERE user_id = :user_id AND page = :page');
$stmt->bindParam(':staff_id', $_SESSION['user_id']);
$stmt->bindParam(':page', basename($_SERVER['PHP_SELF']));
//If PDO query executes and returns 1 row!
if($stmt->execute() && $stmt->rowCount() === 1){
echo 'Permission granted'!
} else { die('Permission denied!');
}
The best part is that all of this script could be in a seperate php file that is then included at the top of each page as a require like so... require ('permissions.php');
So if the code were to change you would only have to change it once in permissions.php
Alternatively if you just want 4 different permission types instead doing permissions by each page. You can change the page column to 'type'. And store things like 'Admin', 'Support' etc. Then at the top of each page put things like this...
$permissiontype = 'Support';
require ('permissions.php');
Then change the query and PDO binding to get the variable instead of current page
$stmt->bindParam(':type', $permissiontype);
"mysql_fetch_array" returns a zero-indexed array for the row. If you want the column names to be indexes within the row, then you should use "mysql_fetch_assoc". For more information on using that function, see this page: http://us1.php.net/manual/en/function.mysql-fetch-assoc.php
Basically, you're getting back:
$permission_result[0] = whatever "sequence" is
$permission_result[1] = the page name that you asked for
$permission_result[2] = value of level_user
$permission_result[3] = value of level_support
$permission_result[4] = value of level_accounts
$permission_result[5] = value of level_admin
So, when you ask for
$permission_result["level_user"]
it turns out that "$permission_result" has no element called "level_user", so it gives you "null" as the result. I'm pretty sure that your permission variables for the user are booleans (true/false) or characters (such as 'y' and 'n'), they won't match the null value that you're getting by checking
$permission_result["level_user"]
Switch "mysql_fetch_array" to "mysql_fetch_assoc" and you'll probably be fine.
While I present a proposal below in the second section, her is an explanation of "why" the code doesn't work, based on the updated question/information.
Then my if statements run and the user i am logged in as has $usertype_admin set as 'yes' and $permission_result["level_admin"] is equal to 'yes' but its displaying the Access denied error which shouldn't be as $usertype_admin does == $permission_result["level_admin"]
This is because if any of the if branches are true the error message will be displayed and exit will be called - note that the condition for each case !=. Imagine this data:
(permission) $user database $user != database
------------ ------ -------- -----------------
user yes no TRUE
support yes no TRUE
admin yes yes FALSE
accounts yes no TRUE
Then it can be seen that, even though the user is an administrator (e.g. $user(user) is 'yes'), and the page only "asks for" the administrator permission, the authorization will fail because $user(user) is 'yes' while database(user) is 'no'. Whoops.
Here is one approach that requires a matching permission only if set to non-'no' in the database:
function hasAllPermissions ($permissions) {
// Note the use of a NEGATIVE selector on the database value
if($permission_result["level_admin"] != 'no'
&& $usertype_admin != $permission_result["level_admin"]) {
// Only here if a permission is set OTHER than 'no' and it does
// not equal the currently assigned user permission.
return FALSE;
}
// .. the other checks
// If we haven't rejected yet, the the database either asks for
// no permissions or we pass all permission checks.
return TRUE;
}
// In check:
if (!hasAllPermissions($permission_result)) {
// display warning and exist
}
Or, perhaps we want the inverse:
function hasAnyPermission ($permissions) {
// Note the use of a POSITIVE selector on the database value
if($permissions["level_admin"] != 'no'
&& $usertype_admin == $permissions["level_admin"]) {
// We matched, so accept as having permission and return to
// avoid the additional checks.
return TRUE;
}
// .. the others checks
// And if nothing succeeded in matching, then we fail.
return FALSE;
}
// In check:
if (!hasAnyPermission($permission_result)) {
// display warning and exit
}
Note how both forms are almost identical, except for the inversion of the test and return result.
Some changes I would recommend, besides redoing the entire schema (which I do also recommend):
$usertype_xyx, such that $usertype["xyz"] mirrors $permission_result["xyz"] and can be passed around to the appropriate functions(The following was written to address the original question and I feel it still holds merit.)
SQL is designed to expand along rows, not columns - column names should not contain information.
When using a column-oriented approach you'll have to add in support for each column name and give it meaning (see "column names should not contain information"). While this could be done based on heuristics and reading the dynamic column shape (e.g. from a SELECT *), I would advise against this approach.
Instead, the following structure may be a more appropriate start for a database schema with role-based permissions.
Users
- Username
Pages
- PageTitle
Roles
- RoleName
Users_Roles -- Granted Roles
- FK Users
- FK Roles
Pages_Roles -- Required (or Denied) Roles
- FK Pages
- FK Roles
Then, given a function hasAllRequiredPermissions($user, $page), it is possible to construct a query that will use the above tables to determine if a particular use has all the required permissions to view a page and the function won't require updating when a new Role is added. This is because the information exists in rows - not columns.
Additionally, the above relationships allow the model to be easily extended to allow things like "Allow access if User has Role" and "Deny access if User has Role".
Also, not using placeholders is a problem and the posted query with $_SERVER["REQUEST_URI"] could potentially be exploited. Make sure to use placeholders when writing the new authentication code.