Linux Operations Phase IV (v) MySQL User and Rights management

Linux Operational Phase IV (IV) MySQL User and Rights Management

1. Related concepts:

"mysql User:

similar VSFTPD virtual users;

password, its own unique encryption mechanism, PASSWORD function;

user name @ host;

User name within the characters, host (host name,IP, network address, wildcard (%,_);

--skip-name-resolve(skip name resolution to increase user connection speed)

The account is used for authentication;

>rename USER ' old_name ' @ ' host ' to ' new_name ' host; (rename user)

"Permission to access the table (data) after authorization;

table in MySQL's own library:user,db,host,tables_priv,columns_priv,procs_priv:

User:user Account,global privileges and other non-privileges column; global level (Super,replication Slave,shutdown)

DB:database-levelprivileges Library-level permissions

Host:obsolete deprecated

Tables-priv:table-level privileges table-level permissions (delete,alter,trigger,select,insert,update)

Columns_priv:column-level Privileges column-level permissions (select,insert,update)

Procs_priv:stored procedure andfunction privileges stored procedure and storage function permissions

Proxies_priv:proxy-user Privileges Delegate user Rights

MySQL will read these six tables at startup and generate authorization tables in memory, and each SQL statement executed will check for permission to read or write;

2,>select * from Db\g

>create temporary TABLE tb_name; (temporary table--memory table);

Usage (used only for connection Mysql-server and some simple commands)

trigger Trigger: Active database (triggers defined statements, such as logging, when performing certain operations)

>help Create user

>create USER ' username ' @ ' host ' [Identified by ' NEW_PW '];

>flush privileges;

>help Grant (for user authorization)

>grant privileges1,privileges2 on [object_type] db_name.tb_name to ' username ' @ ' host ' [Identified by ' NEW_PW '] [RE Quire none|ssl_option] [with with_option];

Note:db_name.tb_name(priv_level):

*

*.*

Db_name.*

Db_name.tb_name

Tb_name

Db_name.routine_name

With_option:

Grant_option users can delegate their own rights to other users, very dangerous, not recommended to use

| Max_queries_per_hour count per hour maximum number of queries

| Max_updates_per_hour count Hour max number of updates

| Max_connections_per_hour count can fail the maximum number of connections per hour

| Max_user_connections count How many times the same user account is connected

Example:>grant EXECUTE on FUNCTION db.abc to [email protected] '% '; (using this object_type when it is not possible to determine What object tb_name is, this example makes it clear that ABC is a function,tb_name is not necessarily a table, it is a stored procedure PROCEDURE and stored function functions )

>grant UPDATE on jiaowu.tutors to ' username ' @ '% ' REQUIRE SSL; (the connection must be SSL establishes a secure connection and can also be X509 )

>grant UPDATE (age) on Jiaowu,stu to ' username ' @ ' host '; (only one field is authorized)

>grant SUPER on * * to ' username ' @ ' host '; ( SUPER and with grantoption two items to be carefully authorized)

>show GRANTS for ' username ' @ ' host '; (View a user right, field authorization is not in the same row as the Library, table authorization)

>help REVOKE

>revoke priv1,priv2 on db_name.tb_name from ' user ' @ ' host ';

3. Re-retrieve the administrator password:

#service mysqld Stop

#vim/etc/rc.d/init.d/mysqld

$bindir/mysqld_safe--skip-grant-tables--skip-networking--datadir= "$datadir"--pid-file= "$mysqld _pid ... (Add --skip-grant-tables Bypass authorization table and --skip-networking prohibit network connection)

#service mysqld Start

#mysql

>update mysql.user SET password=password (' New_password ') WHERE user= ' root ';

( You cannot change the password with SET and mysqladmin only by manually changing the contents of the user table, because the authorization table has been skipped)

#service mysqld Stop(Stop the service to change/etc/rc.d/init.d/mysqld back to its original state and restart the service).

This article is to learn "ma Brother Network Video" made notes.

This article is from the "Linux operation and maintenance of difficult learning notes" blog, declined reprint!

Linux Operations Phase IV (v) MySQL User and Rights management