授予数据库上的所有权限

I've created database, for example 'mydb'.

CREATE DATABASE mydb CHARACTER SET utf8 COLLATE utf8_bin;
CREATE USER 'myuser'@'%' IDENTIFIED BY PASSWORD '*HASH';
GRANT ALL ON mydb.* TO 'myuser'@'%';
GRANT ALL ON mydb TO 'myuser'@'%';
GRANT CREATE ON mydb TO 'myuser'@'%';
FLUSH PRIVILEGES;

Now i can login to database from everywhere, but can't create tables.

How to grant all privileges on that database and (in the future) tables. I can't create tables in 'mydb' database. I always get:

CREATE TABLE t (c CHAR(20) CHARACTER SET utf8 COLLATE utf8_bin);
ERROR 1142 (42000): CREATE command denied to user 'myuser'@'...' for table 't'

转载于:https://stackoverflow.com/questions/5016505/grant-all-privileges-on-database

csdnceshi79
python小菜 Also, try relogin for the user
2 年多之前 回复
csdnceshi54
hurriedly% You should use FLUSH PRIVILEGES; only if you modify the grant tables directly using statements such as INSERT, UPDATE, or DELETE
2 年多之前 回复
csdnceshi61
derek5. flush privileges is not needed when you use grant commands. x4
5 年多之前 回复
csdnceshi56
lrony* Thanks for "SHOW GRANTS FOR CURRENT_USER;" - that helps me see my typo.
9 年多之前 回复
weixin_41568110
七度&光 Have you tried running FLUSH PRIVILEGES ?
9 年多之前 回复
csdnceshi78
程序go What do you get when you SHOW GRANTS FOR CURRENT_USER;
9 年多之前 回复

10个回答

GRANT ALL PRIVILEGES ON mydb.* TO 'myuser'@'%' WITH GRANT OPTION;

This is how I create my "Super User" privileges (although I would normally specify a host).

IMPORTANT NOTE

While this answer can solve the problem of access, WITH GRANT OPTION creates a MySQL user that can edit the permissions of other users.

The GRANT OPTION privilege enables you to give to other users or remove from other users those privileges that you yourself possess.

For security reasons, you should not use this type of user account for any process that the public will have access to (i.e. a website). It is recommended that you create a user with only database privileges for that kind of use.

csdnceshi54
hurriedly% I have error Access denied for user 'root'@'localhost' (using password: YES)
2 年多之前 回复
weixin_41568110
七度&光 to assign the password in one go. ==> : GRANT ALL PRIVILEGES ON mydb.* TO 'myuser'@'%' IDENTIFIED BY 'password' WITH GRANT OPTION;
接近 3 年之前 回复
csdnceshi66
必承其重 | 欲带皇冠 I voted this down for the same reason as @user3338098. The answer with "identified by" in it should be the accepted answer. Also, "WITH GRANT OPTION" should only be used if necessary. I don't believe the OP indicated that it was.
3 年多之前 回复
csdnceshi52
妄徒之命 I was using mysql v5.1 if it matters
大约 4 年之前 回复
csdnceshi52
妄徒之命 this is NOT safe. this created an entry in the users table without a password, and mysql ignores ALL the other permission rows and just looks at the last one that matches. Thus allowing full access for the few minutes this took effect and denying access to anyone using a password. This is a HORRIBLE SECURITY VIOLATION!
大约 4 年之前 回复
csdnceshi64
游.程 And how to revoke these privileges?
5 年多之前 回复
csdnceshi62
csdnceshi62 Users setting up an MySQL server are likely intelligent enough to realise that they should replace myuser with their own custom username.
5 年多之前 回复
csdnceshi55
~Onlooker flush privileges is not needed when you use grant commands. x4
5 年多之前 回复
csdnceshi60
℡Wang Yan Also don 't forget to logout-login from phpMyAdmin in order to see the changes.
5 年多之前 回复
weixin_41568196
撒拉嘿哟木头 I edited this answer to say mydb.* instead of ., since it's such a widely upvoted and viewed answer, and I believe security--especially with a polished turd like MySQL--is extremely important. One would hope that a reader would investigate the details of the answer rather than copy and paste, but I like to live in reality occasionally.
接近 6 年之前 回复
csdnceshi73
喵-见缝插针 I also don't think this answer is the good one. It gives "administrator" privileges on all databases and all tables, which is not what was asked.
6 年多之前 回复
csdnceshi71
Memor.の Fair enough. But one has to also think about the other people, possibly newbies, that could come read this question later on. Isn't it the point of SO as well?
9 年多之前 回复
csdnceshi56
lrony* you are not really bringing alot to the table here - i don't name my users myuser - the questioner was simply using a username as an example - i used the same example username for consistency.
9 年多之前 回复

This will be helpful for some people:

From MySQL command line:

CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';

Sadly, at this point newuser has no permissions to do anything with the databases. In fact, if newuser even tries to login (with the password, password), they will not be able to reach the MySQL shell.

Therefore, the first thing to do is to provide the user with access to the information they will need.

GRANT ALL PRIVILEGES ON * . * TO 'newuser'@'localhost';

The asterisks in this command refer to the database and table (respectively) that they can access—this specific command allows to the user to read, edit, execute and perform all tasks across all the databases and tables.

Once you have finalized the permissions that you want to set up for your new users, always be sure to reload all the privileges.

FLUSH PRIVILEGES;

Your changes will now be in effect.

For more information: http://dev.mysql.com/doc/refman/5.6/en/grant.html

If you are not comfortable with the command line then you can use a client like MySQL workbench, Navicat or SQLyog

