How to grant all privileges to root user in MySQL 8.0

Try running this:

SELECT user,host,password FROM mysql.user WHERE user='';

mysql> select user,host,password from mysql.user;
+-----------+-------------+-------------------------------------------+
| user | host | password |
+-----------+-------------+-------------------------------------------+
| lwdba | 127.0.0.1 | *xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx |
| lwdba | localhost | *xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx |
| lwdba | % | *xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx |
| root | localhost | *xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx |
| root | 127.0.0.1 | *xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx |
| vanilla | localhost | |
| mywife | % | |
| | % | | <<<--- LOOK !!!
| replicant | 10.64.113.% | *xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx |
| kumar | % | |
+-----------+-------------+-------------------------------------------+

So, how did you login? Run this query:

SELECT USER(),CURRENT_USER();

CREATE USER 'username'@'localhost' IDENTIFIED BY 'the_password';

ALL PRIVILEGES — a full root access to the databases. If no database is specified, it has global access across the system.
CREATE — create new tables or databases
DROP — delete tables or databases
DELETE — delete rows from tables
INSERT — insert rows into tables
SELECT — use the SELECT command to read through databases
UPDATE — update table rows
GRANT OPTION — grant or remove other users’ privileges

GRANT ALL PRIVILEGES ON *.* TO 'user_name'@'localhost' WITH GRANT OPTION;

CREATE USER 'username'@'%' IDENTIFIED BY 'the_password';

And grant full root access:

GRANT ALL PRIVILEGES ON *.* TO 'username'@'%' WITH GRANT OPTION;

SHOW GRANTS FOR username;

5. Finally, when everything is settled, reload all the privileges:

And all the changes will take effect immediately.

New to TablePlus? It’s a modern, native tool with an elegant GUI that allows you to simultaneously manage multiple databases such as MySQL, PostgreSQL, SQLite, Microsoft SQL Server and more. 

Download TablePlus for Mac.

Not on Mac? Download TablePlus for Windows.

On Linux? Download TablePlus for Linux

Need a quick edit on the go? Download TablePlus for iOS.

TablePlus GUI for MySQL

I have dealt with this issue before.

When you ran

select count(1) UserTableColumnCount from information_schema.columns
where table_schema='mysql' and table_name='user';

Hopefully you could run

# mysql_upgrade --upgrade-system-tables

Give it a Try !!!

#
# Backup the mysql.user table
#
CREATE TABLE mysql.user_backup LIKE mysql.user;
INSERT INTO mysql.user_backup SELECT * FROM mysql.user;
#
# Add Missing Columns
#
ALTER TABLE mysql.user ADD COLUMN Create_tablespace_priv enum('N','Y') NOT NULL DEFAULT 'N' AFTER Trigger_Priv
;
ALTER TABLE mysql.user ADD COLUMN plugin char(64);
ALTER TABLE mysql.user ADD COLUMN authentication_string text DEFAULT NULL;
#
# Give root user all privileges
#
UPDATE mysql.user SET
Select_priv='Y',Insert_priv='Y',Update_priv='Y',Delete_priv='Y',
Create_priv='Y',Drop_priv='Y',Reload_priv='Y',Shutdown_priv='Y',
Process_priv='Y',File_priv='Y',Grant_priv='Y',References_priv='Y',
Index_priv='Y',Alter_priv='Y',Show_db_priv='Y',Super_priv='Y',
Create_tmp_table_priv='Y',Lock_tables_priv='Y',Execute_priv='Y',
Repl_slave_priv='Y',Repl_client_priv='Y',Create_view_priv='Y',
Show_view_priv='Y',Create_routine_priv='Y',Alter_routine_priv='Y',
Create_user_priv='Y',Event_priv='Y',Trigger_priv='Y',
Create_tablespace_priv='Y'
WHERE user='root';
FLUSH PRIVILEGES;

That’s it !!!

Oh my goodness, I think the problem stems from mixing the mysql schema of different mysql versions. First of all, run this query:

desc mysql.user;

For MySQL 8.0, you get 51 columns

mysql> desc mysql.user;

51 rows in set (0.00 sec)

For MySQL 5.6, you get 43 columns

mysql> desc mysql.user;
+------------------------+-----------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------------+-----------------------------------+------+-----+---------+-------+
| Host | char(60) | NO | PRI | | |
| User | char(16) | NO | PRI | | |
| Password | char(41) | NO | | | |
| Select_priv | enum('N','Y') | NO | | N | |
| Insert_priv | enum('N','Y') | NO | | N | |
| Update_priv | enum('N','Y') | NO | | N | |
| Delete_priv | enum('N','Y') | NO | | N | |
| Create_priv | enum('N','Y') | NO | | N | |
| Drop_priv | enum('N','Y') | NO | | N | |
| Reload_priv | enum('N','Y') | NO | | N | |
| Shutdown_priv | enum('N','Y') | NO | | N | |
| Process_priv | enum('N','Y') | NO | | N | |
| File_priv | enum('N','Y') | NO | | N | |
| Grant_priv | enum('N','Y') | NO | | N | |
| References_priv | enum('N','Y') | NO | | N | |
| Index_priv | enum('N','Y') | NO | | N | |
| Alter_priv | enum('N','Y') | NO | | N | |
| Show_db_priv | enum('N','Y') | NO | | N | |
| Super_priv | enum('N','Y') | NO | | N | |
| Create_tmp_table_priv | enum('N','Y') | NO | | N | |
| Lock_tables_priv | enum('N','Y') | NO | | N | |
| Execute_priv | enum('N','Y') | NO | | N | |
| Repl_slave_priv | enum('N','Y') | NO | | N | |
| Repl_client_priv | enum('N','Y') | NO | | N | |
| Create_view_priv | enum('N','Y') | NO | | N | |
| Show_view_priv | enum('N','Y') | NO | | N | |
| Create_routine_priv | enum('N','Y') | NO | | N | |
| Alter_routine_priv | enum('N','Y') | NO | | N | |
| Create_user_priv | enum('N','Y') | NO | | N | |
| Event_priv | enum('N','Y') | NO | | N | |
| Trigger_priv | enum('N','Y') | NO | | N | |
| Create_tablespace_priv | enum('N','Y') | NO | | N | |
| ssl_type | enum('','ANY','X509','SPECIFIED') | NO | | | |
| ssl_cipher | blob | NO | | NULL | |
| x509_issuer | blob | NO | | NULL | |
| x509_subject | blob | NO | | NULL | |
| max_questions | int(11) unsigned | NO | | 0 | |
| max_updates | int(11) unsigned | NO | | 0 | |
| max_connections | int(11) unsigned | NO | | 0 | |
| max_user_connections | int(11) unsigned | NO | | 0 | |
| plugin | char(64) | YES | | | |
| authentication_string | text | YES | | NULL | |
| password_expired | enum('N','Y') | NO | | N | |
+------------------------+-----------------------------------+------+-----+---------+-------+
43 rows in set (0.15 sec)

