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.

Answers


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.


Need Your Help

curl: (60) SSL certificate : unable to get local issuer certificate

curl ssl openssl ssl-certificate x509certificate

root@sclrdev:/home/sclr/certs/FreshCerts# curl --ftp-ssl --verbose ftp://{abc}/ -u trup:trup --cacert /etc/ssl/certs/ca-certificates.crt

numpy multinomial function returns value error

python numpy multinomial

I am using the numpy.random.multinomial(1,val) NumpyMultinomial, where val is a 1-D numpy array. The function gives the following error: