Hi,
----
Create privileges and users with MySQL console ----
---
Differences between MySQL 5.7 and MySQL 8 ----
All the following is valid for MySQL versions installed from Wampserver addons.
Note: to avoid identification problems, my users always have the three host name options :
- 127.0.0.1 (local IPv4)
- ::1 (local IPv6)
- localhost (local host name)
For MySQL 5.7.23 and earlier :
To assign a password and all rights to the root user, via the console, the commands are :
# Privileges for `root`@`127.0.0.1``
GRANT ALL PRIVILEGES ON *.* TO 'root'@'127.0.0.1' IDENTIFIED BY 'plaintext password' WITH GRANT OPTION;
# Privileges for `root`@`::1`
GRANT ALL PRIVILEGES ON *.* TO 'root'@'::1' IDENTIFIED BY 'plaintext password' WITH GRANT OPTION;
# Privileges for `root`@`localhost`
GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY 'plaintext password' WITH GRANT OPTION;
GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION;
--- To create a user with usage rights
# Privilèges for `
user name`@`127.0.0.1`
GRANT USAGE ON *.* TO 'user name'@'127.0.0.1' IDENTIFIED BY 'plaintext password';
# Privileges for `
user name`@`localhost`
GRANT USAGE ON *.* TO 'user name'@'localhost' IDENTIFIED BY 'plaintext password';
# Privileges for `
user name`@`::1`
GRANT USAGE ON *.* TO 'user name'@'::1' IDENTIFIED BY 'plaintext password';
--- To give privileges on a database to a user
GRANT SELECT, INSERT, UPDATE, DELETE ON `database name`.* TO 'user name'@'127.0.0.1';
GRANT SELECT, INSERT, UPDATE, DELETE ON `database name`.* TO 'user name'@'localhost';
GRANT SELECT, INSERT, UPDATE, DELETE ON `database name`.* TO 'user name'@'::1';
----
With MySQL 8, this has changed :
--- To assign a password and all rights to the root user, via the console, the commands are :
# MySQL 8 - # Privileges for `root`@`localhost`
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'plaintext password' PASSWORD EXPIRE NEVER;
ALTER USER 'root'@'localhost' DEFAULT ROLE ALL;
GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION;
GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION;
# MySQL 8 - # Privileges for `root`@`127.0.0.1`
CREATE USER IF NOT EXISTS 'root'@'127.0.0.1' IDENTIFIED WITH mysql_native_password BY 'plaintext password' PASSWORD EXPIRE NEVER;
ALTER USER IF EXISTS 'root'@'127.0.0.1' DEFAULT ROLE ALL;
GRANT ALL PRIVILEGES ON *.* TO 'root'@'127.0.0.1' WITH GRANT OPTION;
# MySQL 8 - # Privileges for `root`@`::1`
CREATE USER IF NOT EXISTS 'root'@'::1' IDENTIFIED WITH mysql_native_password BY 'plaintext password' PASSWORD EXPIRE NEVER;
ALTER USER IF EXISTS 'root'@'::1' DEFAULT ROLE ALL;
GRANT ALL PRIVILEGES ON *.* TO 'root'@'::1' WITH GRANT OPTION;
--- To create a user with usage rights
CREATE USER 'user name'@'localhost' IDENTIFIED WITH 'mysql_native_password' BY 'plaintext password' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK;
GRANT USAGE ON *.* TO 'user name'@'localhost';
CREATE USER 'user name'@'127.0.0.1' IDENTIFIED WITH 'mysql_native_password' BY 'plaintext password' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK;
GRANT USAGE ON *.* TO 'user name'@'127.0.0.1';
CREATE USER 'user name'@'::1' IDENTIFIED WITH 'mysql_native_password' BY 'plaintext password' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK;
GRANT USAGE ON *.* TO 'user name'@'::1';
--- To give privileges on a database to a user
GRANT SELECT, INSERT, UPDATE, DELETE ON `database name`.* TO 'user name'@'::1';
GRANT SELECT, INSERT, UPDATE, DELETE ON `database name`.* TO 'user name'@'localhost';
GRANT SELECT, INSERT, UPDATE, DELETE ON `database name`.* TO 'user name'@'127.0.0.1';
---------------------------------------------------------------
Documentation Apache -
Documentation PHP -
Documentation MySQL -
Wampserver install files & addonsEdited 1 time(s). Last edit at 08/17/2018 06:35PM by Otomatic.