For MySQL 5.5, you get 42 columns

mysql> desc mysql.user;
+------------------------+-----------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------------+-----------------------------------+------+-----+---------+-------+
| Host | char(60) | NO | PRI | | |
| User | char(16) | NO | PRI | | |
| Password | char(41) | NO | | | |
| Select_priv | enum('N','Y') | NO | | N | |
| Insert_priv | enum('N','Y') | NO | | N | |
| Update_priv | enum('N','Y') | NO | | N | |
| Delete_priv | enum('N','Y') | NO | | N | |
| Create_priv | enum('N','Y') | NO | | N | |
| Drop_priv | enum('N','Y') | NO | | N | |
| Reload_priv | enum('N','Y') | NO | | N | |
| Shutdown_priv | enum('N','Y') | NO | | N | |
| Process_priv | enum('N','Y') | NO | | N | |
| File_priv | enum('N','Y') | NO | | N | |
| Grant_priv | enum('N','Y') | NO | | N | |
| References_priv | enum('N','Y') | NO | | N | |
| Index_priv | enum('N','Y') | NO | | N | |
| Alter_priv | enum('N','Y') | NO | | N | |
| Show_db_priv | enum('N','Y') | NO | | N | |
| Super_priv | enum('N','Y') | NO | | N | |
| Create_tmp_table_priv | enum('N','Y') | NO | | N | |
| Lock_tables_priv | enum('N','Y') | NO | | N | |
| Execute_priv | enum('N','Y') | NO | | N | |
| Repl_slave_priv | enum('N','Y') | NO | | N | |
| Repl_client_priv | enum('N','Y') | NO | | N | |
| Create_view_priv | enum('N','Y') | NO | | N | |
| Show_view_priv | enum('N','Y') | NO | | N | |
| Create_routine_priv | enum('N','Y') | NO | | N | |
| Alter_routine_priv | enum('N','Y') | NO | | N | |
| Create_user_priv | enum('N','Y') | NO | | N | |
| Event_priv | enum('N','Y') | NO | | N | |
| Trigger_priv | enum('N','Y') | NO | | N | |
| Create_tablespace_priv | enum('N','Y') | NO | | N | |
| ssl_type | enum('','ANY','X509','SPECIFIED') | NO | | | |
| ssl_cipher | blob | NO | | NULL | |
| x509_issuer | blob | NO | | NULL | |
| x509_subject | blob | NO | | NULL | |
| max_questions | int(11) unsigned | NO | | 0 | |
| max_updates | int(11) unsigned | NO | | 0 | |
| max_connections | int(11) unsigned | NO | | 0 | |
| max_user_connections | int(11) unsigned | NO | | 0 | |
| plugin | char(64) | YES | | | |
| authentication_string | text | YES | | NULL | |
+------------------------+-----------------------------------+------+-----+---------+-------+
42 rows in set (0.01 sec)
mysql>

For MySQL 5.1, you get 39 columns

mysql> desc mysql.user;
+-----------------------+-----------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------------+-----------------------------------+------+-----+---------+-------+
| Host | char(60) | NO | PRI | | |
| User | char(16) | NO | PRI | | |
| Password | char(41) | NO | | | |
| Select_priv | enum('N','Y') | NO | | N | |
| Insert_priv | enum('N','Y') | NO | | N | |
| Update_priv | enum('N','Y') | NO | | N | |
| Delete_priv | enum('N','Y') | NO | | N | |
| Create_priv | enum('N','Y') | NO | | N | |
| Drop_priv | enum('N','Y') | NO | | N | |
| Reload_priv | enum('N','Y') | NO | | N | |
| Shutdown_priv | enum('N','Y') | NO | | N | |
| Process_priv | enum('N','Y') | NO | | N | |
| File_priv | enum('N','Y') | NO | | N | |
| Grant_priv | enum('N','Y') | NO | | N | |
| References_priv | enum('N','Y') | NO | | N | |
| Index_priv | enum('N','Y') | NO | | N | |
| Alter_priv | enum('N','Y') | NO | | N | |
| Show_db_priv | enum('N','Y') | NO | | N | |
| Super_priv | enum('N','Y') | NO | | N | |
| Create_tmp_table_priv | enum('N','Y') | NO | | N | |
| Lock_tables_priv | enum('N','Y') | NO | | N | |
| Execute_priv | enum('N','Y') | NO | | N | |
| Repl_slave_priv | enum('N','Y') | NO | | N | |
| Repl_client_priv | enum('N','Y') | NO | | N | |
| Create_view_priv | enum('N','Y') | NO | | N | |
| Show_view_priv | enum('N','Y') | NO | | N | |
| Create_routine_priv | enum('N','Y') | NO | | N | |
| Alter_routine_priv | enum('N','Y') | NO | | N | |
| Create_user_priv | enum('N','Y') | NO | | N | |
| Event_priv | enum('N','Y') | NO | | N | |
| Trigger_priv | enum('N','Y') | NO | | N | |
| ssl_type | enum('','ANY','X509','SPECIFIED') | NO | | | |
| ssl_cipher | blob | NO | | NULL | |
| x509_issuer | blob | NO | | NULL | |
| x509_subject | blob | NO | | NULL | |
| max_questions | int(11) unsigned | NO | | 0 | |
| max_updates | int(11) unsigned | NO | | 0 | |
| max_connections | int(11) unsigned | NO | | 0 | |
| max_user_connections | int(11) unsigned | NO | | 0 | |
+-----------------------+-----------------------------------+------+-----+---------+-------+
39 rows in set (0.00 sec)
mysql>

