![首頁 Logo Keith ]p !!](/themes/mytheme/logo.png){kind=logo}
最後更新: 2020-08-01
目錄
- Show all user
- 建立 User
- User 的 Host
- 刪除 User
- 權限設定
- 更新 User Password
- system account
- localhost 與 127.0.0.1 的分別
- GRANT Permission
-
Permission
REFERENCES
EXECUTE
SHOW VIEW - 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 'username'@'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 的 Host
# MySQL 8.0.23 支援 CIDR notation
192.168.100.0/24
# MySQL 5.7
192.168.100.0/255.255.255.0 # Netmask notation
# MySQL 5.1
192.168.100.%
刪除 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
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
The ability to execute the function or procedure is being revoked.
e.g.
GRANT EXECUTE ON [ PROCEDURE | FUNCTION ] object TO user;
Enables use of the SHOW CREATE VIEW statement. This privilege is also needed for views used with EXPLAIN.
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;'