MySQL Privilege¶
Why Privilege¶
Access Control is indispensable.
- whether or not a user is allowed to connect
- whether or not a user can perform the intended action
Levels¶
From MySQL :: MySQL 8.0 Reference Manual :: 13.7.1.6 GRANT Syntax
- Global
- Database
- Table
- Column
- Procedure
(6.Proxy)
priv_level: {
*
| *.*
| db_name.*
| db_name.tbl_name
| tbl_name
| db_name.routine_name
}
Flexible
Commands¶
GRANT
GRANT ALL PRIVILEGES ON <some_level> TO <some_user>@<some_host> IDENTIFIED BY <some_password>;
GRANT ALL PRIVILEGES ON *.* TO 'me'@'%' IDENTIFIED BY 'MyPass';
GRANT ALL PRIVILEGES ON MyDB.* TO 'you'@'%' IDENTIFIED BY 'YourPass';
GRANT ALL PRIVILEGES ON MyDB.* TO 'another_user_can_grant_permissions'@'localhost' WITH GRANT OPTION;
GRANT SELECT ON YourDB.* TO 'role3';
About ‘Identified By Password - StackOverflow’
The “host” is the allowed connection source.
*@localhost
- means only connection from localhost are allowed
@%
- means any source is allowed
one.such.domain
- specify a domain
*.such.domain
- use wildcard
0.0.0.0
- specify an ip
192.168.1.9/255.255.255.0
- specify a segment
REVOKE
REVOKE ALL PRIVILEGES FROM user [, user]…
REVOKE ALL PRIVILEGES, GRANT OPTION FROM user [, user]…
Connection Authorization¶
In order to perform an action, a user must
- connected from specific source
- exist (has a record in mysql)
- give correct authentication credential
- has permission to perform that action on the target
By default, root
cannot be accessed from connections other than localhost.
Usually we create a specific database and assign the permissions on that database.