For MySQL 5.0, you get 37 columns

mysql> desc mysql.user;
+-----------------------+-----------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------------+-----------------------------------+------+-----+---------+-------+
| Host | char(60) | NO | PRI | | |
| User | char(16) | NO | PRI | | |
| Password | char(41) | NO | | | |
| Select_priv | enum('N','Y') | NO | | N | |
| Insert_priv | enum('N','Y') | NO | | N | |
| Update_priv | enum('N','Y') | NO | | N | |
| Delete_priv | enum('N','Y') | NO | | N | |
| Create_priv | enum('N','Y') | NO | | N | |
| Drop_priv | enum('N','Y') | NO | | N | |
| Reload_priv | enum('N','Y') | NO | | N | |
| Shutdown_priv | enum('N','Y') | NO | | N | |
| Process_priv | enum('N','Y') | NO | | N | |
| File_priv | enum('N','Y') | NO | | N | |
| Grant_priv | enum('N','Y') | NO | | N | |
| References_priv | enum('N','Y') | NO | | N | |
| Index_priv | enum('N','Y') | NO | | N | |
| Alter_priv | enum('N','Y') | NO | | N | |
| Show_db_priv | enum('N','Y') | NO | | N | |
| Super_priv | enum('N','Y') | NO | | N | |
| Create_tmp_table_priv | enum('N','Y') | NO | | N | |
| Lock_tables_priv | enum('N','Y') | NO | | N | |
| Execute_priv | enum('N','Y') | NO | | N | |
| Repl_slave_priv | enum('N','Y') | NO | | N | |
| Repl_client_priv | enum('N','Y') | NO | | N | |
| Create_view_priv | enum('N','Y') | NO | | N | |
| Show_view_priv | enum('N','Y') | NO | | N | |
| Create_routine_priv | enum('N','Y') | NO | | N | |
| Alter_routine_priv | enum('N','Y') | NO | | N | |
| Create_user_priv | enum('N','Y') | NO | | N | |
| ssl_type | enum('','ANY','X509','SPECIFIED') | NO | | | |
| ssl_cipher | blob | NO | | NULL | |
| x509_issuer | blob | NO | | NULL | |
| x509_subject | blob | NO | | NULL | |
| max_questions | int(11) unsigned | NO | | 0 | |
| max_updates | int(11) unsigned | NO | | 0 | |
| max_connections | int(11) unsigned | NO | | 0 | |
| max_user_connections | int(11) unsigned | NO | | 0 | |
+-----------------------+-----------------------------------+------+-----+---------+-------+
37 rows in set (0.01 sec)
mysql>

For MySQL 4.x, you get 31 columns

mysql> desc mysql.user;
+-----------------------+-----------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------------+-----------------------------------+------+-----+---------+-------+
| Host | varchar(60) binary | | PRI | | |
| User | varchar(16) binary | | PRI | | |
| Password | varchar(16) binary | | | | |
| Select_priv | enum('N','Y') | | | N | |
| Insert_priv | enum('N','Y') | | | N | |
| Update_priv | enum('N','Y') | | | N | |
| Delete_priv | enum('N','Y') | | | N | |
| Create_priv | enum('N','Y') | | | N | |
| Drop_priv | enum('N','Y') | | | N | |
| Reload_priv | enum('N','Y') | | | N | |
| Shutdown_priv | enum('N','Y') | | | N | |
| Process_priv | enum('N','Y') | | | N | |
| File_priv | enum('N','Y') | | | N | |
| Grant_priv | enum('N','Y') | | | N | |
| References_priv | enum('N','Y') | | | N | |
| Index_priv | enum('N','Y') | | | N | |
| Alter_priv | enum('N','Y') | | | N | |
| Show_db_priv | enum('N','Y') | | | N | |
| Super_priv | enum('N','Y') | | | N | |
| Create_tmp_table_priv | enum('N','Y') | | | N | |
| Lock_tables_priv | enum('N','Y') | | | N | |
| Execute_priv | enum('N','Y') | | | N | |
| Repl_slave_priv | enum('N','Y') | | | N | |
| Repl_client_priv | enum('N','Y') | | | N | |
| ssl_type | enum('','ANY','X509','SPECIFIED') | | | | |
| ssl_cipher | blob | | | | |
| x509_issuer | blob | | | | |
| x509_subject | blob | | | | |
| max_questions | int(11) unsigned | | | 0 | |
| max_updates | int(11) unsigned | | | 0 | |
| max_connections | int(11) unsigned | | | 0 | |
+-----------------------+-----------------------------------+------+-----+---------+-------+
31 rows in set (0.01 sec)

