MySQL user creation and authorization and revocation of user permissions, mysql user permissions

MySQL user creation and authorization and revocation of user permissions, mysql user permissions

How to create users and authorizations in MySQL.

Running Environment: widnows xp professional + MySQL5.0

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 mysql server without the password.

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'@'%';

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. (www.jbxue.com script School)

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 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 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 has SELECT privileges.PROCESS Allows use of SHOW FULL PROCESSLIST.RELOAD Allows use of FLUSH.REPLICATION Allows the user to ask where slave or masterCLIENT servers are.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 are reached.UPDATE Allows use of UPDATE.USAGE Allows connection without any specific privileges.

How does MySQL authorize a self-created user such as daitest to create a new database? Command

Take a look
In mysql, you can grant one or more permissions to a user, such as select, insert, update, and delete. The grant command is used in the following format:
Grant permission on database objects to users
1. grant normal data users the right to query, insert, update, and delete all table data in the database.
Grant select on testdb. * to common_user @ '%'
Grant insert on testdb. * to common_user @ '%'
Grant update on testdb. * to common_user @ '%'
Grant delete on testdb. * to common_user @ '%'
Alternatively, replace the following with a mysql command:
Grant select, insert, update, delete on testdb. * to common_user @ '%'

2. grant database developers to create tables, indexes, views, stored procedures, and functions... .
Grant permissions to create, modify, and delete mysql Data Table structures.
Grant create on testdb. * to developer @ '192. 192.% ';
Grant alter on testdb. * to developer @ '192. 192.% ';
Grant drop on testdb. * to developer @ '192. 192.% ';
Grant the mysql foreign key operation permission.
Grant references on testdb. * to developer @ '192. 192.% ';
Grant the permission to operate mysql temporary tables.
Grant create temporary tables on testdb. * to developer @ '2017. 192.% ';
Grant the permission to operate mysql indexes.
Grant index on testdb. * to developer @ '192. 192.% ';
Grant permissions to operate the mysql view and view the source code.
Grant create view on testdb. * to developer @ '192. 192.% ';
Grant show view on testdb. * to developer @ '192. 192.% ';
Grant permissions to operate mysql stored procedures and functions.
Grant create routine on testdb. * to developer @ '192. 192.% ';-now, can show procedure status
Grant alter routine on testdb. * to developer @ '192. 192.% ';-now, you can drop a procedure
Grant execute on testdb. * to developer @ '192. 192.% ';

3. grant common dba permission to manage a mysql database.
Grant all privileges on testdb to dba @ 'localhost'
The keyword "privileges" can be omitted.

4. grant senior dba permission to manage all databases in mysql.
Grant... the remaining full text>

Mysql, I want to use a new user to directly create a database? Must I use the root user to create a database and then authorize it?

The root user is the default Super administrator and has the highest permissions. Of course, you can manage databases created by any user.
You can create a new ppamdin to obtain management permissions, but not the highest permissions. It can only manage databases authorized by root and execute root-authorized operation commands.
Therefore, the root you want cannot see the database created by pppadmin. Although root can ignore it