MySql:User

lastmod 2018-08-17created 2015-03-244 edits

Example

grant all privileges on `DB이름`.* to 사용자아이디@localhost identified by '비밀번호' with grant option;

Create an account and grant privileges. After use, you must reload (flush privileges).

Caveat: the DB name must be entered with backticks (`), not quotes (').

set password for '아이디'@'%' = password('패스워드');

Changing a MySQL password with set password.

For the '%' part, check the user TABLE of the mysql DB and enter the exact Host to change.

update user set password=PASSWORD('변경할비밀번호') where user = '사용자계정';

Or change the password this way.

mysqladmin -u아이디 -p기존패스워드 password 신규패스워드

Changing a MySQL password with mysqladmin.

drop user 사용자아이디@localhost;

Delete a user.

update user set password=password('123456') where user='root';

Set the password of a specific user(in this example, the root user). After use, you must reload (flush privileges). ¹

Adding privileges

• Zetawiki: Allowing remote access to MySQL

To add privileges for all IPs, apply the following.

INSERT INTO mysql.user (host,user,password) VALUES ('%','root',password('패스워드'));
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%';
FLUSH PRIVILEGES;

To set a specific IP range, configure it as follows; all IPs starting with 111.222 are allowed.

INSERT INTO mysql.user (host,user,password) VALUES ('111.222.%','root',password('패스워드'));
GRANT ALL PRIVILEGES ON *.* TO 'root'@'111.222.%';
FLUSH PRIVILEGES;

Allowing one specific IP

INSERT INTO mysql.user (host,user,password) VALUES ('111.222.33.44','root',password('패스워드'));
GRANT ALL PRIVILEGES ON *.* TO 'root'@'111.222.33.44';
FLUSH PRIVILEGES;

If you allowed all IPs, you can restore the original state as follows.

DELETE FROM mysql.user WHERE Host='%' AND User='root';
FLUSH PRIVILEGES;

See also

• MySql

Favorite site

• How To Create a New User and Grant Permissions in MySQL

References