권한을 부여하려고 시도하는 동안 사용자 'root'@ 'localhost'에 대한 액세스가 거부되었습니다. 권한을 어떻게 부여합니까?
비슷한 질문을 많이 보았으므로 기본 사항을 확인했음을 보여줍니다. 물론 그렇다고해서 완전히 명백한 것을 놓친 것은 아닙니다. :-)
내 질문은 : 내가하려는 일을 할 수있는 권한을 가진 사용자와 이미 암호를 입력하고 액세스 권한이 부여 된 위치에 대한 사용자의 액세스가 거부되는 이유는 무엇입니까? (완전성을 기하기 위해 MySQL 클라이언트가 프로그램 시작시 액세스를 거부하도록 잘못된 암호를 입력하려고했습니다.)
배경:
ssh를 통해 MySQL 서버를 실행하는 시스템의 쉘에 로그인하면 루트로 로그인합니다.
[myname@host ~]$ mysql -u root -p -hlocalhost
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 62396
Server version: 5.5.18-log MySQL Community Server (GPL)
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
대박. 비슷한 질문에 대한 답을 읽으면 보조금 표에있는 권한이 최신 상태인지 확인해야합니다
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
mysql>
다음은 내가 생각하는 사람인지 확인하십시오.
mysql> SELECT user();
+----------------+
| user() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)
... 정말 정말 있는지 확인합니다 :
mysql> SELECT current_user();
+----------------+
| current_user() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)
mysql>
여태까지는 그런대로 잘됐다. 이제 어떤 특권이 있습니까?
mysql> SHOW GRANTS FOR 'root'@'localhost';
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '[OBSCURED]' WITH GRANT OPTION |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
이제는 읽기가 약간 어렵 기 때문에이 방법으로 시도해보십시오 (로컬 호스트가 아닌 '루트'사용자가 있음을 알 수 있습니다).
mysql> SELECT * FROM mysql.user WHERE User='root'\G
*************************** 1. row ***************************
Host: localhost
User: root
Password: *[OBSCURED]
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
ssl_type:
ssl_cipher:
x509_issuer:
x509_subject:
max_questions: 0
max_updates: 0
max_connections: 0
max_user_connections: 0
*************************** 2. row ***************************
Host: [HOSTNAME].com
User: root
Password: *[OBSCURED]
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
ssl_type:
ssl_cipher:
x509_issuer:
x509_subject:
max_questions: 0
max_updates: 0
max_connections: 0
max_user_connections: 0
2 rows in set (0.00 sec)
대박! MySQL은 내가 root @ localhost이고 root @ localhost는 모든 권한을 가지고 있다고 생각합니다. 그것은 내가 원하는 것을 할 수 있어야한다는 것을 의미합니다.
mysql> GRANT ALL PRIVILEGES ON *.* TO 'steves'@'[hostname].com' IDENTIFIED BY '[OBSCURED]' WITH GRANT OPTION;
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
이 기본적인 것을 어떻게 망칠 수 있었습니까?
참고 사항 : 모든 권한을 가진 root라는 사용자가 없다고 제안하려는 사람에게는 훌륭하고 다른 사용자에게 권한을 부여 할 수 있다면 좋을 것입니다.
감사합니다!
Notice how the output of
SHOW GRANTS FOR 'root'@'localhost';
did not say 'ALL PRIVILEGES' but had to spell out what root@localhost has.
GRANT ALL PRIVILEGES will fail, because a user can not grant what he/she does not have, and the server seem to think something is not here ...
Now, what's missing then ?
On my system, I get this:
mysql> select version();
+------------+
| version() |
+------------+
| 5.5.21-log |
+------------+
1 row in set (0.00 sec)
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)
mysql> SELECT * FROM mysql.user WHERE User='root' and Host='localhost'\G
*************************** 1. row ***************************
Host: localhost
User: root
Password:
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 <----------------------------- new column in 5.5
ssl_type:
ssl_cipher:
x509_issuer:
x509_subject:
max_questions: 0
max_updates: 0
max_connections: 0
max_user_connections: 0
plugin: <------------------------------- new column in 5.5
authentication_string: <------------------------------- new column in 5.5
1 row in set (0.00 sec)
There are also new tables in 5.5, such as mysql.proxies_user: make sure you have them.
When installing a brand new mysql server instance, the install script will create all the mysql.* tables with the proper structure.
When upgrading from an old version, make sure the proper upgrade procedure (mysql_upgrade) is used, which will add the missing tables / columns.
It is only a guess, but it seems mysql_upgrade was not done for this instance, causing the behavior seen.
I also had the same problem with this but on Windows after upgrading to MySQL 5.5 from MySQL 5.1. I already tried changing, creating, and resetting password mentioned in here, here, here, and here, no clue. I still get the same error:
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
I'm able to connect normally, show all databases, do selects and inserts, create and add users, and but when it comes to GRANT, I'm screwed up. Those access denied error shows up again.
I managed to solve this problem by fixing the privileges by the following command on the MySQL server bin/ directory as mentioned in here:
C:\MySQL Server 5.5\bin> mysql_upgrade
Then, the problem gone away. I hope this solution works on Linux too since usually MySQL provide the same command both on Linux and Windows.
This might happen when you attempt to grant all privileges on all tables to another user, because the mysql.users table is considered off-limits for a user other than root.
The following however, should work:
GRANT ALL PRIVILEGES ON `%`.* TO '[user]'@'[hostname]' IDENTIFIED BY '[password]' WITH GRANT OPTION;
Note that we use `%`.* instead of *.*
This happened to me when I tried to install a higher MySQL version than the one coming with the distribution.
I erased the old version then installed the new one (rpm -e ... then rpm -i MySQL-server* ) But did not realize that the files in /var/lib/mysql were still from the older version (with differences as explained by Marc Alff - thanks!)
I could have done an mysql_upgrade, but as I wanted to start from scratch I did:
# su - mysql
$ rm -rf /var/lib/mysql/*
$ mysql_install_db
# /etc/init.d/mysql start
Then set root password (/usr/bin/mysqladmin -u root password), and all worked as expected with the GRANT commands...
Basically this error comes when you have not specified a password, it means that you have an incorrect password listed in some option file.
Read this DOC on understanding how to assign and manage Passwords to accounts.
Also , Check if the permission on the folder /var/lib/mysql/mysql is 711 or not.
I had the same problem, i.e. all privileges granted for root:
SHOW GRANTS FOR 'root'@'localhost'\G
*************************** 1. row ***************************
Grants for root@localhost: GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*[blabla]' WITH GRANT OPTION
...but still not allowed to create a table:
create table t3(id int, txt varchar(50), primary key(id));
ERROR 1142 (42000): CREATE command denied to user 'root'@'localhost' for table 't3'
Well, it was cause by an annoying user error, i.e. I didn't select a database. After issuing USE dbname it worked fine.
On Debian (Wheezy, 7.8) with MySQL 5.5.40, I found SELECT * FROM mysql.user WHERE User='root'\G showed the Event_priv and 'Trigger_priv` fields were present but not set to Y.
Running mysql_upgrade (with or without --force) made no difference; I needed to do a manual:
update user set Event_priv = 'Y',Trigger_priv = 'Y' where user = 'root'
Then finally I could use:
GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY 'password' WITH GRANT OPTION
…and then use it more precisely on an individual database/user account.
You may have come to this question with MySQL version 8 installed (like me) and not found a satisfactory answer. You can no longer create users like this in version 8:
GRANT ALL PRIVILEGES ON *.* TO 'steves'@'[hostname].com' IDENTIFIED BY '[OBSCURED]' WITH GRANT OPTION;
The rather confusing error message that you get back is: ERROR 1410 (42000): You are not allowed to create a user with GRANT
In order to create users in version 8 you have to do it in two steps:
CREATE USER 'steves'@'[hostname].com' IDENTIFIED BY '[OBSCURED]';
GRANT ALL PRIVILEGES ON *.* TO 'steves'@'[hostname].com' WITH GRANT OPTION;
Of course, if you prefer, you can also supply a limited number of privileges (instead of GRANT ALL PRIVILEGES), e.g. GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER
Typing SHOW GRANTS FOR 'root'@'localhost'; showed me some obscured password, so I logged into mysql of that system using HeidiSQL on another system (using root as the username and the corresponding password) and typed
GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY 'thepassword' WITH GRANT OPTION;
and it worked when I went back to the system and logged on using
mysql -uroot -pthepassword;
I run at this when I tried to add privileges to performance_schema, which is mysql bug http://bugs.mysql.com/bug.php?id=44898 (workaround to add --single-transaction).
For those who still stumble upon this like I did, it's worth checking to make sure the attempted GRANT does not already exist:
SHOW GRANTS FOR username;
In my case, the error was not actually because there was a permission error, but because the GRANT already existed.
I had the same problem and it took a lot of reading SO posts and Google's documentation. I finally found this from the Cloud SQL FAQ:
Google Cloud SQL does not support SUPER privileges, which means that
GRANT ALL PRIVILEGESstatements will not work. As an alternative, you can useGRANT ALL ON `%`.*
'development' 카테고리의 다른 글
| HTML 스크립트 태그 : 유형 또는 언어 (또는 둘 다 생략)? (0) | 2020.06.03 |
|---|---|
| 소켓의 연결 시간과 읽기 시간 초과의 차이점은 무엇입니까? (0) | 2020.06.03 |
| Android 에뮬레이터 키보드가 중국어 문자 모드 인 이유는 무엇입니까? (0) | 2020.06.03 |
| maven의 "pom"패키징이란 무엇입니까? (0) | 2020.06.03 |
| 자바 스크립트 의존성 관리 : npm vs. bower vs. volo (0) | 2020.06.03 |