Limiting Access by Permission
thanks for viewing this. I have a db that has users, roles & user_roles. What I am trying to achieve is a login that will select users who have Admin or Associate permissions. The login then uses name and password to permit access.
My SQL syntax thus far is -
SELECT * FROM users LEFT JOIN ON user_roles ON user.id=user_roles.userid AND roleid IN (Administrator, Associate) WHERE username = '$username' AND password = '$password'";
I am not sure where I am going wrong.
Thanks in advance for your help.
Try replacing "LEFT JOIN" by "INNER JOIN"
Here's how I'd write the query:
$stmt = $pdo->prepare(" SELECT (u.password = :password) AS password_is_correct, (r.roleid IS NOT NULL) AS role_is_authorized FROM users u LEFT JOIN ON user_roles r ON u.id=r.userid AND r.roleid IN (Administrator, Associate) WHERE u.username = :username"); $stmt->execute(array(":password"=>$password, ":username"=>$username));
This allows you to distinguish between the three conditions: (1) username does not exist, (2) password is wrong, or (3) role is not authorized.
PS: Should "Administrator" and "Associate" be quoted or something? The way you're using them, they look like identifiers rather than values.