Picture this scenario:

You install 5.0
You uninstall 5.0
You forgot to move /var/lib/mysql out of the way
You install 5.1

mysql -hhostaddr -umyuserid -pmypassword --skip-column-names -A -e"SELECT CONCAT('SHOW GRANTS FOR ''',user,'''@''',host,''';') FROM mysql.user WHERE user<>''" | mysql -hhostaddr -umyuserid -pmypassword --skip-column-names -A | sed 's/$/;/g' > MySQLUserGrants.sql

Содержание

Solution 1
Solution 4
Solution 5
Related videos on Youtube
Comments
Solution 3
3 Управление пользователями в MySQL
4.3.1 Синтаксис GRANT и REVOKE
4.3.2 Имена и пароли пользователей MySQL
4.3.3 Когда привилегии вступают в силу
4.3.4 Установка начальных привилегий MySQL
4.3.5 Добавление новых пользователей в MySQL
4.3.6 Установка паролей
4.3.7 Хранение паролей в безопасности
What privileges did you have when you logged in?
Solution 2

Дополнительно: Хромакей или для чего используют зеленый экран при съемке видео? - Записки Славного парня — LiveJournal

Solution 1

mysql> CREATE USER 'root'@'%' IDENTIFIED BY 'PASSWORD';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;
mysql> FLUSH PRIVILEGES;

Caution about the security risks about WITH GRANT OPTION, see:

Solution 4

On your mysql server machine, do mysql -u root -p, then enter your password for root to login.

mysql> CREATE USER 'root'@'10.154.10.241' IDENTIFIED BY 'Abcdef123!@#';

mysql> GRANT ALL ON *.* TO 'root'@'10.154.10.241';

```
FLUSH PRIVILEGES;
```
Restart the mysqld service:
```
sudo service mysqld restart
```
Confirm that the server has successfully restarted:
```
sudo service mysqld status
```

If the steps above were executed without any error, you can now access to the mysql server from a remote machine using root.

Solution 5

mysql --version
mysql Ver 8.0.16 for Linux on x86_64 (MySQL Community Server - GPL)

What worked for me:

mysql> CREATE USER 'username'@'localhost' IDENTIFIED BY 'desired_password';
mysql> GRANT ALL PRIVILEGES ON db_name.* TO 'username'@'localhost' WITH GRANT OPTION;

Response in both queries:

Query OK, O rows affected (0.10 sec*)

20 : 53

11 : 22

19 : 51

PnTutorials by Pradnyankur Nikam

01 : 16

05 : 18

Oracle Database Solutions

01 : 04

Comments

Solution 3

> mysql> CREATE USER 'foo'@'localhost' IDENTIFIED WITH mysql_native_password BY 'bar';

> mysql> GRANT ALL PRIVILEGES ON database_name.* TO'foo'@'localhost';

Note: database_name is the database that you want to have privileges, . means all on all

mysql> mysql -u foo -p

4) Make sure your initial connection from Sequelize is set to foo with pw bar.

3 Управление пользователями в MySQL

4.3.1 Синтаксис `GRANT` и `REVOKE`

GRANT priv_type [(column_list)] [, priv_type [(column_list)] ...] ON {tbl_name|*|*.*|db_name.*} TO user_name [IDENTIFIED BY 'password'] [, user_name [IDENTIFIED BY 'password'] ...] [REQUIRE {SSL|X509} [ISSUER issuer] [SUBJECT subject]] [WITH GRANT OPTION]
REVOKE priv_type [(column_list)] [, priv_type [(column_list)] ...] ON {tbl_name|*|*.*|db_name.*} FROM user_name [, user_name ...]

GRANT реализован в MySQL Version 3.22.11 или позже. Для более
ранних версий MySQL инструкция GRANT не делает ничего.

Команды GRANT и REVOKE позволяют администраторам
системы создавать пользователей, предоставлять и отменять права на
MySQL-пользователей в четырех уровнях привилегий:

Глобальный уровень (Global level): Глобальные привилегии обращаются ко всем базам данных на данном сервере.
Эти привилегии сохранены в таблице mysql.user.
Уровень баз данных (Database level): Привилегии баз данных обращаются ко всем таблицам в данной базе данных.
Эти привилегии сохранены в таблицах mysql.db и
mysql.host.
Уровень таблиц (Table level): Привилегии таблиц обращаются ко всем столбцам в данной таблице. Эти
привилегии сохранены в таблице mysql.tables_priv.
Уровень столбцов (Column level): Привилегии столбцов обращаются к одиночным столбцам в данной таблице. Эти
привилегии сохранены в таблице mysql.columns_priv.

Если Вы даете привилегии пользователю, который не существует, он будет
автоматически создан. За примерами по работе GRANT обратитесь к
разделу «4.3.5 Добавление новых
пользователей к MySQL«.

Для инструкций GRANT и REVOKE аргумент
priv_type может быть определен как любой из следующего списка:

ALL PRIVILEGES FILE RELOAD ALTER INDEX SELECT
CREATE INSERT SHUTDOWN DELETE PROCESS UPDATE
DROP REFERENCES USAGE

ALL представляет собой синоним для
ALL PRIVILEGES. REFERENCES пока не реализовано.
USAGE в настоящее время представляет собой синоним для
«no privileges». Это может быть применено, когда Вы хотите создать
пользователя, который не имеет никаких привилегий.

Чтобы отменять привилегию предоставленную командой grant,
используйте значение priv_type параметра GRANT OPTION:

