Security for a database could be at a physical level,network level,OS level,File System level or at User account level.The mysql.user table contains account identification tables,global privilege information,optional usage limitations etc.
basic account info consists of:
host value(location from where user can login),user value(username for login) - two halves of primary key
Optional password value
Host Property is classified by location specification options :
Named locations(localhost)
Specific IP address
Domain Name
Range of Ip address - network prefix format eg: 192.168.0.0/24 or wildcard ip address
There can also be wildcard host values such as:
In IP address - '192.168.1.%' or '192.168.1._ _'
In domain name '%.xyz.%' or '_ _ _.xyz.com'
To specify any location - ('%')
Creating a new User
mysql> use mysql
mysql> CREATE USER 'test'@'localhost' IDENTIFIED BY 'pwd';
Query OK, 0 rows affected (0.01 sec)
mysql> select user, host,authentication_string from user where user='test';
+-----------+-----------+-------------------------------------------+
| user | host | authentication_string |
+-----------+-----------+-------------------------------------------+
| test | localhost | *975B2CD4FF9AE554FE8AD33168FBFC326D2021DD |
+-----------+-----------+-------------------------------------------+
User Privileges
It is used to grant or restrict access to resources managed by the database server and only applies to database server.
The various scope hierarchy for user privileges are:
Global - available anywhere within preview of DB server.
Database - only to a particular database
Table - assigned table by table basis
Column - access column wise
Routine - privilege for using/modifying/dropping stored procedures
Privileges held in RAM when the user logs in to the particular scope.
Reviewing Privilege
mysql> show grants \G
*************************** 1. row ***************************
Grants for root@localhost: GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION
*************************** 2. row ***************************
Grants for root@localhost: GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION
2 rows in set (0.00 sec)
mysql> SHOW GRANTS FOR 'joe'@'%' \G
*************************** 1. row ***************************
Grants for joe@%: GRANT USAGE ON *.* TO 'joe'@'%'
1 row in set (0.00 sec)
USAGE - no privileges but only login and see information schema dbs
The various Privilege Tables used are:
user
db
tables_priv
columns_priv
procs_priv
Global Privileges
Blanket Access to database system including mysql db.Privileges usually reserved for admin accounts.
Eg:
"Select","Insert","Update" and "Delete"
"Create","Alter" and "Drop" - for both db and tables
"File","Process","Shutdown" - Only global privelege
"Create User" Privilege
"Super" privelege - eg change password,
mysql> GRANT SELECT ON *.* TO 'test'@'localhost';
Query OK, 0 rows affected (0.01 sec)
mysql> select * from user where user='test'\G
*************************** 1. row ***************************
Host: localhost
User: test
Select_priv: Y
Insert_priv: N
Update_priv: N
Delete_priv: N
Create_priv: N
Drop_priv: N
Reload_priv: N
Shutdown_priv: N
Process_priv: N
File_priv: N
Grant_priv: N
References_priv: N
Index_priv: N
Alter_priv: N
Show_db_priv: N
Super_priv: N
Create_tmp_table_priv: N
Lock_tables_priv: N
Execute_priv: N
Repl_slave_priv: N
Repl_client_priv: N
Create_view_priv: N
Show_view_priv: N
Create_routine_priv: N
Alter_routine_priv: N
Create_user_priv: N
Event_priv: N
Trigger_priv: N
Create_tablespace_priv: N
ssl_type:
ssl_cipher:
x509_issuer:
x509_subject:
max_questions: 0
max_updates: 0
max_connections: 0
max_user_connections: 0
plugin: mysql_native_password
authentication_string: *975B2CD4FF9AE554FE8AD33168FBFC326D2021DD
password_expired: N
password_last_changed: 2019-07-11 10:01:55
password_lifetime: NULL
account_locked: N
1 row in set (0.00 sec)
mysql> GRANT ALL ON *.* TO 'test'@'localhost';
Query OK, 0 rows affected (0.00 sec)
mysql> select * from user where user='test'\G
*************************** 1. row ***************************
Host: localhost
User: test
Select_priv: Y
Insert_priv: Y
Update_priv: Y
Delete_priv: Y
Create_priv: Y
Drop_priv: Y
Reload_priv: Y
Shutdown_priv: Y
Process_priv: Y
File_priv: Y
Grant_priv: N
References_priv: Y
Index_priv: Y
Alter_priv: Y
Show_db_priv: Y
Super_priv: Y
Create_tmp_table_priv: Y
Lock_tables_priv: Y
Execute_priv: Y
Repl_slave_priv: Y
Repl_client_priv: Y
Create_view_priv: Y
Show_view_priv: Y
Create_routine_priv: Y
Alter_routine_priv: Y
Create_user_priv: Y
Event_priv: Y
Trigger_priv: Y
Create_tablespace_priv: Y
ssl_type:
ssl_cipher:
x509_issuer:
x509_subject:
max_questions: 0
max_updates: 0
max_connections: 0
max_user_connections: 0
plugin: mysql_native_password
authentication_string: *975B2CD4FF9AE554FE8AD33168FBFC326D2021DD
password_expired: N
password_last_changed: 2019-07-11 10:01:55
password_lifetime: NULL
account_locked: N
1 row in set (0.00 sec)
Database Privileges
Database privileges apply to all resources within a specific database.
"Create","Alter","Drop" for tables within the database.
"select","insert","update","delete"
"execute"
mysql> GRANT SELECT,INSERT ON maintpro.* TO 'joe'@'%';
Query OK, 0 rows affected (0.01 sec)
mysql> select * from db\G
*************************** 1. row ***************************
Host: localhost
Db: sys
User: mysql.sys
Select_priv: N
Insert_priv: N
Update_priv: N
Delete_priv: N
Create_priv: N
Drop_priv: N
Grant_priv: N
References_priv: N
Index_priv: N
Alter_priv: N
Create_tmp_table_priv: N
Lock_tables_priv: N
Create_view_priv: N
Show_view_priv: N
Create_routine_priv: N
Alter_routine_priv: N
Execute_priv: N
Event_priv: N
Trigger_priv: Y
*************************** 2. row ***************************
Host: %
Db: maintpro
User: joe
Select_priv: Y
Insert_priv: Y
Update_priv: N
Delete_priv: N
Create_priv: N
Drop_priv: N
Grant_priv: N
References_priv: N
Index_priv: N
Alter_priv: N
Create_tmp_table_priv: N
Lock_tables_priv: N
Create_view_priv: N
Show_view_priv: N
Create_routine_priv: N
Alter_routine_priv: N
Execute_priv: N
Event_priv: N
Trigger_priv: N
2 rows in set (0.00 sec)
Table Privileges
Used when account must not have access to all tables.
"Create","Alter","Drop" for a table
"select","insert","update","delete"
"create view" , "Trigger"
mysql> GRANT UPDATE,DELETE ON maintpro.userdetails to 'joe'@'%';
Query OK, 0 rows affected (0.01 sec)
mysql> select * from tables_priv\G
*************************** 1. row ***************************
Host: localhost
Db: sys
User: mysql.sys
Table_name: sys_config
Grantor: root@localhost
Timestamp: 2017-07-14 12:22:18
Table_priv: Select
Column_priv:
*************************** 2. row ***************************
Host: %
Db: maintpro
User: joe
Table_name: userdetails
Grantor: root@localhost
Timestamp: 0000-00-00 00:00:00
Table_priv: Update,Delete
Column_priv:
2 rows in set (0.00 sec)
Set values in bit wise representation so faster for mysql to read access
Column Privileges
"select","insert","update"
"references"-use to perform some action
interaction with table privileges
mysql> GRANT SELECT (dbid,desc_id,info),INSERT(mail_flg,enddate),UPDATE(mail_flg,info) ON maintpro.alerts to 'joe'@'%';
Query OK, 0 rows affected (0.01 sec)
mysql> select * from columns_priv \G
*************************** 1. row ***************************
Host: %
Db: maintpro
User: joe
Table_name: alerts
Column_name: dbid
Timestamp: 0000-00-00 00:00:00
Column_priv: Select
*************************** 2. row ***************************
Host: %
Db: maintpro
User: joe
Table_name: alerts
Column_name: desc_id
Timestamp: 0000-00-00 00:00:00
Column_priv: Select
*************************** 3. row ***************************
Host: %
Db: maintpro
User: joe
Table_name: alerts
Column_name: info
Timestamp: 0000-00-00 00:00:00
Column_priv: Select,Update
*************************** 4. row ***************************
Host: %
Db: maintpro
User: joe
Table_name: alerts
Column_name: mail_flg
Timestamp: 0000-00-00 00:00:00
Column_priv: Insert,Update
*************************** 5. row ***************************
Host: %
Db: maintpro
User: joe
Table_name: alerts
Column_name: enddate
Timestamp: 0000-00-00 00:00:00
Column_priv: Insert
5 rows in set (0.00 sec)
This adds a column in the tables_priv:
*************************** 3. row ***************************
Host: %
Db: maintpro
User: joe
Table_name: alerts
Grantor: root@localhost
Timestamp: 0000-00-00 00:00:00
Table_priv:
Column_priv: Select,Insert,Update
3 rows in set (0.00 sec)
Routine Privileges
"execute","alter routine","grant","create routine"
mysql> GRANT EXECUTE ON FUNCTION world.demo1 TO 'joe'@'%';
Revoking Privileges
mysql> show grants for 'joe'@'%' \G
*************************** 1. row ***************************
Grants for joe@%: GRANT USAGE ON *.* TO 'joe'@'%'
*************************** 2. row ***************************
Grants for joe@%: GRANT SELECT, INSERT ON `maintpro`.* TO 'joe'@'%'
*************************** 3. row ***************************
Grants for joe@%: GRANT SELECT (desc_id, info, dbid), INSERT (enddate, mail_flg), UPDATE (mail_flg, info) ON `maintpro`.`alerts` TO 'joe'@'%'
*************************** 4. row ***************************
Grants for joe@%: GRANT UPDATE, DELETE ON `maintpro`.`userdetails` TO 'joe'@'%'
4 rows in set (0.00 sec)
mysql> REVOKE DELETE ON maintpro.userdetails FROM 'joe'@'%';
Query OK, 0 rows affected (0.00 sec)
Assigning & Changing Passwords
Users can change thier own password using:
SET PASSWORD = PASSWORD('
Administrators can change password for other accounts using:
SET PASSWORD FOR
UPDATE user SET password = PASSWORD('
FLUSH PRIVILEGES;
Limiting Activity By Account
max_user_connections limits no of simultaneous connection by an account.
Account Limitations Possible:
No of select queries per hour
No of updates per hour
No of connections per hour
No of simultaneous connections
GRANT USAGE ON *.* TO 'user'@'host' WITH MAX_QUERIES_PER_HOUR x MAX_UPDATES_PER_HOUR x MAX_CONNECTION_PER_HOUR x MAX_USER_CONNECTIONS x;
The Login Process
First Test - Client Host validation :Connection attempt packet includes IP address of machine from which the request was sent.Server tests whether there are any accounts whose host range includes this address.If not it returns the message:
Host
Second Test - Full account validation :Server now attempts to locate an account that uses the name supplied whose host range includes the client host IP address.If account does not exist, it returns:
Access denied for user
Third Test - Password Validation:Server hashes the supplied password.If password is incorrect:
Access denied for user
Additional Security Features
REQUIRE clause after IDENTIFIED BY
Options: SSL,X509,ISSUER,SUBJECT and CIPHER(NONE is default)
Proxy Privileges : User has privileges of another user.
GRANT PROXY ON
REQUIRE not allowed and WITH can only include GRANT OPTION
Requires an authentication plugin
Privilege Persistence
mysql> CREATE DATABASE test1;
Query OK, 1 row affected (0.01 sec)
mysql> GRANT SELECT ON test1.* TO 'joe'@'%';
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE DATABASE test1;
Query OK, 1 row affected (0.01 sec)
mysql> GRANT SELECT ON test1.* TO 'joe'@'%';
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW GRANTS FOR 'joe'@'%' \G *************************** 1. row *************************** Grants for joe@%: GRANT SELECT ON `test1`.* TO 'joe'@'%' **************************************************************
mysql> DROP DATABASE test1;
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW GRANTS FOR 'joe'@'%' \G *************************** 1. row *************************** Grants for joe@%: GRANT SELECT ON `test1`.* TO 'joe'@'%' **************************************************************
Alternate Authentication
version 5.5 IDENTIFIED WITH
Dropping a User Account
mysql> DROP USER ''@'localhost';
Query OK, 0 rows affected (0.02 sec)
Comments
Post a Comment