MYSQL create user and authorization and REVOKE user rights method _mysql

The implementation method of creating user and authorization in MySQL.

Operating Environment: Widnows XP Professional + MySQL5.0

First, create a user

Command: CREATE USER ' username ' @ ' host ' identified by ' password ';

Description: Username-you will create the username, host-Specify which host the user can log on, if the local user is available localhost, if you want the user to be able to log in from any remote host, you can use the wildcard character%. Password-The user's login password, the password can be null, if empty then the user can not need a password login MySQL server.

Example:

CREATE USER ' dog ' @ ' localhost ' identified by ' 123456 '; 
CREATE USER ' pig ' @ ' 192.168.1.101_ ' idendified by ' 123456 '; 
CREATE USER ' pig ' @ '% ' identified by ' 123456 '; 
CREATE USER ' pig ' @ '% ' identified by '; 
CREATE USER ' pig ' @ '% ';

Second, authorize

Command: GRANT privileges on databasename.tablename to ' username ' @ ' host '

Description: Privileges-user's operation rights, such as SELECT, INSERT, UPDATE, etc. (see the end of this article for a detailed list). Use all if you want to grant the permissions you have. DatabaseName-database name, tablename-table name, available * representations, such as *.*, if you want to give the user the appropriate action rights for all databases and tables.

Example:

GRANT SELECT, INSERT on Test.user to ' pig ' @ '% '; 
GRANT all on *.* to ' pig ' @ '% ';

Note: Users authorized by the above command cannot authorize other users, and if they want the user to be authorized to do so, use the command:
Grant privileges on Databasename.tablename to ' username ' @ ' host ' with Grant OPTION;

Three. Set up and change user password

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");

Four. Revoke user rights

Command: REVOKE privilege on databasename.tablename from ' username ' @ ' host ';

Description: Privilege, DatabaseName, TableName-with authorization section.

Example: REVOKE SELECT on *.* from ' pig ' @ '% ';

Note: If you are giving the user ' pig ' @ '% ' authorization to be like this (or similar): GRANT select on Test.user to ' pig '% ', then use revoke SELECT on *.* ' pig ' @ '% '; command not to withdraw Pin the user's SELECT action on the Users table in the test database. Conversely, if the authorization is using Grant SELECT on *.* to ' pig '% ', then revoke select on Test.user from ' pig ' @ '% '; Revoke SELECT permissions for the user table in the test database. (Www.jb51.net Script Academy)

Specific information can be used to show the grants for ' pig ' @ '% '; View.

Five. Delete User

Command: DROP USER ' username ' @ ' host ';

Schedule: Operating permissions 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 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 a lock tables on tables for which the user also has 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 are.
REPLICATION SLAVE Needed for REPLICATION slaves.
Select allows use of SELECT.
Show DATABASES allows use of the show DATABASES. Show VIEW allows use the show CREATEVIEW.
SHUTDOWN allows use of mysqladmin SHUTDOWN. SUPER allows use the 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 are.
Update allows use of update. USAGE allows connection without any specific privileges.