REVOKE GRANT OPTION ON ... FROM ...;

Значения priv_type, которые Вы можете определять для таблицы:
SELECT, INSERT, UPDATE,
DELETE, CREATE, DROP,
GRANT, INDEX и ALTER.

Значения priv_type, которые Вы можете определять для столбца
(то есть, когда Вы используете аргумент column_list):
SELECT, INSERT и UPDATE.

Вы можете устанавливать глобальные привилегии, используя синтаксис
ON *.*. Вы можете устанавливать привилегии базы данных,
используя синтаксис ON db_name.*. Если Вы определяете
ON *, и Вы имеете текущую базу данных, Вы установите привилегии
именно для этой базы данных. ПРЕДУПРЕЖДЕНИЕ: Если Вы
определяете ON *, и Вы не имеете текущей базы данных,
Вы будете воздействовать на глобальные привилегии!

mysql> SELECT Host,User FROM mysql.user WHERE User='';

В настоящий момент GRANT поддерживает имена хоста, базы
данных, таблицы и столбца длиной только до 60 символов. Имя пользователя
может быть длиной до 16 символов.

Привилегии для столбца могут быть вычислены следующим образом:

global privileges OR
(database privileges AND host privileges) OR
table privileges OR column privileges

В большинстве случаев Вы предоставляете права пользователю только в одном
из уровней привилегии, так что обычно это просто. Детали проверяющей
привилегии процедуры подробно рассмотрены в разделе
«4.2 Общие проблемы защиты и система
привилегий доступа MySQL«.

В MySQL Version 3.22.12 или позже, если новый
пользователь создан, или если Вы имеете глобальные привилегии, пароль
пользователя будет установлен в пароль, определенный предложением
IDENTIFIED BY, если оно задано. Если пользователь уже имел
пароль, он будет заменен новым.

ПРЕДУПРЕЖДЕНИЕ: Если Вы создаете нового пользователя, но
не определяете предложение IDENTIFIED BY, пользователь не имеет
никакого пароля. Это опасно.

Пароли также могут быть установлены командой SET PASSWORD.
Подробности в разделе «5.5.6 Синтаксис
SET«.

Если Вы предоставляете привилегии для базы данных, запись в таблице
mysql.db будет создана, если необходимо. Когда все привилегии
для базы данных будут удалены с помощью REVOKE, эта запись тоже
будет удалена автоматически.

Если пользователь не имеет привилегий на таблице, данная таблица не
отображается вообще, когда пользователь запрашивает список таблиц (например,
инструкцией SHOW TABLES).

Предложение WITH GRANT OPTION дает пользователю способность
передать другим пользователям любые привилегии, которые этот пользователь
имеет в определенном уровне доступа. Вы должны быть внимательным к тому, кому
Вы даете привилегию grant, поскольку два пользователя с
различными привилегиями могут быть способны соединить свои привилегии!

Дополнительно: Правда или нет? Синий свет экрана вреден - 4PDA

Вы не можете предоставлять другому пользователю привилегию, которую Вы не
имеете сами. Привилегия grant позволяет Вам передавать
только те привилегии, которыми Вы реально обладаете.

Знайте, что, когда Вы предоставляете пользователю привилегию
grant в специфическом уровне привилегии, любые привилегии,
которые пользователь уже имеет (или получит в будущем!) в этом уровне также
могут передаваться этим пользователем кому угодно. Предположите, что Вы
предоставляете пользователю привилегию insert на базе
данных. Если Вы затем предоставляете привилегию select на
базе данных и определяете WITH GRANT OPTION, пользователь сможет
передавать не только привилегию select, но и
insert! Если Вы затем предоставляете ему еще и привилегию
update на базе данных, этот пользователь сможет передавать
insert, select и update.

Вы не должны предоставлять привилегию alter нормальному
пользователю. Если Вы это сделаете, пользователь может попробовать
разрушить систему привилегии, переименовывая таблицы!

Обратите внимание, что, если Вы используете привилегии столбца или таблицы
даже для одного пользователя, сервер исследует привилегии столбца и таблицы
для всех пользователей, и это замедлит немного MySQL.

Самые большие различия между ANSI SQL и MySQL
версиями оператора GRANT:

В MySQL привилегии даны для комбинаций username+hostname, а не только
для одного username.
ANSI SQL не имеет привилегий глобального уровня и базы данных и не
поддерживает все типы привилегии, которые предоставляет MySQL. Зато MySQL не
поддерживает привилегии TRIGGER, EXECUTE и
UNDER из ANSI SQL.
Привилегии ANSI SQL структурированы иерархически. Если Вы удаляете
пользователя, все привилегии, которые этот пользователь предоставил,
отменяются автоматически. В MySQL предоставленные привилегии автоматически не
отменяются, Вы должны отменить их непосредственно, если это необходимо.
Если Вы в MySQL имеете предоставление INSERT только на части
столбцов в таблице, Вы можете выполнять инструкции INSERT
относительно таблицы: столбцы, для которых Вы не имеете привилегию
INSERT будут установлены к их значениям по умолчанию. ANSI SQL
требует, чтобы Вы имели привилегию INSERT на всех столбцах.
Когда Вы удаляете таблицу в ANSI SQL, все привилегии для таблицы
отменяются. Если Вы отменяете привилегию в ANSI SQL, все привилегии, которые
предоставлялись основанными на этой привилегии, также отменяются. В MySQL
привилегии могут быть удалены только явным вызовом команды
REVOKE или правкой таблиц предоставления привилегий MySQL.

4.3.2 Имена и пароли пользователей MySQL

