04 Oct 2013

GRANT PRIVILEGES in mysql

Um eine MySQL-Datenbank mit einen MySQL-Benutzer anlegen und ihm Zugriff auf eine Datenbanktabelle zu geben, ist folgendes am MySQL-Command-Promt einzugeben:

Benutzer mit Rechten (Privileges) anlegen

 chrissie ~ $ mysql -u root -p

-- Datenbank erstellen
CREATE DATABASE `web38_2`;

-- Benutzer anlegen
CREATE USER 'web38'@'localhost' IDENTIFIED BY 'secret-pw';

-- Alle Rechte für diese eine Datenbank vergeben
GRANT ALL PRIVILEGES ON `web38_2`.* TO 'web38'@'localhost';

-- Rechte neu laden
FLUSH PRIVILEGES;

Wenn ein Benutzer nur bestimmte Rechte auf einer Datenbank haben soll:

GRANT 
  SELECT, INSERT, UPDATE, DELETE, CREATE, 
  DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, 
  LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, 
  CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER 
ON `web38_2`.* 
TO 'web38'@'localhost';

FLUSH PRIVILEGES;

will man als Passwort den Hash aus der Tabelle mysql.User verwenden, z. B. bei Migrationen, gilt diese Syntax:

CREATE USER 'web38'@'localhost' IDENTIFIED VIA mysql_native_password 
  USING '*5674E91865B67AB3ED6F9050F5729446F17CB648';

-- Danach Rechte zuweisen wie gewohnt:
GRANT ALL PRIVILEGES ON `web38_2`.* TO 'web38'@'localhost';

Sicherheit im Shell-Befehl: Bei mysql -u root -pmysqlrootpassword steht das Passwort in deiner Bash-History. Wenn du nur -p schreibst, fragt MySQL das Passwort ab, ohne dass es irgendwo gespeichert wird.
Trennung von CREATE USER und GRANT: Das alte GRANT ... IDENTIFIED BY 'pw' stammte aus MySQL 5.x. Modernes SQL trennt die Erstellung des Users (CREATE USER) strikt von der Rechtevergabe (GRANT).

Fehlerbehebung

Kommt es beim anschließenden flush privileges zu folgendem Fehler:

 mysql> flush privileges;
 ERROR 1146 (42S02): Table 'mysql.procs_priv' doesn't exist

Dann hat man in der Zwischenzeit ein MySql-Update durchgeführt. Es ist auf der Kommandozeile folgendes einzugeben:

 chrissie ~ $ mysql_fix_privilege_tables --password=mysqlrootpassword
 This script updates all the mysql privilege tables to be usable by
 the current version of MySQL

Import eines MySQL-Dumps:

Importieren einer Datenbank von der Kommandozeile aus:

 chrissie ~ $ mysql -u web38 -psecret-pw web38_2 < web38_2-dump.sql
 mysql: [Warning] Using a password on the command line interface can be insecure.

Erstellen eines MySQL-Dumps

Exportieren einer Datenbank von der Kommandozeile aus:

 chrissie ~ $ mysqldump -u web38 -psecret-pw web38_2 > web38_2-dump.sql
 mysql: [Warning] Using a password on the command line interface can be insecure.

Fehler beim Anlegen eines Users

Diese Fehlermeldung

MariaDB [(none)]> create user 'froxlor'@'localhost' identified by 'secret-pw';
ERROR 1396 (HY000): Operation CREATE USER failed for 'froxlor'@'localhost'

besagt meistens, dass es den User schon gibt. Lass uns das schnell checken:

MariaDB [(none)]> SELECT Host, User, Password FROM mysql.user WHERE user = 'froxlor';
+-----------+---------+-------------------------------------------+
| Host      | User    | Password                                  |
+-----------+---------+-------------------------------------------+
| localhost | froxlor | *5694E33F1D3B292999D5536CB6E9D2BE33CE5E13 |
| 127.0.0.1 | froxlor | *5694E33F1D3B292999D5536CB6E9D2BE33CE5E13 |
+-----------+---------+-------------------------------------------+
2 rows in set (0.002 sec)

Wenn das soweit passt, dann einfach nur das Passwort neu setzen:

MariaDB [(none)]> ALTER USER 'froxlor'@'127.0.0.1' IDENTIFIED BY 'password';
Query OK, 0 rows affected (0.005 sec)

MariaDB [(none)]> ALTER USER 'froxlor'@'localhost' IDENTIFIED BY 'password';
Query OK, 0 rows affected (0.004 sec)

MariaDB [(none)]> SELECT Host, User, Password FROM mysql.user WHERE user = 'froxlor';
+-----------+---------+-------------------------------------------+
| Host      | User    | Password                                  |
+-----------+---------+-------------------------------------------+
| localhost | froxlor | *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 |
| 127.0.0.1 | froxlor | *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 |
+-----------+---------+-------------------------------------------+
2 rows in set (0.002 sec)

MariaDB [(none)]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.007 sec)