MariaDB 데이터베이스 표시 권한

MariaDB 데이터베이스 표시 권한

콘솔에서 실행SHOW DATABASES;오류가 발생합니다.

ERROR 1227 (42000): Access denied; you need (at least one of) the SHOW DATABASES privilege(s) for this operation

사용자 권한을 확인했습니다.SHOW GRANTS FOR CURRENT_USER;모든 걸 다 가진 것 같군요

GRANT USAGE ON *.* TO 'tec'@'%' IDENTIFIED BY PASSWORD ' *F0AAB5B469***16C3449'

GRANT ALL PRIVILEGES ON `tec`.* TO 'tec'@'%' 

그 이유는 무엇일까요?

Server version: 10.0.32-MariaDB-0+deb8u1 (Debian)

문제를 재현할 수 없습니다.

$ mysql -u tec -p
Enter password:

MariaDB [(none)]> SELECT VERSION(), CURRENT_USER();
+-----------------+----------------+
| VERSION()       | CURRENT_USER() |
+-----------------+----------------+
| 10.0.33-MariaDB | tec@%          |
+-----------------+----------------+
1 row in set (0.00 sec)

MariaDB [(none)]> SHOW GRANTS FOR CURRENT_USER;
+--------------------------------------------------------------------------------+
| Grants for tec@%                                                               |
+--------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'tec'@'%' IDENTIFIED BY PASSWORD '*F0AAB5B469***16C3449' |
| GRANT ALL PRIVILEGES ON `tec`.* TO 'tec'@'%'                                   |
+--------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| tec                |
+--------------------+
2 rows in set (0.01 sec)

갱신하다

서버 시스템 변수를 확인합니다.

my.cnf

...

[mysqld]
...
skip_show_database
...

$ mysql -u tec -p
Enter password:

MariaDB [(none)]> SELECT VERSION(), CURRENT_USER();
+-----------------+----------------+
| VERSION()       | CURRENT_USER() |
+-----------------+----------------+
| 10.0.33-MariaDB | tec@%          |
+-----------------+----------------+
1 row in set (0.00 sec)

MariaDB [(none)]> SHOW GRANTS FOR CURRENT_USER;
+--------------------------------------------------------------------------------+
| Grants for tec@%                                                               |
+--------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'tec'@'%' IDENTIFIED BY PASSWORD '*F0AAB5B469***16C3449' |
| GRANT ALL PRIVILEGES ON `tec`.* TO 'tec'@'%'                                   |
+--------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

MariaDB [(none)]> SHOW DATABASES;
ERROR 1227 (42000): Access denied; you need (at least one of) the SHOW DATABASES privilege(s) for this operation

업데이트 2

MariaDB [(none)]> SHOW VARIABLES
    -> WHERE `Variable_name` = 'skip_show_database';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| skip_show_database | ON    |
+--------------------+-------+
1 row in set (0.00 sec)

언급URL : https://stackoverflow.com/questions/47181380/mariadb-show-databases-privilege