Имеются несколько различий между использованием имен и паролей MySQL и
Unix или Windows:

Имена пользователей, которые используются MySQL для опознавательных
целей, не имеют никакого отношения к именам пользователей Unix или Windows.
Большинство клиентов MySQL по умолчанию пробует войти, используя текущее имя
пользователя Unix как имя пользователя MySQL, но это только для удобства.
Программы-клиенты позволяют задавать различные имена опциями
-u или --user. Это означает, что Вы не можете
делать базу данных безопасной всегда, если все имена пользователей MySQL не
имеют паролей. Любой может пытаться соединиться с сервером, используя любое
имя, и это получиться, если использовано имя, которое не имеет пароля.
Имена пользователей MySQL могут быть длиной до 16 символов. В Unix обычно
длина не превышает 8 символов.
Пароли MySQL тоже не имеют никакого отношения к паролям в Unix. Очень
советую сделать их разными: тогда взлом пароля Unix не будет означать взлома
пароля для доступа к базам данных и, наоборот.
MySQL шифрует пароли, используя алгоритм, отличный от алгоритма
шифрования в Unix. Обратите внимание, что, даже если пароль сохранен в
зашифрованном виде, знания именно зашифрованного пароля вполне достаточно,
чтобы соединиться с сервером MySQL!

Пользователи MySQL и их привилегии обычно создаются командой
GRANT. Подробности в разделе «4.3.1
Синтаксис GRANT и REVOKE«.

Когда Вы входите в систему на сервере MySQL с помощью клиента командной
строки, Вы должны определить пароль с помощью опции
--password=your-password. Подробности изложены в разделе
«4.2.7 Связь с сервером MySQL«:

mysql --user=monty --password=guess database_name

Если Вы хотите, чтобы Вас запросили относительно пароля, Вы должны
использовать аргумент --password:

mysql --user=monty --password database_name

Или в кратком виде:

mysql -u monty -p database_name

Обратите внимание, что в последнем примере пароль НЕ
database_name.

Если Вы хотите использовать опцию -p, чтобы задать пароль,
сделайте это так:

mysql -u monty -pguess database_name

На некоторых системах библиотечный вызов, который MySQL использует для
запроса пароля, автоматически урежет пароль до длины в 8 символов. Внутренне
MySQL не имеет ограничений на длину пароля.

4.3.3 Когда привилегии вступают в силу

При запуске mysqld все содержание таблиц предоставления
привилегий читается в память и становится действующим.

Модификации к таблицам предоставления, которые Вы выполняете используя
GRANT, REVOKE или SET PASSWORD
вступают в силу немедленно.

Когда сервер обращает внимания, что таблицы предоставления были изменены,
на существующие подключения пользователей это воздействуют следующим образом:

Изменения привилегий столбцов и таблиц воздействуют, начиная со
следующего запроса пользователя.
Изменения привилегии базы данных воздействуют в следующем вызове команды
USE db_name.

Изменения глобальных привилегий и пароля воздействуют, когда пользователь
соединится в следующий раз.

4.3.4 Установка начальных привилегий MySQL

После установки MySQL Вы устанавливаете начальные привилегии доступа
вызовом скрипта scripts/mysql_install_db. Подробности в разделе
«2.3.1 Обзор быстрой установки«.
Скрипт mysql_install_db запускает сервер mysqld и
устанавливает следующие привилегии:

MySQL-пользователь root создан как суперпользователь,
который может делать что угодно. Подключения должны быть обязательно с
локального компьютера. ОБРАТИТЕ ВНИМАНИЕ: начальный пароль
для root пуст, так что любой может соединяться как
root без пароля и предоставлять все привилегии.
Анонимный пользователь создан, что может делать что-нибудь с базами
данных, которые имеют имя test, или их имя начинается с
test_. Подключения должны быть с локального компьютера. Это
означает, что любой локальный пользователь может соединяться без пароля и
обрабатываться как анонимный пользователь.
Другие привилегии отклонены. Например, нормальные пользователи не могут
mysqladmin shutdown или mysqladmin processlist.

ОБРАТИТЕ ВНИМАНИЕ: заданные по умолчанию привилегии иные
для Windows. Подробности в разделе «
2.6.2.3 Запуск MySQL под Windows«.

Первое, что следует сделать, это определить пароль для MySQL-пользователя
root. Вы можете сделать это следующим образом (обратите
внимание, что Вы определяете пароль, используя функцию
PASSWORD()):

shell> mysql -u root mysql
mysql> UPDATE user SET Password=PASSWORD('new_password') WHERE user='root';
mysql> FLUSH PRIVILEGES;

Вы можете, в MySQL версии 3.22 и выше, использовать инструкцию
SET PASSWORD:

shell> mysql -u root mysql
mysql> SET PASSWORD FOR root=PASSWORD('new_password');

shell> mysqladmin -u root password new_password

Только пользователи с доступом на чтение/запись к базе данных
mysql могут изменять пароль для других пользователи. Все
нормальные пользователи (не анонимные) могут менять только их собственный
пароль с помощью вышеупомянутых команд или вызовом
SET PASSWORD=PASSWORD('new password').

Как только пароль root был установлен, Вы должны использовать
его при соединении с сервером.

Изучение скрипта scripts/mysql_install_db весьма пригодится
при сборе информации по созданию и настройке других пользователей.

Если Вы хотите, чтобы начальные привилегии были иными, чем те, которые
только что я описал, Вы можете изменять скрипт mysql_install_db
прежде, чем Вы его выполните.

