User management and permissions and settings - mysql

MySQL is one of the most popular database management systems in the world. The book starts with an introduction to simple data retrieval and gradually goes into more complex content, including the use of joins, subqueries, regular expressions and full-text based searches, stored procedures, cursors, triggers, table constraints, etc. Through highlighted chapters, the knowledge that readers should master is described in a clear, systematic and concise manner, so that they can immediately and inadvertently increase their skills. This section mainly describes the related commands of mysql user management and permission settings.

User Management

mysql>use mysql;

View

mysql>select host,user,password from user ;

Create

mysql>create user zx_root;

Modify

mysql>rename user feng to newuser; //mysql 5之后可以使用，之前需要使用update 更新user表

Delete

mysql>drop user newuser;   //mysql5之前删除用户时必须先使用revoke 删除用户权限，然后删除用户，mysql5之后drop 命令可以删除用户的同时删除用户的相关权限

Change password

mysql>set password for zx_root =password('xxxxxx');
mysql>update  mysql.user  set  password=password('xxxx')  where user='otheruser'

View user permissions

mysql>show grants for zx_root;

Grant permissions

mysql>grant select on dmc_db.*  to zx_root;

Recycle permissions

mysql>revoke  select on dmc_db.*  from  zx_root;  //如果权限不存在会报错

The above command can also use multiple permissions at the same time Grant and recycle, use commas to separate permissions

mysql>grant select，update，delete  ，insert  on dmc_db.*  to  zx_root;

If you want to see the results immediately, use the

flush  privileges ;

command to update

You must give the following information when setting permissions

1, Permissions to be granted

2, Database or table to which access permissions are granted

3, User name

grant and revoke can be used in To control access permissions at one level

1, for the entire server, use grant ALL and revoke ALL

2, for the entire database, use on database.*

3, for the feature table, Use on database.table

4, specific column

5, specific stored procedure

The meaning of the value of the host column in the user table

% Match all hosts

localhost localhost will not be parsed into an IP address and will be connected directly through UNIXsocket

127.0.0.1 will be connected through the TCP/IP protocol and can only be accessed on this machine

::1 ::1 is compatible with ipv6, indicating the same as 127.0.0.1 of ipv4

grant ordinary data users have 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@'%'

Or, use a MySQL command instead:

grant select, insert, update, delete on testdb.* to common_user@'%'

9>.grant Database developer, create tables, indexes, views, stored procedures, and functions. . . and other permissions.

grant Permission to create, modify, and delete MySQL data table structures.

grant create on testdb.* to developer@'192.168.0.%';
grant alter on testdb.* to developer@'192.168.0.%';
grant drop on testdb.* to developer@'192.168.0.%';

grant permission to operate MySQL foreign keys.

grant references on testdb.* to developer@'192.168.0.%';

grant permission to operate MySQL temporary tables.

grant create temporary tables on testdb.* to developer@'192.168.0.%';

grant permission to operate MySQL indexes.

grant index on testdb.* to developer@'192.168.0.%';

grant permission to operate MySQL views and view view source code.

grant create view on testdb.* to developer@'192.168.0.%';
grant show view on testdb.* to developer@'192.168.0.%';

grant permission to operate MySQL stored procedures and functions.

grant create routine on testdb.* to developer@'192.168.0.%'; -- now, can show procedure status
grant alter routine on testdb.* to developer@'192.168.0.%'; -- now, you can drop a procedure
grant execute on testdb.* to developer@'192.168.0.%';

10>.grant Ordinary DBA authority to manage a MySQL database.

grant all privileges on testdb to dba@'localhost'

Among them, the keyword "privileges" can be omitted.

11>.grant Senior DBA permission to manage all databases in MySQL.

grant all on *.* to dba@'localhost'

12>.MySQL grant permissions can be applied at multiple levels.

1. Grant applies to the entire MySQL server:

grant select on *.* to dba@localhost; -- dba 可以查询 MySQL 中所有数据库中的表。
grant all on *.* to dba@localhost; -- dba 可以管理 MySQL 中的所有数据库

2. Grant applies to a single database:

grant select on testdb.* to dba@localhost; -- dba 可以查询 testdb 中的表。

3. Grant applies to a single data table:

grant select, insert, update, delete on testdb.orders to dba@localhost;

4. Grant acts on the columns in the table:

grant select(id, se, rank) on testdb.apache_log to dba@localhost;

5. Grant acts on stored procedures and functions:

grant execute on procedure testdb.pr_add to 'dba'@'localhost'
grant execute on function testdb.fn_add to 'dba'@'localhost'

Note: After modifying the permissions, you must Refresh the service, or restart the service. To refresh the service, use: FLUSH PRIVILEGES.

Related recommendations:

Related knowledge of MySQL query time

The relationship between PHP and MySQL

The relationship between php, apache and mysql

The above is the detailed content of User management and permissions and settings - mysql. For more information, please follow other related articles on the PHP Chinese website!