I’m a relative noob with SQL and PHP, but learning every day. I managed to create a DB with some tables using phpMyAdmin and my webhost’s cPanel.
Then I made some PHP scripts in my web pages that create, change and display that data, and it works, so far so good.
I have been reading up on security, as this is new to me and every site recommends doing that to avoid attacks.
I read about having different user accounts to access the data with limited privileges to avoid unwanted queries, sounds sensible. I had one user that created the tables and has the ability to do things like truncate, drop etc… So I made a couple more users in cPanel. One was a “Read Only” with just SELECT granted, for pages that just display a table of data for example. The other was an “Editor” user, which has DELETE, INSERT, SELECT and UPDATE granted, for use on form pages, so it can add, change and delete entries, but not destroy tables.
The problem is, when I get my scripts is access the DB with these accounts, I get:
Access denied for user ‘username’@‘localhost’ (using password: YES)
It works fine with the original user with more privileges.
Anyone got any clues?
Are you certain the “original user” has the GRANT privilege?
Last I tried to do something similar for similar reasons I was disappointed to learn that my shared host did not give me GRANT privileges, hence I was unable to increase security this way.
No, the original user does not have GRANT privilege. But the privileges are set from the cPanel, which I believe should work. Though it is a ‘dumbed down’ interface without all the options you get doing it by SQL queries, but I can’t do that, because my user doesn’t have the privieges, catch 22.
Coincidentaly, I just got a reply from my host support, it has been several days since I requested support, I thought they were not replying. It says:
When you assign the database users from the cPanel, cPanel handles the MySQL database and the privileges.
But they are asking for more info, So I will see where this leads.
I have it working now. Turned out to be an error on my part. When you create a user in cPanel, the username gets a pre-fix added to it which is your cPanel usename and an underscore. I only entered the last part of the name, without the prefix.