Чтобы освежить таблицы
предоставления привилегий полностью (пересоздать их заново), удалите все
файлы `.frm', `.MYI' и `.MYD' в каталоге,
содержащем базу данных mysql. Это каталог с именем
`mysql' в каталоге баз данных, который будет перечислен, когда
Вы выполняете mysqld --help. Затем выполните скрипт
mysql_install_db, возможно, после его редактирования, чтобы
иметь те привилегии, которые Вы хотите установить.

ОБРАТИТЕ ВНИМАНИЕ: Для MySQL версий старше, чем 3.22.10,
Вы не должны удалить файлы `.frm'. Если Вы случайно сделаете это, Вы
должны скопировать их обратно из Вашего дистрибутива MySQL перед выполнением
mysql_install_db.

4.3.5 Добавление новых пользователей в MySQL

Вы можете добавлять пользователей двумя различными путями: используя
инструкции GRANT или непосредственно управляя таблицами MySQL.

Дополнительно: Участник root

Примеры ниже показывают, как использовать клиент mysql, чтобы
установить новых пользователей. Эти примеры принимают, что привилегии
установлены согласно значениям по умолчанию, описанным в предыдущем разделе.
Это означает, что, чтобы делать изменения, Вы должны быть зарегестрированы на
той же самой машине, где работает сервер mysqld, Вы должны
соединиться как MySQL-пользователь root, и он должен иметь
привилегию insert для базы данных mysql, а
также административную привилегию reload. Также, если Вы
изменили пароль для root, Вы должны определить его в командах.

Вы можете добавлять новых пользователей, выдавая инструкции
GRANT:

shell> mysql --user=root mysql
mysql> GRANT ALL PRIVILEGES ON *.* TO monty@localhost IDENTIFIED BY 'some_pass' WITH GRANT OPTION;
mysql> GRANT ALL PRIVILEGES ON *.* TO monty@"%" IDENTIFIED BY 'some_pass' WITH GRANT OPTION;
mysql> GRANT RELOAD,PROCESS ON *.* TO admin@localhost;
mysql> GRANT USAGE ON *.* TO dummy@localhost;

Эти инструкции GRANT устанавливают трех новых пользователей:

monty: Полный суперпользователь, может соединяться с сервером отовсюду, но
должен использовать пароль 'some_pass'. Обратите внимание, что
Вы должны выдать инструкции GRANT для
monty@localhost и monty@"%". Если мы не добавляем
запись с localhost, анонимный пользователь для
localhost, который создан mysql_install_db, будет
иметь приоритет, когда мы соединяемся с локального компьютера потому, что это
имеет более специфическое поле Host, и таким образом окажется
первым в отсортированной таблице user.
admin: Пользователь может соединяться с localhost без пароля, и ему
предоставляют административные привилегии reload и
process. Это позволяет пользователю выполнять команды
mysqladmin reload, mysqladmin refresh и
mysqladmin flush-*, также как mysqladmin processlist. Никакие связанные с базой данных привилегии ему не
предоставляются. Их можно предоставить позже, выдавая
дополнительные инструкции GRANT.
dummy: Пользователь может соединяться без пароля, но только с локального
компьютера. Глобальные привилегии все установлены в 'N'.
Привилегия USAGE позволяет Вам создавать пользователя без
привилегий. Принимается, что Вы предоставите ему специфические для базы
данных привилегии позже.

Вы можете также добавлять то же
самое, обращаясь к информации непосредственно, выдавая инструкции
INSERT и затем сообщая серверу перезагрузить таблицы:

shell> mysql --user=root mysql
mysql> INSERT INTO user VALUES('localhost','monty',PASSWORD('some_pass'), 'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');
mysql> INSERT INTO user VALUES('%','monty',PASSWORD('some_pass'), 'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');
mysql> INSERT INTO user SET Host='localhost',User='admin', Reload_priv='Y', Process_priv='Y';
mysql> INSERT INTO user (Host,User,Password) VALUES('localhost','dummy','');
mysql> FLUSH PRIVILEGES;

Следующий пример добавляет пользователя custom, который может
соединяться с компьютеров localhost, server.domain
и whitehouse.gov. Он хочет обращаться к базе данных
bankaccount только с машины localhost, базе данных
expenses только с машины whitehouse.gov, а к базе
данных customer со всех трех компьютеров. Он хочет использовать
пароль stupid для всех систем.

Чтобы установить привилегии этого пользователя, используя инструкцию
GRANT, выполните эти команды:

shell> mysql --user=root mysql
mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP ON bankaccount.* TO custom@localhost IDENTIFIED BY 'stupid';
mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP ON expenses.* TO custom@whitehouse.gov IDENTIFIED BY 'stupid';
mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP ON customer.* TO custom@'%' IDENTIFIED BY 'stupid';

Чтобы установить привилегии пользователя, изменяя таблицы предоставления
привилегий непосредственно, выполните эти команды (обратите внимание на вызов
FLUSH PRIVILEGES в конце):

shell> mysql --user=root mysql
mysql> INSERT INTO user (Host,User,Password) VALUES('localhost','custom',PASSWORD('stupid'));
mysql> INSERT INTO user (Host,User,Password) VALUES('server.domain','custom',PASSWORD('stupid'));
mysql> INSERT INTO user (Host,User,Password) VALUES('whitehouse.gov','custom',PASSWORD('stupid'));
mysql> INSERT INTO db (Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv, Create_priv,Drop_priv) VALUES ('localhost','bankaccount','custom','Y','Y','Y','Y','Y','Y');
mysql> INSERT INTO db (Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv, Create_priv,Drop_priv) VALUES ('whitehouse.gov','expenses','custom','Y','Y','Y','Y','Y','Y');
mysql> INSERT INTO db (Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv, Create_priv,Drop_priv) VALUES('%','customer','custom','Y','Y','Y','Y','Y','Y');
mysql> FLUSH PRIVILEGES;

