Create a user?
1 |
CREATE USER ‘username‘ @ ‘host‘ IDENTIFIED BY ‘password‘ ; |
Description
username– the user name you will create
host– specifies on which host the user can log on, if localhost is available to the local user, if you want the user to be able to log in from any remote host, you can use the wildcard%
password– The user's login password, the password can be empty, if it is empty, the user can not require a password to log on to the server
Example:
?
12345 |
CREATE
USER
‘javacui‘
@
‘localhost‘
IDENTIFIED
BY
‘123456‘
;
CREATE
USER
‘javacui‘
@
‘172.20.0.0/255.255.0.0‘ IDENDIFIED
BY
‘123456‘
;
CREATE
USER
‘javacui‘
@
‘%‘
IDENTIFIED
BY
‘123456‘
;
CREATE
USER
‘javacui‘
@
‘%‘
IDENTIFIED
BY
‘‘
;
CREATE
USER
‘javacui‘
@
‘%‘
;
|
Authorized?
1 |
GRANT privileges ON databasename.tablename TO ‘username‘ @ ‘host‘ ; |
Description
privileges– the user's operation permissions, such as SELECT, INSERT, UPDATE, etc. (see the last side of the article for a detailed list). Use all if you want to grant permissions
databasename– database name
tablename-the table name, if you want to grant the user the appropriate operation permissions on all databases and tables, the * representation, such as *. *
Example:
?
12 |
grant select , insert on test. user to Javacui ' @ '% ' ; grant all on *.*&NBSP; to < Code class= "SQL string" > ' javacui ' @ |
Note: The user authorized by the above command cannot authorize other users, if you want to allow the user to authorize, to use the command
?
1 |
GRANT privileges ON databasename.tablename TO ‘username‘ @ ‘host‘ WITH GRANT OPTION ; |
setting and changing user passwords
?
1 |
SET PASSWORD FOR ‘username‘ @ ‘host‘ = PASSWORD ( ‘newpassword‘ ); |
If the user is currently logged in
?
1 |
SET PASSWORD = PASSWORD ( "newpassword" ); |
Revoke user Rights?
1 |
REVOKE privilege ON databasename.tablename FROM ‘username‘ @ ‘host‘ ; |
Description: Privilege, DatabaseName, tablename– with authorization section
Example
?
1 |
REVOKE SELECT ON *.* FROM ‘javacui‘ @ ‘%‘ ; |
Attention:
If you are authorizing the user ' javacui ' @ '% ' (or similar): Grant SELECT on Test.user to ' javacui ' @ '% ', then use REVOKE SELECT on *. * from ' Javacui ' @ '% '; the command does not revoke the user's select operation on the Users table in the test database
Conversely, if the authorization is to use the grant SELECT on * * to ' javacui ' @ '% ', then REVOKE Select on the test.user from ' javacui '% '; the command cannot revoke the user table in the test database The Select permission
Specific information can be used with the command show GRANTS for ' javacui ' @ '% '; View
Delete User
?
1 |
DROP USER ‘username’@‘host’; |
Remember to refresh the database after operation
?
Schedule: Permissions for operations in MySQL
Alter
Allows use of ALTER TABLE.
ALTER ROUTINE
Alters or drops stored routines.
CREATE
Allows use of CREATE TABLE.
CREATE ROUTINE
Creates stored routines.
CREATE Temporary TABLE
Allows use of CREATE temporary TABLE.
CREATE USER
Allows use of the CREATE user, DROP user, RENAME user, and REVOKE all privileges.
CREATE VIEW
Allows use of CREATE VIEW.
DELETE
Allows use of DELETE.
DROP
Allows use of DROP TABLE.
EXECUTE
Allows the user to run stored routines.
FILE
Allows use of SELECT ... Into OUTFILE and LOAD DATA INFILE.
INDEX
Allows use of CREATE index and DROP index.
INSERT
Allows use of INSERT.
LOCK TABLES
Allows use of LOCK TABLES on TABLES for which the user also have SELECT privileges.
PROCESS
Allows use of the SHOW full processlist.
RELOAD
Allows use of FLUSH.
REPLICATION
Allows the user to ask where slave or master
CLIENT
Servers is.
REPLICATION SLAVE
Needed for replication slaves.
SELECT
Allows use of SELECT.
SHOW DATABASES
Allows use of SHOW DATABASES.
SHOW VIEW
Allows use of SHOW CREATE VIEW.
SHUTDOWN
Allows use of mysqladmin shutdown.
SUPER
Allows use of change master, KILL, PURGE master LOGS, and SET GLOBAL SQL statements. Allows mysqladmin debug command. Allows one extra connection to be made if maximum connections is reached.
UPDATE
Allows use of UPDATE.
USAGE
Allows connection without any specific privileges.
MySQL add user, give user authorization