For example, does root always have all privileges on all databases?
If I create a new database, will this user automatically have all privileges on the new database without having to run a GRANT command?
2 Answers
By default only root has full access to everything on the database. However, it is very easy to set it so that every user has access to the data in the database.
The following is for a new user specifically: This is usually the more accepted way of granting privileges.
1. On the root account I create a new database with table, and rows
mysql> create database db;
Query OK, 1 row affected (0.00 sec)
mysql> use db;
cDatabase changed
mysql> create table t1(k int);
Query OK, 0 rows affected (0.21 sec)
mysql> insert into t1 values (1),(2),(3);
Query OK, 3 rows affected (0.05 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from t1;
+------+
| k |
+------+
| 1 |
| 2 |
| 3 |
+------+
3 rows in set (0.00 sec)
2. I then create another user called user1 and flush
mysql> CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';
Query OK, 0 rows affected (0.00 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
3. I exit the root account, and access the user1 (identified by 'password')
user@localhost:~$ mysql -u user1 -P 2227 test -p
Enter password:
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 458
Server version: 5.5.5-10.1.19-MariaDB Source distribution
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
4. When I check which databases are available, it's clear that db is not there.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| test |
+--------------------+
2 rows in set (0.00 sec)
5. In order to have db there, you would need to execute the following command on the root account:
mysql> GRANT ALL PRIVILEGES ON * . * TO 'user1'@'localhost';
Query OK, 0 rows affected (0.00 sec)
As you can see, user1 now has access to all the data in MySQL.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| db |
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.00 sec)
If you would like to set root privliages to all existing users, without iterating through the names, then execute the following (under root):
mysql> GRANT ALL PRIVILEGES ON * . * TO '.'@'localhost';
Query OK, 0 rows affected (0.00 sec)
Mysql by default creates a single or multiple root user accounts (this depends on the mysql version) that are indeed superusers and have full access to all databases that you create on that mysql server. However, these accounts are initialised as superusers and you can remove their access rights based on your requirements. Or at least you can rename the superuser accounts from root to something else to make it even more harder to hack your mysql server. Any superuser account with all privileges granted to *.* and with grant option added will have full access to any newly created database on the mysql server.
Technically, it is possible not to have any superuser account in a mysql server, but it does not make much sense. Such accounts should be used as a last resort only, but they can be handy in case of an emergency.