Если Вы хотите давать специфический доступ пользователю с любой машины в
данном домене, Вы можете выдать инструкцию GRANT таким образом:

mysql> GRANT ... ON *.* TO myusername@"%.mydomainname.com" IDENTIFIED BY 'mypassword';

Чтобы сделать ту же самую работу, изменяя таблицы предоставления
привилегий непосредственно, сделайте так:

mysql> INSERT INTO user VALUES ('%.mydomainname.com', 'myusername', PASSWORD('mypassword'),...);
mysql> FLUSH PRIVILEGES;

4.3.6 Установка паролей

В большинстве случаев Вы должны использовать GRANT, чтобы
установить Ваших пользователей и их пароли, так что следующее касается только
пользоваателей профессионального уровня.

shell> mysql -u root mysql
mysql> INSERT INTO user (Host,User,Password) VALUES('%','jeffrey','biscuit');
mysql> FLUSH PRIVILEGES;

shell> mysql -u jeffrey -pbiscuit test
Access denied

mysql> INSERT INTO user (Host,User,Password) VALUES('%','jeffrey',PASSWORD('biscuit'));

Вы должны также использовать функцию PASSWORD(), когда Вы
применяете инструкции SET PASSWORD:

mysql> SET PASSWORD FOR jeffrey@"%" = PASSWORD('biscuit');

mysql> GRANT USAGE ON *.* TO jeffrey@"%" IDENTIFIED BY 'biscuit';

или примерно так:

shell> mysqladmin -u jeffrey password biscuit

ОБРАТИТЕ ВНИМАНИЕ: PASSWORD() не выполняет
шифрование пароля так, как это делается в Unix.

4.3.7 Хранение паролей в безопасности

Нецелесообразно определять Ваш пароль так, что его могут увидеть другие
пользователи. Методы, которые Вы можете использовать, чтобы определить Ваш
пароль, когда Вы выполняете программы-клиенты, перечислены ниже, наряду с
оценкой рисков каждого метода:

Никогда не давайте нормальному пользователю доступ к таблице
mysql.user. Знание зашифрованного пароля для пользователя делает
возможным вход в систему под именем этого пользователя.
Используйте опции -pyour_pass или
--password=your_pass в командной строке. Это удобно, но опасно
потому, что Ваш пароль становится видимым программам состояния системы
(например, ps), которые могут вызываться другими пользователями,
чтобы отобразить командные строки. Клиенты MySQL обнуляют командные строки,
чтобы их не было видно в процессе выполнения, но имеется краткий интервал, в
течение которого значение является видимым.
Используйте опции -p или --password (без
определенного значения your_pass). В этом случае
программа-клиент запросит пароль с терминала:
```
shell> mysql -u user_name -p
Enter password: ********
```
Символы * представляют Ваш пароль. Это более безопасно, чем
определить пароль прямо в командной строке потому, что это не видно другим
пользователям. Однако, этот метод ввода пароля подходит только для программ,
которые Вы выполняете в интерактивном режиме. Если Вы хотите вызывать клиента
из скрипта, который выполняется не в интерактивном режиме, не имеется никакой
возможности ввести пароль с терминала. На некоторых системах Вы можете даже
обнаружить, что первая строка Вашего скрипта читается и неправильно
интерпретируется как Ваш пароль!
Сохраните Ваш пароль в файле конфигурации. Например,
Вы можете перечислить пароль в разделе [client] файла
.my.cnf в Вашем основном каталоге:
```
[client]
password=your_pass
```
Если Вы сохраняете Ваш пароль в файле .my.cnf, не должен быть
доступен на чтение (тем более на запись) никому, кроме Вас. Удостоверьтесь,
что режим доступа файла 400 или 600.
Вы можете сохранять Ваш пароль в системной переменной
MYSQL_PWD, но этот метод должен рассматриваться как чрезвычайно
опасный и не должен использоваться. Некоторые версии ps включают
опцию, чтобы отобразить среду управления процессами. Ваш пароль будет видим
всем желающим использовать такую команду. Даже на системах без такой версии
ps неблагоразумно считать, что не имеется никакого другого
метода наблюдать среды процесса.

В целом, самые безопасные методы состоят в том, чтобы иметь запрос
программы-клиента для пароля или определять пароль в правильно защищенном
файле личных настроек .my.cnf.

What privileges did you have when you logged in?

SHOW GRANTS;

Feb 17, 2012 : MySQL : Why are there «test» entries in mysql.db?
Feb 17, 2012 : What is the mysql.db table used for?
Jan 18, 2012 : MySQL error: Access denied for user ‘a’@’localhost’ (using password: YES)

echo "SET PASSWORD FOR root@localhost=PASSWORD('password');" > /var/lib/mysql/rootpwd.sql
service mysql restart
rm -f /var/lib/mysql/rootpwd.sql

Give it a Try !!!

Solution 2

I see a lot of (wrong) answers, it is just as simple as this:

USE mysql;
CREATE USER 'user'@'localhost' IDENTIFIED BY '[email protected]';
GRANT ALL ON *.* TO 'user'@'localhost';
FLUSH PRIVILEGES;

Alternative privileges (be careful and remember the least-privilege principle):

-- Grant user permissions to all tables in my_database from localhost --
GRANT ALL ON my_database.* TO 'user'@'localhost';
-- Grant user permissions to my_table in my_database from localhost --
GRANT ALL ON my_database.my_table TO 'user'@'localhost';
-- Grant user permissions to all tables and databases from all hosts --
GRANT ALL ON *.* TO 'user'@'*';