I created a database, such as'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 log in to the database from anywhere, but I can't create tables.
How to grant all permissions to the database and (in the future) tables.I cannot create tables in my'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'
This is an old question, but I don't think it's safe to accept the answer.This is good for creating superusers, but not for granting permissions 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';
% does not seem to include sockethost communication, localhost is used for.WITH GRANT OPTION is only beneficial to superusers, otherwise there is usually a security risk.
Hopefully this will help.
This is useful for some people:
From the MySQL command line:
CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';
Unfortunately, newuser is not authorized to perform any operations on the database at this time.In fact, if newuser even attempts to log in (using a password, password), they will not be able to access the MySQL shell.
Therefore, the first thing to do is to provide users with access to the information they need.
GRANT ALL PRIVILEGES ON * . * TO 'newuser'@'localhost';
The asterisk in this command refers to the databases and tables that they have access to (respectively) - this specific command allows users to read, edit, perform, and perform all tasks in all databases and tables.
After you have completed the permissions you want to set for the new user, be sure to reload all permissions.
Your changes will now take effect.
Hello I have super users in mysql using this code
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;
This SQL grants all databases, but only basic permissions.They are sufficient for Drupal or Wordpress and, as an exact one, allow a developer account to be used 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';
Permissions applicable to the architecture:
Optional: After mypasswd, you can add WITH GRANT OPTION