Search code examples
mysqlsecuritypermissionsmariadbsql-grant

db contains records with empty username and suspicous db entries

I want to make sure that my php website and the user to login the database has only mandatory permissions / privileges on the maria db. Also i would like to know

  • if certain records are suspicious or at least an insecure configuration
  • how to safely delete certain records if it is advisable to do so

The records are

  1. from mysql.user the record User=webphp AND Host=%
  2. from information_schema.USER_PRIVILEGES the record GRANTEE='webphp'@'%'
  3. from mysql.db the record User="" and host='%'

Records inside my database

mysql.user

SELECT Host, User FROM mysql.user where Host in ('%', '127.0.0.1', '::1', 'localhost');

+-----------+--------+
| Host      | User   |
+-----------+--------+
| 127.0.0.1 | root   |
| ::1       | root   |
| localhost | root   |
| %         | webphp |  <-- Can i delete this to avoid connections from outside?
| localhost | webphp |
+-----------+--------+

information_schema.USER_PRIVILEGES

SELECT * FROM information_schema.USER_PRIVILEGES where PRIVILEGE_TYPE = 'USAGE';

+----------------------+---------------+----------------+
| GRANTEE              | TABLE_CATALOG | PRIVILEGE_TYPE |
+----------------------+---------------+----------------+
| 'webphp'@'localhost' | def           | USAGE          |
| 'webphp'@'%'         | def           | USAGE          | <-- Can i delete this?
+----------------------+---------------+----------------+

mysql.db

SELECT Host, User, Length(User), Db FROM mysql.db;

+-----------+--------+--------------+---------+
| Host      | User   | Length(User) | Db      |
+-----------+--------+--------------+---------+
| %         |        |            0 | test    |  <-- AFAIK i have no Db named test
| %         |        |            0 | test\_% |  <-- same here - Delete?
| localhost | webphp |            6 | webphp  |
+-----------+--------+--------------+---------+

From the MariaDB Server Documentation about mysql.db it is unclear to me if records in mysql.db should have an empty string "" in the column User. The docs information-schema-innodb_sys_datafiles-table do not state if i can delete records here.

Questions regarding granting only mandatory rights / privileges

I plan to grant these rights

GRANT USAGE, SELECT, INSERT, UPDATE, DELETE, EXECUTE, ALTER, CREATE TEMPORARY TABLES  
ON *webphp.* TO 'webphp'@localhost IDENTIFIED BY 'mySuperSecretPW';

The above should ensure that user webphp can only connect to the db webphp if he connects from the same hardware

  1. Are these rights sufficient? Did i miss any?
  2. Is it a good idea?

Questions for db=test and user =""

  1. Should i remove the records inside mysql.db where the field / column User is empty?
  2. To my knowledge i have no Db named test or test\_%. Can i delete these records?

Questions for db=webphp and user ="webphp"

  1. information_schema.USER_PRIVILEGES to my understanding GRANTEE = 'webphp'@'%' allows the user webphp to connect from anywhere. If my database is running on the same hardware can and should i remove it?
  2. mysql.user can i delete the record host=% and user=webphp to avoid use from other servers?
Solution 
  • GRANT USAGE, SELECT, INSERT, UPDATE, DELETE, EXECUTE, ALTER, CREATE TEMPORARY TABLES  
ON *webphp.* TO 'webphp'@localhost IDENTIFIED BY 'mySuperSecretPW';

    Are these rights sufficient? Did i miss any?

    Be more specific on the database name and use webphp.*. Do not give the user delete or alter authority if it really doesn't need it. If your application needs to delete things have a column in the table that flags something to be deleted.

    Is it a good idea?

    Yes.

    Questions for db=test and user =""

    Delete users and databases that aren't needed.

    information_schema.USER_PRIVILEGES to my understanding GRANTEE = 'webphp'@'%' allows the user webphp to connect from anywhere. If my database is running on the same hardware can and should i remove it?

    Yes

    mysql.user can i delete the record host=% and user=webphp to avoid use from other servers?

    Yes