MySQL create user and authorization

2010-08-04  来源:本站原创  分类:Database  人气:233 

First, create a user:

Command: CREATE USER ' username '@' Host 'IDENTIFIED BY' password ';

Description: username - you will create a user name, host - the specified host in which the user can login, if a local user can be localhost, if you want the user can login from any remote host, you can use the wildcard%. Password - The user's login password, the password can be empty, if empty then the user can login without a password server.

Examples: 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, authorized:

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

Description: privileges - the user's operating authority, such as SELECT, INSERT, UPDATE, etc. (a detailed list, see the text of the final surface). If you want to grant the permission to use the ALL.; Databasename - the database name, tablename-table name, if you want to grant The user database and tables on all the appropriate operating authority is available * that if *.*.

Example: GRANT SELECT, INSERT ON test.user TO 'Pig'@'%' ;
GRANT ALL ON *.* TO 'Pig'@'%' ;

Note: The above command authorized users to other users not authorized, if you want the user can authorize, with the following command:
GRANT privileges ON databasename.tablename TO 'username' @ 'Host' WITH GRANT OPTION;

3. Settings and change the user password

Command: SET PASSWORD FOR 'username' @ 'Host' = PASSWORD ('NewPassword'); if the current login user with SET PASSWORD = PASSWORD ("newpassword");

Examples: SET PASSWORD FOR 'Pig'@'%' = PASSWORD ("123456");

4. Revoke user rights

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

Description: privilege, databasename, tablename - part of the same authority.

Example: REVOKE SELECT ON *.* FROM 'Pig'@'%' ;

Note: If you give the user 'pig'@'%' when authorized so (or similar): GRANT SELECT ON test.user TO 'Pig'@'%' , then use REVOKE SELECT ON *.* FROM 'Pig'@'%' ; command does not remove the user on the test database, user table SELECT operation. Conversely, if authorized to use the GRANT SELECT ON *.* TO 'Pig'@'%' ; the REVOKE SELECT ON test.user FROM 'Pig'@'%' ; command can not remove that user user database on the test table Select permissions.

Specific information can use the command SHOW GRANTS FOR 'Pig'@'%' ; view.

5. Delete users

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

Schedule: permissions in MySQL in the operation

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 master

CLIENT

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.

Reprint: http://hi.baidu.com/fwso/blog/item/658c00555bdd1cc5b645aee0.html