weixin_41568126
乱世@小熊 I needed to identify again with GRANT ALL PRIVILEGES ON * . * TO 'newuser'@'localhost' IDENTIFIED BY 'my_password' WITH GRANT OPTION; (strange... , but true)
一年多之前 回复
weixin_41568196
撒拉嘿哟木头 Either you wholesale copied from Linode, or they copied from you: linode.com/docs/databases/mysql/…
大约 4 年之前 回复
csdnceshi71
Memor.の Stop using FLUSH PRIVILEGES
4 年多之前 回复
csdnceshi68
local-host flush privileges is not needed when you use grant commands. x4
5 年多之前 回复

This is old question but I don't think the accepted answer is safe. It's good for creating a super user but not good if you want to grant privileges on a single database.

grant all privileges on mydb.* to myuser@'%' identified by 'mypasswd';
grant all privileges on mydb.* to myuser@localhost identified by 'mypasswd';

% seems to not cover socket communications, that the localhost is for. WITH GRANT OPTION is only good for the super user, otherwise it is usually a security risk.

Hope this helps.

csdnceshi71
Memor.の mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password';
大约 2 年之前 回复
csdnceshi60
℡Wang Yan I'm running into this warning: Using GRANT statement to modify existing user's properties other than privileges is deprecated and will be removed in future release. Use ALTER USER statement for this operation. MySQL 5.7
大约 3 年之前 回复
csdnceshi78
程序go there are a lot of things here that depend on use case as well environment. For example java driver does not support socket transport at all IIRC. But generally I agree - better specify specific IPs when possible, or run server only on 127.0.0.1 and firewall the server when listening to non-localhost.
3 年多之前 回复
csdnceshi58
Didn"t forge Note that you probably don't need the first command unless you want access from outside IP addresses, or from other computers on the local subnet. If you do, I would recommend using commands that are targeted to the specific IP addresses that you want to allow, such as myuser@192.168.0.1 (for one on the local subnet), rather than myuser@% generally.
3 年多之前 回复
csdnceshi78
程序go search for unix sockets. When using localhost myslq client on linux is trying to use a unix socket instead of a TCP connection to the server.
大约 6 年之前 回复
weixin_41568134
MAO-EYE the grammar of "% seems to not cover socket communications, that the localhost is for" is unclear. What does this actually mean?
大约 6 年之前 回复
csdnceshi80
胖鸭 +1 for not including WITH GRANT OPTION and targeting a specified database instead of all (*).
6 年多之前 回复

I could able to make it work only by adding GRANT OPTION, without that always receive permission denied error

GRANT ALL PRIVILEGES ON mydb.* TO 'myuser'@'localhost' WITH GRANT OPTION;

Hello I used this code to have the super user in mysql

GRANT EXECUTE, PROCESS, SELECT, SHOW DATABASES, SHOW VIEW, ALTER, ALTER ROUTINE,
    CREATE, CREATE ROUTINE, CREATE TEMPORARY TABLES, CREATE VIEW, DELETE, DROP,
    EVENT, INDEX, INSERT, REFERENCES, TRIGGER, UPDATE, CREATE USER, FILE,
    LOCK TABLES, RELOAD, REPLICATION CLIENT, REPLICATION SLAVE, SHUTDOWN,
    SUPER
        ON *.* TO mysql@'%'
    WITH GRANT OPTION;

and then

FLUSH PRIVILEGES;
csdnceshi69
YaoRaoLov Note: The exact list in the GRANT varies between Versions of MySQL.
4 年多之前 回复
weixin_41568184
叼花硬汉 Stop using FLUSH PRIVILEGES
4 年多之前 回复
csdnceshi78
程序go flush privileges is not needed when you use grant commands. x4
5 年多之前 回复

To access from remote server to mydb database only

GRANT ALL PRIVILEGES ON mydb.* TO 'root'@'192.168.2.21';

To access from remote server to all databases.

GRANT ALL PRIVILEGES ON * . * TO 'root'@'192.168.2.21';

 1. Create the database

CREATE DATABASE db_name;

 2. Create the username for the database db_name

GRANT ALL PRIVILEGES ON db_name.* TO 'username'@'localhost' IDENTIFIED BY 'password';

 3. Use the database

USE db_name;

 4. Finally you are in database db_name and then execute the commands like create , select and insert operations.

csdnceshi73
喵-见缝插针 Today this gave me the warning "Using GRANT statement to modify existing user's properties other than privileges is deprecated and will be removed in future release. Use ALTER USER statement for this operation." So should I understand that I should create a user, then grant privileges, now?
2 年多之前 回复

This SQL grants on all databases but just basic privileges. They're enough for Drupal or Wordpress and as a nicety, allows one developer account for local projects.

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, 
    INDEX, ALTER, CREATE TEMPORARY TABLES 
ON *.* TO 'username'@'localhost' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON mydb.* TO myuser@localhost IDENTIFIED BY 'mypasswd';

Works for privileges on schema :)

Optional: after mypasswd you can add WITH GRANT OPTION

To grant all priveleges on the database: mydb to the user: myuser, just execute:

GRANT ALL ON mydb.* TO 'myuser'@'localhost';

or:

GRANT ALL PRIVILEGES ON mydb.* TO 'myuser'@'localhost';

The PRIVILEGES keyword is not necessary.

Also I do not know why the other answers suggest that the IDENTIFIED BY 'password' be put on the end of the command. I believe that it is not required.

Csdn user default icon
上传中...
上传图片
插入图片
抄袭、复制答案,以达到刷声望分或其他目的的行为,在CSDN问答是严格禁止的,一经发现立刻封号。是时候展现真正的技术了!
立即提问