MariaDB [(none)]> show grants;
+-----------------------------------------------------------------------------------------------------------------------------------------+
| Grants for anel@localhost |
+-----------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON . TO 'anel'@'localhost' IDENTIFIED VIA mysql_native_password USING 'invalid' OR unix_socket WITH GRANT OPTION |
+-----------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.000 sec)
+ Now let's try to connect with other user, not created yet `melisa`:
```sql
./client/mysql -u melisa
MariaDB [(none)]> select user(), current_user;
+------------------+--------------+
| user() | current_user |
+------------------+--------------+
| melisa@localhost | @localhost |
+------------------+--------------+
1 row in set (0.000 sec)
MariaDB [(none)]> show grants;
+--------------------------------------+
| Grants for @localhost |
+--------------------------------------+
| GRANT USAGE ON *.* TO ''@'localhost' |
+--------------------------------------+
1 row in set (0.000 sec)
MariaDB [(none)]> select user,host from mysql.user\G
ERROR 1142 (42000): SELECT command denied to user ''@'localhost' for table 'user'
Let's connect with root:
./client/mysql -u root
2019-10-23 2:52:14 14 [Warning] Access denied for user 'root'@'localhost'
ERROR 1698 (28000): Access denied for user 'root'@'localhost'
Now I have created user melisa (create user melisa identified by password, and it will create melisa@% (for all hosts)) and granted privileges:
*************************** 5. row ***************************
Host: %
User: melisa
Priv: {"access":0,"plugin":"mysql_native_password","authentication_string":"*279018759837D91FAB7BB5939852F3E940F3334B","password_last_changed":1571825125}
5 rows in set (0.000 sec)
MariaDB [(none)]> show grants for melisa;
+-------------------------------------------------------------------------------------------------------+
| Grants for melisa@% |
+-------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON . TO 'melisa'@'%' IDENTIFIED BY PASSWORD '279018759837D91FAB7BB5939852F3E940F3334B' |
+-------------------------------------------------------------------------------------------------------+
MariaDB [(none)]> grant all on . to melisa@localhost;
ERROR 1133 (28000): Can't find any matching row in the user table
MariaDB [(none)]> grant all on .* to 'melisa'@'%';
Query OK, 0 rows affected (0.001 sec)
MariaDB [(none)]> flush privileges;
+ #### But I cannot to connect
```sql
./client/mysql -umelisa -p
Enter password:
2019-10-23 3:26:42 29 [Warning] Access denied for user 'melisa'@'localhost' (using password: YES)
ERROR 1045 (28000): Access denied for user 'melisa'@'localhost' (using password: YES)
I thought the problem was that bind_address is undefined and we have to change it in our config file to 0.0.0.0:
MariaDB [(none)]> show variables like 'bind%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| bind_address | |
+---------------+-------+
but wasn't.
QUESTION 1.1) Why I'm logged in as anonymous?
Because we are starting the server with --skip-grant-tables (this option can be set in my.cnf also), and by default we are in anonymous mode.
In my case I'm not starting the server with skipping the grant tables and I used correct user (melisa )but again I'm anonymous (it is default user) ?
./client/mysql -u melisa
MariaDB [(none)]> select * from mysql.global_priv\G
ERROR 1142 (42000): SELECT command denied to user ''@'localhost' for table 'global_priv'
show grants;
+--------------------------------------+
| Grants for @localhost |
+--------------------------------------+
| GRANT USAGE ON *.* TO ''@'localhost' |
+--------------------------------------+
QUESTION 2.2) Why this is not working and should be?
MariaDB [(none)]> select current_user();
+----------------+
| current_user() |
+----------------+
| @localhost |
+----------------+
1 row in set (0.000 sec)
MariaDB [(none)]> grant all *.* to current_user(); # ERROR !
The problem was that there existed anonymous users :
MariaDB [(none)]> select user,host from mysql.user where user='';
+------+-----------+
| User | Host |
+------+-----------+
| | localhost |
| | ubuntu |
+------+-----------+
And anonymous user with localhost has precedence over any host (%). I deleted anonymous user, flushed privileges and it worked.
Documentation says to start mysqld with --skip-grant-table #(there should be some user, if there is no user I don't know?) and it will have full access to all tables (after FLUSH PRIVILEGES?)!
login with user and run FLUSH PRIVILEGES to disable grant-tables and allows to change stored password for root example.
run set password for root@localhost = password('mypass')
TESTING
Now I have created some user; melisa (grant usage) and unix_socket is disabled:
./client/mysql -u melisa
QUESTION 2.2) How to get all options or system_variables (MYSQLD/client) which are configured ?
I would like to see is mariadb option: skip-option checked ?
After running: ./sql/mysqld --help --verbose at the we have:
To see what values a running MySQL server is using, type
'mysqladmin variables' instead of 'mysqld --verbose --help'.
If I try this:
>> ./sql/mysqld --help --verbose | grep skip-grant
2019-10-18 2:48:59 0 [Note] Plugin 'FEEDBACK' is disabled.
2019-10-18 2:48:59 0 [Warning] Could not open mysql.plugin table. Some options may be missing from the help text
--skip-grant-tables Start without grant tables. This gives all users FULL
skip-grant-tables FALSE
./client/mysqladmin variables | grep skip #--# not started with skip-grant-tables
| replicate_events_marked_for_skip | REPLICATE |
| skip_external_locking | ON |
| skip_name_resolve | OFF |
| skip_networking | OFF |
| skip_show_database | OFF |
| slave_skip_errors | OFF |
| sql_slave_skip_counter | 0
First we have to test unix socket in mariadb:
unix_socket
Q1) configure root to use unix_socket ?
MariaDB [(none)]> show grants; +-----------------------------------------------------------------------------------------------------------------------------------------+ | Grants for anel@localhost | +-----------------------------------------------------------------------------------------------------------------------------------------+ | GRANT ALL PRIVILEGES ON . TO 'anel'@'localhost' IDENTIFIED VIA mysql_native_password USING 'invalid' OR unix_socket WITH GRANT OPTION | +-----------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.000 sec)
MariaDB [(none)]> select user,host from mysql.user\G 1. row User: Host: localhost 2. row User: anel Host: localhost 3. row User: root Host: localhost 4. row User: Host: ubuntu 4 rows in set (0.002 sec)
M ariaDB [(none)]> select * from mysql.global_priv\G 1. row Host: localhost User: root Priv: {"access":18446744073709551615,"plugin":"mysql_native_password","authentication_string":"invalid","auth_or":[{},{"plugin":"unix_socket"}]} 2. row Host: localhost User: anel Priv: {"access":18446744073709551615,"plugin":"mysql_native_password","authentication_string":"invalid","auth_or":[{},{"plugin":"unix_socket"}]} 3. row Host: localhost User: Priv: {} 4. row Host: ubuntu User: Priv: {} 4 rows in set (0.000 sec)
Let's connect with
root
:Now I have created user
melisa
(create user melisa identified by password
, and it will createmelisa@%
(for all hosts)) and granted privileges:MariaDB [(none)]> select user,host from mysql.user\G 1. row User: melisa Host: %
Grant privileges
MariaDB [(none)]> show grants for melisa; +-------------------------------------------------------------------------------------------------------+ | Grants for melisa@% | +-------------------------------------------------------------------------------------------------------+ | GRANT USAGE ON . TO 'melisa'@'%' IDENTIFIED BY PASSWORD '279018759837D91FAB7BB5939852F3E940F3334B' | +-------------------------------------------------------------------------------------------------------+ MariaDB [(none)]> grant all on . to melisa@localhost; ERROR 1133 (28000): Can't find any matching row in the user table MariaDB [(none)]> grant all on .* to 'melisa'@'%'; Query OK, 0 rows affected (0.001 sec) MariaDB [(none)]> flush privileges;
I thought the problem was that
bind_address
is undefined and we have to change it in our config file to0.0.0.0
:but wasn't.
QUESTION 1.1) Why I'm logged in as
anonymous
?--skip-grant-tables
(this option can be set in my.cnf also), and by default we are in anonymous mode.In my case I'm not starting the server with skipping the grant tables and I used correct user (
melisa
)but again I'm anonymous (it is default user) ?QUESTION 2.2) Why this is not working and should be?
The problem was that there existed anonymous users :
And anonymous user with
localhost
has precedence over any host (%
). I deleted anonymous user, flushed privileges and it worked.To wrap up:
Q2) What if unix_socket is not enabled and we are going from scratch? (like changing the root password)
https://mariadb.com/kb/en/library/authentication-from-mariadb-104/#cookbook
run
set password for root@localhost = password('mypass')
TESTING
Now I have created some user;
melisa
(grant usage) andunix_socket
is disabled: ./client/mysql -u melisaVARIANT 1) Not using the --skip-grant-tables:
QUESTION 2.2) How to get all options or system_variables (MYSQLD/client) which are configured ?
I would like to see is mariadb option:
skip-option
checked ?After running:
./sql/mysqld --help --verbose
at the we have:If I try this:
To see the variables (some options are variables also):
show variables
(https://mariadb.com/kb/en/library/show-variables/) is showing system variables from client (https://mariadb.com/kb/en/library/server-system-variables/)!QUESTION 2.4) Killing the second mysqld with --v works, while killing the second with --skip-grant-tables doesn't work, why ?
Note: About status of mariadb options switch:
skip-grant-tables
) (asked on zulip).mysqld --help --verbose | grep grant-tables
VARIANT 2) Now we are using the
--skip-grant-tables
:Q3)
libmysqld.c:
mysql_real_connect(MYSQL mysql,const char host, const char user, const char passwd, const char db, uint port, const char unix_socket,ulong client_flag)
takes passwd string
There is also in /usr/include/pwd.h ???
ifdef HAVE_GETPWUID
struct passwd getpwuid(uid_t); char getlogin(void);
endif
getenv(MYSQL_PWD) -> /usr/include/stdlib.c
mysql.cc
get_one_option()
argument, opt_password
< /proc/$(pidof mysql)/environ tr '\0' '\n' | grep MYSQL_PWD ???
TESTING THE PATCH
get_tty_password (opt_message=0x0) at mysys/get_password.c:149 (gdb) bt +bt
0 get_tty_password (opt_message=0x0) at mysys/get_password.c:149
1 0x00005555555b6da7 in get_options (argc=0, argv=0x555555c012c8) at client/mysql.cc:1977
2 0x00005555555b56bd in main (argc=4, argv=0x555555c012c0, envp=0x7fffffffdbc0) at client/mysql.cc:1199