I'm at the point in a project I'm working on where I am vastly overthinking things due to too much information and not enough experience in MySQL, so I'm pretty much looking for the experts here to say "shut up and do this." I'm even open to suggestions that my entire approach is dumb and should be overhauled. The only things in this project that are non-negotiable are the use of php and MySQL.
So I'm trying to implement row-level security in MySQL. Read some good articles explaining that MySQL doesn't have roles, so you either need to use a view for each table you need FGAC for or where conditions filtering for user-identifiable information like their userid. Because of the relative complexity of the database and security concerns of the nature of the database, I opted for the view route. Here's an example of a view I have:
create OR replace view v_system_requirement (
sys_id,
req_id,
sysreq_notes,
rate_id,
range_id,
art_id,
)
as
select
system_requirement.org_id,
organization.org_name,
organization.org_parent_branch,
system.sys_id,
system.sys_name
from organization JOIN system USING (org_id)
where
organization.org_id IN
(select o.org_id from organization o JOIN user u ON o.org_parent_branch=u.org_id OR o.org_id=u.org_id WHERE u.user_email=substring_index(user(), '@', 2)
);
select * from v_user_reference;
Since each user would have his own database account, with the vast majority of them only being able to select, update, insert, etc. the relevant views, this perfectly gives me the organization that user works for, immediate child organizations of the user's organization, and any systems that belong to any of the aforementioned organizations. Usernames are their email addresses, hence the 2 in the substring_index of the user function, and their passwords are sufficiently hashed.
The rub here comes in when I add the web-interface. Obviously, when the user logs in with their username and password, these will be passed to the database through mysqli_connect(). To the best of my knowledge, best practice is to close the connection after each transaction or group of transactions is run.
So the question becomes, how does the user connect under his database account? I'm obviously not going to ask him to reinput his password everytime. And saving the hashed password in a $_SESSION variable is a bad idea, right? Given that, is there something that can be done on the MySQL side to connect as a more general account but set the user as himself such that user() will return his actual database account name?
Or, as I asked in the opening, is this approach just dumb and should be scrapped in favor of a better practice? Thanks for any information you can provide.
Generally in PHP/MySQL applications there is a single account for the application to access the database. This would be stored in a config file of some sort or in the class you have which creates a database connection.
You would then store your user's data in a users table, and you could also have a roles/permissions table which joins to each user through a user_permissions/user_roles table.
This way you can then have a user logged in and authenticated by matching their credentials with their row in the database, and you can manage what permissions they have and don't have to worry about them typing their password all the time to log into the database, as the application logs into the database and not the user.
Using the W3Schools Tutorial as a base it would look something like this:
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
// prepare and bind
$stmt = $conn->prepare("SELECT * FROM users WHERE email = ?");
$stmt->bind_param("s", $_POST['email']);
$stmt->execute();
$user = $stmt->fetch_assoc();
if(password_verify($_POST['password'], $user['password']))
{
echo "User authenticated";
}
else
{
echo "DENIED!!";
}
?>
You could then store the fact that your user is authenticated in a $_SESSION variable, and have your application code check that variable to see if a user should be allowed to remain on the "dashboard" or be redirected back to login page.