Access Denied for Particular User

PHP
1

I am using My SQL 5.6.31 on RHEL 7.2 and PHP 5.5. Here are some issues I am facing in the following scenarios:

Scenario #1 (Adding User via command line which works fine with PHP Code)
There is a database called practiceDB. I used Use practiceDB command to select the database and created a user using the command

CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'mypass';

Connection Test Via PHP Code:

So, when I connect via my PHP code and specify user jeffrey and password mypass in my PHP files, I can connect to the database without any problem.

Connection Test Via Command Line:

Also, when I log in like the following from linux shell mysql -u jeffrey -pmypass, I can get the mysql prompt and running Show databases command lists practiceDB database.

Scenario #2: (When my PHP Code is creating user and adding it to database)

Connection Test Via PHP Code:

The user gets created and inserted into the users table of practiceDB database. However, when I try to connect using the same user (say for example I used peter as username), I keep on getting the following error in my logs:

[:error] [pid 22866] [client ] PHP Fatal error: Could Not Connect to the Database : Access denied for user 'peter'@'localhost' (using password: YES) in /var/www/dbonn.php on line ##, referer: ....

The reason for above error as I can see is there is no password I have specified/hardcoded as I did in Scenario #1 for user jefferey. Basically, I don’t want to specify password at all, even for user jeffrey, Is there a way to achieve this?

Connection Test Via Command Line:

When I try to log in like this, mysql -u peter, I can get into the mysql prompt and upon running the show database command , I see information-schema database selected which is default I believe. So basically, it has nothing to do with the practiceDB where user was inserted using the PHP Code.

2

I’m confused, is there a difference between the two scenarios that in the second one, you have tried to create a user without a password? That is, is the difference between the working and non-working user that you have created it from the MySQL command line vs. created it from your PHP code, or is the difference that you have created a user without a password?

Somewhere in my head I think there’s a setting somewhere that you have to enable to allow users to be created without passwords, but I can’t think what or where.

3

The differences between the two scenarios are as follows:

  1. In Scenario #1, running the command mysql> select * from mysql.user;, I can see the user created using the command prompt with some long password. So, the user shows up at both the places, that is at above location after running the mysql> select * from mysql.user; command and insider the user table of my database.

However, user inserted into the user table from the Scenario #2 (PHP Code), doesn’t shows up after running the command mysql> select * from mysql.user;.So, the user is only residing at one place, that is in the user table

  1. When I hard code the user created from Scenario #1 in my PHP code along with the password, I don’t face any connection related issues.However, there is no way to hardcode and test the user created from PHP code(Scenario #2) since I don’t know what password to provide and using simply ' ' doesn’t work for password field.

Please let me know if I can elaborate or any further questions. Thanks

4

Could you show the PHP code you use to create the user that isn’t working correctly?

5

Sure, here is the code snippet. The code is basically just inserting the user into database.

$user   = $_POST["user"]; 
$sql    ="INSERT INTO $table_name_users (uname) VALUES (\"$user\")";
$result = mysqli_query($connection,$sql) or trigger_error("Couldn Not Insert Data into User's Table : ". mysqli_error($sql));
6

This is the bit that’s confusing me - your PHP code doesn’t seem to go anywhere near the mysql.user table, only your own users table (unless the code you showed is in a loop around both tables). So if you’re not creating the code in that table, surely you won’t be able to use it as a login to connect to the database, only as a login for whatever you use your own login names for?

If you want to create a MySQL database user, you need to do it with a query along the lines of your first command in Scenario #1, but run as a query. If you do that, but miss out the section about passwords altogether, does it create the user without a password?

It’s difficult to find many references to creating database users without passwords because the majority of focus seems to be on increasing security, rather than decreasing it.

7

You are right. There is no PHP code that goes anywhere near to mysql.user. It’s just a user addition into the database using the INSERT INTO clause.

  1. Is it a requirement to do something like this(writing a code handling mysql.user related insertion) whenever user related data insertion needs to happen in the database. Could you refer me to any online material where I can understand how to do that in PHP code?

  2. Also, is having a MySQL database user a mandatory requirement to make connection with that user via PHP code? I mean right now I just have the user existing in the database table.

Thanks

8

As far as I know, the only reason you have a MySQL user like the one you create in scenario #1 is for accessing a MySQL database. If you’re implementing your own user login or user accounting system and the usernames are simply to control access to that, then there’s no need for a separate MySQL user for each of “your” users.

9

To me the idea of different “users” is not individual users, but groups of users. With various abilities based on their “roles”

For example, “site_owner” could be GRANTed everything, “content_editor” UPDATE but not DELETE, “site_visitor” only SELECT.

Then depending on the query and who’s using it, things are more secure.

10

ETA: " as part of your database connection routine."

11

I see. Do you think it would make more sense to have CREATE user type of SQL command in the PHP code itself along with the INSERT INTO SQL Clause.

12

If all you’re doing is creating users for your own login management, I personally don’t see a need for each of those to have their own separate username for database access. Your application could quite easily use the same one in your database connection script. But I may have misunderstood what you’re actually trying to achieve.

13

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.