I created a webform for users to submit data and store the input in the mariadb database. The data input side is working fine. Now I need to create the page where the data is verified and approved or denied. For the data input I used a hard coded username and password in the php. This is what I did in mariadb:
CREATE USER 'username'@localhost IDENTIFIED BY 'password';
GRANT INSERT ON mytable TO 'username'@localhost;
and used those credentials in the PDO.
For the verification side I would like to prompt for a username and password and pass that information to mariadb for PDO usage. The goal is not to create users in Linux, htaccess, mariadb, etc. I am looking to create just 1 username and password for easy maintenance.

Should I have httpd prompt for the username and password? Should I write php to accept a username and password? I come from a windows background where this is usually handled by IIS windows integrated authentication and Active Directory.

I don't use MariaDB, I use MySQL. But it should be close enough. I do use IIS as my development server but I don't use Active Directory at all.

I find the bare minimum database permissions to be 'Insert', 'Update', 'Select' and 'Delete'. But, do you want every user to have 'Delete'? Probably not. On the database side you may want to create 2-4 user accounts. Each user will have varying permissions. But only 1 account should have 'Delete'.

Now you need to create a table called 'Users'. In this table you will store the user name, hashed password and any other data you want to store from the user (like e-mail, phone no, country, timezone). You may also want a column for 'SQL Access'. This is how you assign a database access level for each user. You may want to assign a default SQL level for the user (the lowest you can). If you want to allow the user 'Delete' permission then assign another SQL user account to the 'SQL Access' column. If you plan on allowing the user to store multiple e-mail addresses or phone number then you will need 2 more tables that link to the 'User' table (the database must support foreign keys for this).

This means you need at least 2 pages. 1 page to 'register' the user and 1 page for the user to log in. DO NOT save the passwords as plain text. Use the PHP command 'password_hash' to create the password that you store in the database.

The 'log in' page will use the PHP command 'password_verify' to compare and verify the password.

Remember, the user doesn't have access to the database during the register process. You will need to log in to the database using 1 of the users you created (on the database side).


Hope I've made sense.

It makes sense but I was going for a more streamlined approach. There is the public facing webpage where the public enters their data. The code for that is in the other thread and works well. This webpage is going to be for the 2 users who will be approving/rejecting the applications. These two users won't create their accounts. I will create their accounts.

I am planning to do the following:
create user 'user1'@localhost IDENTIFIED BY 'password1';
create user 'user2'@localhost IDENTIFIED BY 'password2';
GRANT UPDATE ON solicitortable TO 'user1'@localhost;
GRANT UPDATE ON solicitortable TO 'user2'@localhost;
FLUSH PRIVILEGES;

The question becomes how do I get the username and password from the person? And then get it from the system into $userprompt and $passwordprompt?

Then it should be a matter of:
try {
$pdo = new PDO($dsn, $userprompt, $passwordprompt, $options);

$stmt = $pdo->prepare("SELECT * FROM mytable WHERE accepted is null");
$stmt->execute();
while ($row=$stmt->fetch(PDO::FETCH_ASSOC)) {
echo $row[$field1data];
}
$stmt = null;
$pdo = null;
}
catch (Exception $e) {
error_log($e->getMessage());
exit('Something bad happened');
}

Well, in that case. You could create a PHP include file. This file should contain a PHP associative array which contains 2 entries. 1 for each login name, password and DB login information. You then require or include the file in your action script, the script that runs when the form is submitted. What ever you call it.

Once the include file is 'require/include' you then have access to the array information. You check the user name / password and if correct you set a session variable for the DB login information. If user name / password are incorrect you send them back to the login page.

You would then use the session variables in all your DB scripts.