Pora zająć się użytkownikami i ich uprawnieniami w bazie MySQL
Jakby trzeba było na początku uruchomić bazę danych to:
mysqld --defaults-file=/mysqldata/data/my.cnf &
Jesteśmy zalogowani do bazy, ale na przykład nie pamiętamy na jakiego usera. Żeby to sprawdzić wydajemy polecenie:
mysql> SELECT USER(),CURRENT_USER();
+----------------+----------------+ | USER() | CURRENT_USER() | +----------------+----------------+ | root@localhost | root@localhost | +----------------+----------------+ 1 row in set (0.00 sec)
Możemy też dać polecenie STATUS, gdzie jest podany CURRENT USER:
MariaDB [testowadb1]> status
-------------- mysql Ver 14.14 Distrib 5.7.17, for Linux (x86_64) using EditLine wrapper Connection id: 4 Current database: Current user: root@localhost SSL: Not in use Current pager: stdout Using outfile: '' Using delimiter: ; Server version: 5.7.17 MySQL Community Server (GPL) Protocol version: 10 Connection: Localhost via UNIX socket Server characterset: cp1250 Db characterset: cp1250 Client characterset: utf8 Conn. characterset: utf8 UNIX socket: /mysqldata/data/baza_mysql.sock Uptime: 4 min 40 sec Threads: 2 Questions: 11 Slow queries: 0 Opens: 111 Flush tables: 1 Open tables: 27 Queries per second avg: 0.039 --------------
Jakbyśmy chcieli zobaczyć co dany użytkownik wykonuje:
mysql> show full processlist;
+----+-----------------+-----------+------+---------+------+------------------------+-----------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+-----------------+-----------+------+---------+------+------------------------+-----------------------+ | 1 | event_scheduler | localhost | NULL | Daemon | 314 | Waiting on empty queue | NULL | | 4 | root | localhost | NULL | Query | 0 | starting | show full processlist | +----+-----------------+-----------+------+---------+------+------------------------+-----------------------+ 2 rows in set (0.00 sec)
Sprawdzamy uprawnienia dla naszego użytkownika ROOT
mysql> show grants for root@localhost;
+---------------------------------------------------------------------+ | Grants for root@localhost | +---------------------------------------------------------------------+ | GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION | | GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION | +---------------------------------------------------------------------+ 2 rows in set (0.00 sec)
Informacje o użytkownikach
mysql> select user, host from mysql.user;
+-----------+-----------+ | host | user | +-----------+-----------+ | mysql.sys | localhost | | root | localhost | +-----------+-----------+ 2 rows in set (0.00 sec)
Jakbyśmy chcieli np zobaczyć uprawnienia userów do odczytu, zapisu itd to możemy rozszerzyć zapytanie;
mysql> select user,host, select_priv, insert_priv, update_priv, delete_priv from mysql.user;
+-----------+-----------+-------------+-------------+-------------+-------------+ | user | host | select_priv | insert_priv | update_priv | delete_priv | +-----------+-----------+-------------+-------------+-------------+-------------+ | root | localhost | Y | Y | Y | Y | | mysql.sys | localhost | N | N | N | N | +-----------+-----------+-------------+-------------+-------------+-------------+ 2 rows in set (0.00 sec)
Tych uprawnień jest naprawdę sporo 🙂
mysql> desc mysql.user;
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Host | char(60) | NO | PRI | | |
| User | char(32) | NO | PRI | | |
| 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) | NO | | mysql_native_password | |
| authentication_string | text | YES | | NULL | |
| password_expired | enum('N','Y') | NO | | N | |
| password_last_changed | timestamp | YES | | NULL | |
| password_lifetime | smallint(5) unsigned | YES | | NULL | |
| account_locked | enum('N','Y') | NO | | N | |
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
45 rows in set (0.00 sec)
najważniejsze to:
• SELECT– odczytanie danych z tabeli
• INSERT – wstawianie danych do tabeli
• UPDATE – modyfikowanie danych w tabeli
• DELETE – usunięcie danych z tabeli
• REFERENCE – odwoływanie się do innych tabel
• CREATE – tworzenie nowych tabel i baz danych
• DROP – usuwanie tabel oraz baz danych
• ALL PRIVILEGES – wszystkie dostępne uprawnienia
W bazie MySQL jest trochę inaczej (można odpytać np o hasło):
MariaDB [(none)]> SELECT user, host, password, select_priv, insert_priv, shutdown_priv, grant_priv FROM mysql.user where user like 'root';
+------+-----------------------+-------------------------------------------+-------------+-------------+---------------+------------+ | user | host | password | select_priv | insert_priv | shutdown_priv | grant_priv | +------+-----------------------+-------------------------------------------+-------------+-------------+---------------+------------+ | root | localhost | *54958E764CE10E50764C2EECBB71D01F08549980 | Y | Y | Y | Y | | root | localhost.localdomain | | Y | Y | Y | Y | | root | 127.0.0.1 | | Y | Y | Y | Y | | root | ::1 | | Y | Y | Y | Y | +------+-----------------------+-------------------------------------------+-------------+-------------+---------------+------------+ 4 rows in set (0.01 sec)
I to samo wyświetlone w wierszach, a nie w kolumnach dla usera ROOT na host LOCALHOST:
mysql> select * from mysql.user where user like 'root' \G;
*************************** 1. row ***************************
Host: localhost
User: root
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
ssl_type:
ssl_cipher:
x509_issuer:
x509_subject:
max_questions: 0
max_updates: 0
max_connections: 0
max_user_connections: 0
plugin: mysql_native_password
authentication_string: *E74858DB86EBA20BC33D0AECAE8A8108C56B17FA
password_expired: N
password_last_changed: 2017-04-11 20:22:52
password_lifetime: NULL
account_locked: N
1 row in set (0.00 sec)
ERROR:
No query specified
W systemie mamy usera MYSQL i takiego samego użytkownika założymy w bazie (zgodnie z zaleceniami bezpieczeństwa)
Aby wygenerować skrypt do tworzenia użytkownika wydajemy polecenie:
mysql> show create user 'root'@'localhost';
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | CREATE USER for root@localhost | +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | CREATE USER 'root'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*E74858DB86EBA20BC33D0AECAE8A8108C56B17FA' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK | +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
za jego pomocą stworzymy usera MYSQL posiadającego takie same uprawnienia jak ROOT:
mysql> CREATE USER 'mysql'@'%' IDENTIFIED WITH 'mysql_native_password' AS '*E74858DB86EBA20BC33D0AECAE8A8108C56B17FA';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for 'mysql'@'localhost';
ERROR 1141 (42000): There is no such grant defined for user 'mysql' on host 'localhost'
A to dlatego że mamy zdefiniowanego usera 'MYSQL’@’%' czyli usera za pomocą którego możemy się podłączyć z każdego miejsca z sieci a nie tylko z lokalnego serwer (localhost)
mysql> show grants for 'mysql'@'%';
+--------------------------------------+ | Grants for newuser2@% | +--------------------------------------+ | GRANT USAGE ON *.* TO 'newuser2'@'%' | +--------------------------------------+ 1 row in set (0.00 sec)
Możemy się już zalogować do bazy ale user widzi tylko jedną bazę:
mysql> show databases;
+--------------------+ | Database | +--------------------+ | information_schema | +--------------------+ 1 row in set (0.00 sec)
Porównujemy uprawnienia obu userów:
mysql> select user,host, select_priv, insert_priv, update_priv, delete_priv from mysql.user;
+-----------+-----------+-------------+-------------+-------------+-------------+ | user | host | select_priv | insert_priv | update_priv | delete_priv | +-----------+-----------+-------------+-------------+-------------+-------------+ | root | localhost | Y | Y | Y | Y | | mysql.sys | localhost | N | N | N | N | | mysql | % | N | N | N | N | +-----------+-----------+-------------+-------------+-------------+-------------+ 3 rows in set (0.00 sec)
Generujemy skrypt uprawnień dla usera ROOT:
mysql> show grants for 'root'@'localhost';
+---------------------------------------------------------------------+ | Grants for root@localhost | +---------------------------------------------------------------------+ | GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION | | GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION | +---------------------------------------------------------------------+ 2 rows in set (0.00 sec)
i na jego podstawie tworzymy polecenia przyznające uprawnienia dla naszego nowego usera:
mysql> GRANT ALL PRIVILEGES ON *.* TO 'mysql'@'%' WITH GRANT OPTION;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> GRANT PROXY ON ''@'' TO 'mysql'@'%' WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)
Po zalogowaniu widać wszystkie bazy i user otrzymał uprawnienia takie same jak ROOT:
mysql> select user,host, select_priv, insert_priv, update_priv, delete_priv from mysql.user;
+-----------+-----------+-------------+-------------+-------------+-------------+ | user | host | select_priv | insert_priv | update_priv | delete_priv | +-----------+-----------+-------------+-------------+-------------+-------------+ | root | localhost | Y | Y | Y | Y | | mysql.sys | localhost | N | N | N | N | | mysql | % | Y | Y | Y | Y | +-----------+-----------+-------------+-------------+-------------+-------------+ 3 rows in set (0.00 sec)
Stworzymy teraz nowego użytkownika do naszych testów :
MariaDB [(none)]> CREATE USER 'newuser1'@'%' IDENTIFIED BY 'newuser1';
Query OK, 0 rows affected (0.00 sec)
Możemy mu po prostu dać uprawnienia do odczytu na wszystkich tabelach we wszystkich bazach:
mysql> GRANT SELECT ON *.* to 'newuser1'@'%';
Query OK, 0 rows affected (0.00 sec)
dać uprawnienia tylko dla jednej bazy i wszystkich tabel w niej utworzonych
mysql> GRANT ALL PRIVILEGES ON testbaza1.* TO 'newuser1'@'%';
lub nadać my tylko do konkretnej tabeli w konkretnej bazie (TESTTABLE1 w TESTBAZA1);
mysql> grant select on testbaza1.testtable1 to 'newuser1'@'%';
Query OK, 0 rows affected (0.00 sec)
Zaczytujemy uprawnienia (uruchamiamy to za każdym razem gdy zmieniamy uprawnienia):
MariaDB [(none)]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
Sprawdzamy w globalnych uprawnieniach i tu nic się nie pojawiło
mysql> select user,host, select_priv, insert_priv, update_priv, delete_priv from mysql.user;
+-----------+-----------+-------------+-------------+-------------+-------------+ | user | host | select_priv | insert_priv | update_priv | delete_priv | +-----------+-----------+-------------+-------------+-------------+-------------+ | root | localhost | Y | Y | Y | Y | | mysql.sys | localhost | N | N | N | N | | mysql | % | Y | Y | Y | Y | | newuser1 | % | Y | N | N | N | +-----------+-----------+-------------+-------------+-------------+-------------+ 5 rows in set (0.00 sec)
ale user uprawnienia ma:
mysql> show grants for 'newuser1'@'%';
+------------------------------------------------------------+ | Grants for newuser1@% | +------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'newuser1'@'%' | | GRANT SELECT ON `testbaza1`.`testtable1` TO 'newuser1'@'%' | +------------------------------------------------------------+ 2 rows in set (0.00 sec)
Możemy ograniczyć prawa użytkownika tylko do wybranych kolumn w tabeli:
najpierw zabierzemy mu uprawnienia do całej tabeli:
mysql> revoke SELECT ON testbaza1.testtable1 from 'newuser1'@'%';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
mysql> show grants for 'newuser1'@'%';
+--------------------------------------+ | Grants for newuser1@% | +--------------------------------------+ | GRANT USAGE ON *.* TO 'newuser1'@'%' | +--------------------------------------+ 1 row in set (0.00 sec)
a następnie damy tylko do konkretnej kolumny:
mysql> grant select (firstname) on testbaza1.testtable1 to 'newuser1'@'%';
Query OK, 0 rows affected (0.00 sec)
Użytkownik próbując wyświetlić tabelę otrzyma komunikat:
mysql> select * from testtable1;
ERROR 1143 (42000): SELECT command denied to user 'newuser1'@'localhost' for column 'id' in table 'testtable1'
Ale już wyświetlając tylko jedną kolumnę:
mysql> select firstname from testtable1;
+-----------+ | firstname | +-----------+ | Anna | | Katarzyna | | Robert | | Andrzej | | Agnieszka | | Hubert | +-----------+ 6 rows in set (0.00 sec)
Uprawnienia do kolumn możemy „mieszać” czyli np:
mysql> GRANT SELECT (col1), INSERT (col1,col2) ON testbaza1.testtabel1 TO 'newuser1'@'%';
Wydajemy polecenie
mysql> grant select on testbaza1.testtable1 to 'newuser1'@'%';
i user może znowu czytać całą tabelę 🙂
Jak widać jest kilka poziomów uprawnień:
• globalny – przyznane uprawnienia obowiązują we wszystkich istniejących bazach,
• baza danych – uprawnienia w zakresie danej bazy danych,
• tabela – w zakresie jednej tabeli,
• kolumna – w zakresie jednej kolumny konkretnej tabeli
Dla sprawdzenie czy user ma prawidłowe uprawnienia, zalogowujemy się do bazy na NEWUSER1:
[root@localhost ~]# mysql -u newuser1 -p
mysql> select * from testbaza1.testtable1;
+----+-----------+------------+------------------------------+ | id | firstname | lastname | email | +----+-----------+------------+------------------------------+ | 1 | Jan | Kowalski | jan.kowalski@mail.pl | | 3 | Anna | Nowak | anna.nowak@mail.pl | | 4 | Katarzyna | Polska | katarzyna.polska@mail.pl | | 5 | Robert | Barski | robert.barski@mail.pl | | 6 | Andrzej | Dulski | andrzej.dulski@mail.pl | | 7 | Agnieszka | Frankowska | agnieszka.frankowska@mail.pl | | 8 | Hubert | Goglewski | hubert.goglewski@mail.pl | +----+-----------+------------+------------------------------+ 7 rows in set (0.00 sec)
Spróbujemy wyświetlić jakąś inną tabelę
mysql> select * from mysql.user;
ERROR 1142 (42000): SELECT command denied to user 'newuser'@'localhost' for table 'user'
Aby zabrać uprawnienie używamy polecenia REVOKE
Zabranie wszytskich uprawnień do bazy:
mysql> REVOKE ALL ON *.* FROM 'newuser1'@'%';
Query OK, 0 rows affected (0.00 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
lub jakiś konkretnych np prawa do INSERT’ów
mysql> REVOKE INSERT ON *.* FROM 'newuser1'@'%';
Usunięcie użytkownika
Usuwamy go poleceniem DROP USER
MariaDB [(none)]> DROP USER 'newuser1'@'localhost';
Query OK, 0 rows affected (0.00 sec)
lub „ręcznie” usuwając go z tabeli mysql.user
MariaDB [(none)]> DELETE FROM mysql.user WHERE User='testusr2.localhost';
Query OK, 1 row affected (0.00 sec)
