Jump to content

The mysql.user table and login troubles


vanderzar

Recommended Posts

Hi everyone

 

I created a database called shop.

I created also the user "wildcard1" with the password "1234" for this shop database. 

User "wildcard1" has the following privileges on the "shop" database: "GRANT SELECT, INSERT, UPDATE, DELETE, INDEX, ALTER, CREATE, DROP"

 

Now i want to execute the login code which you see below. And there I have a problem.

The problem is that the query (see row: 22 in the code below) is not executed.

Would the solution be to give the user "wildcard1", the "SELECT" privilege on the "mysql.user" table? But if so, if these would be the solution. Would giving the "SELECT" privilege on random users to mysql.user not be a serious security issue?

<!DOCTYPE HTML>
<html>
<head>
	<title>Sign-In</title>
	<link rel="stylesheet" type="text/css" href="./css/style.css">
</head>
<body id="body-color">
	
	<?php
	function db_connect() {

		
		$result = new mysqli("localhost", "wildcard1", "1234", "shop");
		//echo $result->num_rows();
		if (!$result) {
		echo "failed <br>";
		throw new Exception('Could not connect to database server');
		}
		else {
			echo "done <br>";
			echo 'Die aktuelle PHP Version ist ' . phpversion() . "<br>";
			$rows = $result->query("SELECT * FROM mysql.user WHERE User = 'wildcard1' AND Password = password('1234')");
			echo "Object type is: ";
			if($rows){echo "true <br>";} else{echo "false <br>";};
			printf("Query String: %d rows.\n <br>", $rows->num_rows);
			echo $result->host_info . "<br> \n";
			
			if ($result->connect_errno) {
				printf("Connect failed: %s\n", $result->connect_error);
				exit();
			}
			
			/* check if server is alive */
			if ($result->ping()) {
				printf ("Our connection is ok!\n");
			} else {
				printf ("Error: %s\n", $result->error);
			}
			
			/* close connection */
			//$result->close();
			
			
			return $result;
		}
	}
	
	db_connect();
	?>	
	
</body>
</html> 

all best

Link to comment
Share on other sites

You need to create a users table in your shop database and select from that table, (shop.users). The mysql.users table is the credentials and permissions for MySQL itself, not your application.

 

You will need to use password_hash and password_verify. Never store passwords in plaintext.

In case I create an user table "shop.users". There will be still the problem, that any user will have access to this table and will be able to get the hashed password. Or am I mistaken?

What purpose would have the shop.users table, because the actual login happens anyway through the mysql.users table?

Link to comment
Share on other sites

In case I create an user table "shop.users". There will be still the problem, that any user will have access to this table and will be able to get the hashed password. Or am I mistaken?

What purpose would have the shop.users table, because the actual login happens anyway through the mysql.users table?

It's up to your application to control what your users do or don't have access to.

 

It sounds like you're confusing mysql's user accounts with users that would come to your site. You should create just one user account for mysql that your application's code uses to connect to the database. That user needs to have all the permissions necessary to manage your applications data. End-user accounts have nothing to do with this, they are an entirely separate thing that your application controls.

 

Your end-user accounts belong as part of your applications data, such as the shop.users table mentioned. It's up to your application code to manage this data and granting/denying access to different things based on whatever permissions you want to define for the different users.

Link to comment
Share on other sites

It's up to your application to control what your users do or don't have access to.

 

It sounds like you're confusing mysql's user accounts with users that would come to your site. You should create just one user account for mysql that your application's code uses to connect to the database. That user needs to have all the permissions necessary to manage your applications data. End-user accounts have nothing to do with this, they are an entirely separate thing that your application controls.

 

Your end-user accounts belong as part of your applications data, such as the shop.users table mentioned. It's up to your application code to manage this data and granting/denying access to different things based on whatever permissions you want to define for the different users.

Yes. But lets suppose the admin account has all the permissions. This account can manage all the databases.

Now if I create an end-user with the following order:

use shop;
CREATE USER "Arrow" IDENTIFIED BY "1234";
GRANT SELECT, INSERT, UPDATE, DELETE, INDEX, ALTER, CREATE, DROP on shop.* TO "Arrow" IDENTIFIED BY "1234";

Then the username and password of the end-user arrow is anyways stored in the mysql.user table.

Link to comment
Share on other sites

Yes. But lets suppose the admin account has all the permissions. This account can manage all the databases.

Now if I create an end-user with the following order:

This is what I mean by I think you're confusing the accounts. For your application, within mysql's authentication system there's no admin vs end-user account, there is only one account, which is used by your application to connect to the database.

 

Admin vs end-user accounts is something you code into your application. Your application has a table that stores all your users and what permissions they have, then your application code enforces those permissions. This side of things has nothing to do with mysql and it's accounts / GRANT statements.

 

Mysql's GRANT statement is only for managing mysql accounts, not anything with your application/end-user accounts. Mysql's user accounts are always stored in the mysql.user database because that's where mysql stores it's own data, it doesn't matter what database you're currently using when you issue the grant statement.

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.