Create user authorization in Mysql and create user authorization in mysql

Create user authorization in Mysql and create user authorization in mysql

1. Create a user:

Command: create user 'username' @ 'host' identified by 'Password ';

Description: username-the username you will create, host-specifies the host on which the user can log on. If a local user can use localhost, if you want to allow the user to log on from any remote host, you can use the wildcard %. password-the user's login password. The password can be blank. If it is blank, the user can log on to the server without the password.

Example: create user 'dog '@ 'localhost' identified by '123 ';
Create user 'pig' @ '192. 168.1.101 _ 'idendified BY '123 ';
Create user 'pig' @ '%' identified by '000000 ';
Create user 'pig' @ '%' identified '';
Create user 'pig' @ '% ';

2. Authorization:

Command: GRANT privileges ON databasename. tablename TO 'username' @ 'host'

Pri: privileges-operation permissions of users, such as SELECT, INSERT, and UPDATE (for detailed list, see the end of this Article ). use ALL .; databasename-Database Name, tablename-table name. If you want to grant the user the corresponding operation permissions on all databases and tables, it can be represented by *, as shown in *. *.

Example: grant select, insert on test. user TO 'pig' @ '% ';
Grant all on *. * TO 'pig' @ '% ';

Note: The user authorized with the preceding command cannot authorize other users. to authorize the user, run the following command:
GRANT privileges ON databasename. tablename TO 'username' @ 'host' with grant option;

3. Set and change user passwords

Command: set password for 'username' @ 'host' = PASSWORD ('newpassword'); if the current login user uses set password = PASSWORD ("newpassword ");

Example: set password for 'pig' @ '%' = PASSWORD ("123456 ");

Iv. Revoke User Permissions

Command: REVOKE privilege ON databasename. tablename FROM 'username' @ 'host ';

Note: privilege, databasename, tablename-same as the authorization section.

Example: revoke select on *. * FROM 'pig' @ '% ';

Note: If you authorize the user 'pig' @ '%' like this (or similar): grant select on test. user TO 'pig' @ '%', use revoke select on *. * FROM 'pig' @ '%'; the command does not cancel the SELECT Operation on the user table in the test database. conversely, grant select on * is used for authorization *. * TO 'pig' @ '%'; then revoke select on test. user FROM 'pig' @ '%'; the command cannot revoke this user's Select permission on the user table in the test database.

FOR more information, run the show grants for 'pig' @ '%' command.

5. delete a user

Command: drop user 'username' @ 'host ';

Appendix: Operation permissions in MySQL

 

 

ALTER	Allows useALTER TABLE.
ALTER ROUTINE	Alters or drops stored routines.
CREATE	Allows useCREATE TABLE.
CREATE ROUTINE	Creates stored routines.
CREATE TEMPORARY TABLE	Allows useCREATE TEMPORARY TABLE.
CREATE USER	Allows useCREATE USER,DROP USER,RENAME USER, AndREVOKE ALL PRIVILEGES.
CREATE VIEW	Allows useCREATE VIEW.
DELETE	Allows useDELETE.
DROP	Allows useDROP TABLE.
EXECUTE	Allows the user to run stored routines.
FILE	Allows useSELECT...INTO OUTFILEAndLOAD DATA INFILE.
INDEX	Allows useCREATE INDEXAndDROP INDEX.
INSERT	Allows useINSERT.
LOCK TABLES	Allows useLOCK TABLESOn tables for which the user also hasSELECTPrivileges.
PROCESS	Allows useSHOW FULL PROCESSLIST.
RELOAD	Allows useFLUSH.
REPLICATION	Allows the user to ask where slave or master
CLIENT	Servers are.
REPLICATION SLAVE	Needed for replication slaves.
SELECT	Allows useSELECT.
SHOW DATABASES	Allows useSHOW DATABASES.
SHOW VIEW	Allows useSHOW CREATE VIEW.
SHUTDOWN	Allows useMysqladmin shutdown.
SUPER	Allows useCHANGE MASTER,KILL,PURGE MASTER LOGS, AndSET GLOBALSQL statements. AllowsMysqladmin debugCommand. Allows one extra connection to be made if maximum connections are reached.
UPDATE	Allows useUPDATE.
USAGE	Allows connection without any specific privileges.