vanderzar Posted November 19, 2017 Share Posted November 19, 2017 (edited) 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 Edited November 19, 2017 by vanderzar Quote Link to comment Share on other sites More sharing options...
benanamen Posted November 19, 2017 Share Posted November 19, 2017 (edited) 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. Edited November 19, 2017 by benanamen Quote Link to comment Share on other sites More sharing options...
vanderzar Posted November 20, 2017 Author Share Posted November 20, 2017 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? Quote Link to comment Share on other sites More sharing options...
kicken Posted November 20, 2017 Share Posted November 20, 2017 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. Quote Link to comment Share on other sites More sharing options...
vanderzar Posted November 20, 2017 Author Share Posted November 20, 2017 (edited) 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. Edited November 20, 2017 by vanderzar Quote Link to comment Share on other sites More sharing options...
kicken Posted November 20, 2017 Share Posted November 20, 2017 (edited) 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. Edited November 20, 2017 by kicken Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.