![首頁 Logo Keith ]p !!](/themes/mytheme/logo.png){kind=logo}
最後更新: 2020-08-01
目錄
- Show all user
- 建立 User
- 刪除 User
- 權限設定
- 更新 User Password
- system account
- localhost 與 127.0.0.1 的分別
- GRANT Permission
- REFERENCES Permission
- Scripts
Show All User
# User 與 Host 係一對的 !!
SELECT User,Host FROM mysql.user;
+-----------+-----------+ | User | Host | +-----------+-----------+
User table schema:
describe mysql.user;
# Version: 5.1.73
+-----------------------+-----------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------------+-----------------------------------+------+-----+---------+-------+
| Host | char(60) | NO | PRI | | |
| User | char(16) | NO | PRI | | |
| Password | char(41) | NO | | | |
| Select_priv | enum('N','Y') | NO | | N | |
| Insert_priv | enum('N','Y') | NO | | N | |
| Update_priv | enum('N','Y') | NO | | N | |
| Delete_priv | enum('N','Y') | NO | | N | |
| Create_priv | enum('N','Y') | NO | | N | |
| Drop_priv | enum('N','Y') | NO | | N | |
| Reload_priv | enum('N','Y') | NO | | N | |
| Shutdown_priv | enum('N','Y') | NO | | N | |
| Process_priv | enum('N','Y') | NO | | N | |
| File_priv | enum('N','Y') | NO | | N | |
| Grant_priv | enum('N','Y') | NO | | N | |
| References_priv | enum('N','Y') | NO | | N | |
| Index_priv | enum('N','Y') | NO | | N | |
| Alter_priv | enum('N','Y') | NO | | N | |
| Show_db_priv | enum('N','Y') | NO | | N | |
| Super_priv | enum('N','Y') | NO | | N | |
| Create_tmp_table_priv | enum('N','Y') | NO | | N | |
| Lock_tables_priv | enum('N','Y') | NO | | N | |
| Execute_priv | enum('N','Y') | NO | | N | |
| Repl_slave_priv | enum('N','Y') | NO | | N | |
| Repl_client_priv | enum('N','Y') | NO | | N | |
| Create_view_priv | enum('N','Y') | NO | | N | |
| Show_view_priv | enum('N','Y') | NO | | N | |
| Create_routine_priv | enum('N','Y') | NO | | N | |
| Alter_routine_priv | enum('N','Y') | NO | | N | |
| Create_user_priv | enum('N','Y') | NO | | N | |
| Event_priv | enum('N','Y') | NO | | N | |
| Trigger_priv | enum('N','Y') | NO | | N | |
| ssl_type | enum('','ANY','X509','SPECIFIED') | NO | | | |
| ssl_cipher | blob | NO | | NULL | |
| x509_issuer | blob | NO | | NULL | |
| x509_subject | blob | NO | | NULL | |
| max_questions | int(11) unsigned | NO | | 0 | |
| max_updates | int(11) unsigned | NO | | 0 | |
| max_connections | int(11) unsigned | NO | | 0 | |
| max_user_connections | int(11) unsigned | NO | | 0 | |
+-----------------------+-----------------------------------+------+-----+---------+-------+
建立 User
方式1: 建立帳戶
CREATE USER 'username'@'127.0.0.1' IDENTIFIED BY 'some_pass';
拍派權限
# 所有 DB
GRANT ALL PRIVILEGES ON *.* TO 'username'@'127.0.0.1'
# 某個 DB
GRANT ALL PRIVILEGES ON DB.* TO 'username'@'127.0.0.1';
# 某 DB 某 TABLE
GRANT ALL PRIVILEGES ON DB.TABLE TO 'username'@'127.0.0.1';
方式2: 建立帳戶並設定權限
GRANT ALL PRIVILEGES ON *.* TO 'root'@'127.0.0.1' IDENTIFIED BY 'some_pass';
GRANT OPTION
GRANT GRANT OPTION ON *.* TO 'root'@'127.0.0.1';
更新權限:
FLUSH PRIVILEGES;
Remark 1
'%' 代表任何 hosts
A Range of IP Addresses:
1) '192.168.1.%'
2) '192.168.1.0/255.255.255.0'
Remark 2
ON Database.Table
*.* => 所有 DB 的所有 Tables
myDB.* => 某 DB 內的所有 Tables
刪除 User
DROP USER 'USER'@'HOST';
account name => 'USER'@'HOST'
If you specify only the username part of the account name, a host name part of '%' is used.
當 User 不存在時會有以下 Err
ERROR 1396 (HY000): Operation DROP USER failed for 'USER'@'%'
mysql allow remote access root
Step 1: "#" 了 bind-address
#bind-address = 127.0.0.1
Step 1: GRANT PRIVILEGES
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '??????????';
GRANT GRANT OPTION ON *.* TO 'root'@'%';
權限設定
查看權限:
mysql> SHOW GRANTS FOR 'admin'@'localhost';
+-----------------------------------------------------+ | Grants for admin@localhost | +-----------------------------------------------------+ | GRANT RELOAD, PROCESS ON *.* TO 'admin'@'localhost' | +-----------------------------------------------------+
* The host part, if omitted, defaults to '%'
OR
SHOW GRANTS;
SHOW GRANTS FOR CURRENT_USER;
SHOW GRANTS FOR CURRENT_USER();
設定權限:
GRANT ALL PRIVILEGES ON *.* TO 'monty'@'localhost' WITH GRANT OPTION;
PRIVILEGES:
- ALL ("ALL" 是沒有包 "GRANT")
- SELECT
- INSERT
- DELETE
取消權限:
REVOKE
priv_type [(column_list)]
[, priv_type [(column_list)]] ...
ON [object_type] priv_level
FROM user [, user] ...priv_type: ALL [PRIVILEGES] ...
更新 User Password
# MySQL 5.6 or Older
SET PASSWORD FOR 'username'@'host' = PASSWORD('password');
OR
UPDATE mysql.user SET password=PASSWORD('password') WHERE user='username' AND host='host' Limt 1;
# MySQL 5.7.6 and later or MariaDB 10.1.20 and later:
ALTER USER 'user'@'localhost' IDENTIFIED BY 'newPass';
OR
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'your_password_here';
FLUSH PRIVILEGES;
System Account
'mysql.sys'@'localhost'
Used as the DEFINER for sys schema objects.
Use of the mysql.sys account avoids problems that occur if a DBA renames or removes the root account.
This account is locked so that it cannot be used for client connections.
'mysql.session'@'localhost'
Used internally by plugins to access the server.
This account is locked so that it cannot be used for client connections.
localhost 與 127.0.0.1 的分別
MariaDB [(none)]> select USER(),CURRENT_USER();
+-----------------+----------------+ | USER() | CURRENT_USER() | +-----------------+----------------+ | webdb@localhost | webdb@% | +-----------------+----------------+
For connections to localhost, MySQL programs attempt to connect to the local server by using a Unix socket file.
This occurs even if a --port or -P option is given to specify a port number.
To ensure that the client makes a TCP/IP connection to the local server,
use --host or -h to specify a host name value of 127.0.0.1, or the IP address or name of the local server.
You can also specify the connection protocol explicitly, even for localhost, by using the --protocol=TCP option.
GRANT Permission
GRANT OPTION allows a user to pass on his privileges at the level which they 're given, or any lower level
REFERENCES Permission
It allows a user to create a foreign key constraint on a table.
Create foreign key constraints using the REFERENCES clause in the CREATE TABLE or ALTER TABLE statements
Scripts
update_mysql_pass.sh
#!/bin/bash OldPass=? MyPass=? mysql -p$OldPass -e "SET PASSWORD FOR 'root'@'localhost' = PASSWORD(\"$MyPass\");" mysql -p$OldPass -e "SET PASSWORD FOR 'root'@'192.168.200.%' = PASSWORD(\"$MyPass\");" mysql -p$OldPass -e "flush privileges;"
create-db-and-user.sh
#!/bin/bash
ROOTPW=?
DB=?
DBPW=?
s=$(mktemp)
cat > $s <<EOF
CREATE DATABASE $DB CHARACTER SET utf8;
CREATE USER "$DB"@"%" IDENTIFIED BY "$DBPW";
GRANT ALL PRIVILEGES ON ${DB}.* TO "$DB"@"%";
FLUSH PRIVILEGES;
EOF
mysql -p$ROOTPW < $s
rm -f $s
# Verify
mysql -u$DB -p$DBPW -e'SHOW GRANTS FOR CURRENT_USER;'