相关文章
  • MySQL create user and authorization 2010-08-04

    First, create a user: Command: CREATE USER ' username '@' Host 'IDENTIFIED BY' password '; Description: username - you will create a user name, host - the specified host in which the user can login, if a local user can be localhost, if you want the u

  • MySQL create user and authorization operations. 2011-04-11

    create user '<user_name>' identified by '<password>' grant on *.* to '<user_name>' with grant option

  • mysql create function problem (change) 2010-08-29

    mysql create function problem (change) 2010-05-26 19:37 Today, when sorting the data, but also encountered MySQL functions can not create the problem, also encountered many times before to solve is solved, but the solution always remember, do not wri

  • Create an index in MySQL (Create Index) method, and grammatical structure and examples 2010-09-10

    Create an index in MySQL (Create Index) method, and grammatical structure and examples CREATE INDEX Syntax CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name [Index_type] ON tbl_name (index_col_name ,...) [Index_type] index_col_name: col_name [(le

  • mysql database establishment and authorization 2010-11-15

    mysql> create database django default charset=utf8 ; mysql> GRANT ALL PRIVILEGES ON dango.* TO 'django'@'%' IDENTIFIED BY 'dbdjango' WITH GRANT OPTION; mysql> flush privileges;

  • mysql create function problems (transfer) 2010-08-29

    mysql create function problems (transfer) 2010-05-26 19:37 Today, when sorting the data, but also encountered MySQL functions can not create problems previously encountered many times to solve is solved, but the solution always remember, there is no

  • Create an index in MySQL (Create Index) method and syntax and examples 2010-09-10

    Create an index in MySQL (Create Index) method and syntax and examples CREATE INDEX Syntax CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name [Index_type] ON tbl_name (index_col_name ,...) [Index_type] index_col_name: col_name [(length)] [ASC | DE

  • mysql create user. authorized to use the database to delete the user of the two methods to solve 2011-02-25

    Log in as root mysql; Create a user and authorized to use a database approach mysql> use mysql Database changed mysql> insert into user(Host,User,Password) values("localhost","little_bill",password("1234")); Query OK, 1

  • linux mysql create function fails, the solution 2011-06-27

    When the function under linux mysql create fails, the following solution, everyone needs to study: 1.mysql> set global log_bin_trust_function_creators = 1; (2) to perform the start mysql: log_bin_trust_function_creators = 1; 3 In mysql.ini (linux und

  • mysql create table and set the primary key from the increase 2011-10-25

    mysql Create a table : mysql> create table user( -> userid int(4) primary key not null auto_increment, -> username varchar(16) not null, -> userpassword varchar(32) not null -> ); create table log( logid int(4) primary key not null auto_inc

  • Lost connection to MySQL server at 'reading authorization packet', system error: 0 2015-01-29

    这篇文章主要介绍了Lost connection to MySQL server at 'reading authorization packet', system error: 0错误解决方法,需要的朋友可以参考下 1.进入Mysqld 如果已经设置Mysql/Bin环境变量,直接在CMD里输入命令,如果没有设置Mysql环境变量,去Mysql安装目录的Bin文件夹里运行 F:\AppServ\MySQL\bin\mysql.exe -u root -p 其中root是用户名,然后就可以进入M

  • mysql create database utf8 2014-10-19

    1.创建数据库表 mysql>CREATE DATABASE IF NOT EXISTS my_db default charset utf8 COLLATE utf8_general_ci; #注意后面这句话 "COLLATE utf8_general_ci",大致意思是在排序时根据utf8变码格式来排序 #那么在这个数据库下创建的所有数据表的默认字符集都会是utf8了 mysql>create table my_table (name varchar(20) not n

  • MySQL create user, authorization, revocation, delete 2011-03-30

    First, create a user: Command: CREATE USER 'username' @ 'host 'IDENTIFIED BY' password '; Description: username - you will create a user name, host - Specify the host on which the user can log in, if it is a local user can be localhost, if you want t

  • mysql remote access and authorization 2010-05-01

    Related articles: Installation mysql5.1 (linux (red hat 3)) I see pros and cons of Spring Security Recommended circle: rights management for more recommended [MYSQL] to create a remote login user and authorize Database 2009-04-29 14:07:01 268 Comment

  • Mysql user creation and authorization 2011-03-04

    First, create user : Command :CREATE USER 'username'@'host' IDENTIFIED BY 'password'; Explain :username - You will create a user name, host - Specify the host on which the user can log in , If a local user can localhost, if you want the user can logi

  • MySQL create user - user license 2011-06-04

    1.CREATE USER CREATE USER user [IDENTIFIED BY [PASSWORD] 'password'] [, User [IDENTIFIED BY [PASSWORD] 'password']] ... EG: create user wiki identified by 'wiki'; CREATE USER to create a new MySQL accounts. To use the CREATE USER, you must have the m

  • mysql create user Reserved 2010-04-09

    1.CREATE USER CREATE USER user [IDENTIFIED BY [PASSWORD] 'password'] [, user [IDENTIFIED BY [PASSWORD] 'password']] ... CREATE USER to create a new MySQL account. To use the CREATE USER, you must have the global CREATE USER mysql database, permission

  • mysql create a foreign key code associated with their total prompt "ERROR 1005: Can't create table (errno: 150)" 2010-06-17

    http://eternal1025.javaeye.com/blog/425311 In order to create two MySQL tables between a foreign key relationship must meet the following three conditions: * Two tables must be InnoDB table type. * Use foreign key relationship must be index-type doma

  • my-sql create stored procedures 2010-10-17

    use test create procedure show_a () select * from a call show_a (); Create and execute simple Mysql in the above is not the same sql server, create procedure show_a as select * from a

  • mysql create users and forget the root password 2011-04-21

    1 Create mysql user Quote grant all on myidb .* to myidb @ 'localhost' identified by'password '; revoke all on myidb .* from myidb @ 'localhost'; Description: In addition to grant / revoke commands, and other rights associated with the implementation