Day 16 Security

MySQL user format it as ‘User_name’@’hostname’

To create the user account use following syntax:

  • CREATE USER ‘User_name’@’Host_name’ IDENTIFIED BY <Pwd>;

 

We can specify same name user for different hosts. Hostname can be specify either of this:

‘local_host’ –> for the mysql server local user

‘ip address’ /’host_name -> for specific host or ip.

‘xxx.xxx.x.%’ -> specific to network

‘xxx.xxx.com’ – > specific fqdn network

Password can be saved at my.cnf on [client] group

[client]

password=your_pass

Restrict the my.cnf file to be access by others using chmod 400 or 600

chmod 600 .my.cnf

Using Environmental variable – Not Recommanded

Environment variable: MYSQL_PWD

 

This will create a user but that user is not having any access to mysql system, to grant an access to system we have to GRANT access to that USER as

  • GRANT <Privilege> ON <db.object> TO ‘User_name’@’Host_name’ WITH GRANT OPTION;

 

  •  

Here DB is the db name of the instance and object is tables we can specify * for all objects.

Following are list of privileges available to grant an access:

Server administration:

ALL [PRIVILEGES]

CREATE TABLESPACE

CREATE USER

PROCESS   PROXY

RELOAD

REPLICATION CLIENT

REPLICATION SLAVE

SHOW DATABASES

SHUTDOWN

SUPER

USAGE

 

Database/Objects 

CREATE

DROP

EVENT

GRANT OPTION

LOCK TABLES

REFERENCES

 

Objects   

ALTER

CREATE TEMPORARY TABLES

CREATE VIEW

DELETE     INDEX

INSERT

SELECT

SHOW VIEW

TRIGGER

UPDATE

 

Routine and File    

ALTER ROUTINE

CREATE ROUTINE

EXECUTE

FILE

Multiple Privilege can be granted with common separated.

Grant SELECT, UPDATE, INSERT, DELETE  ON DB.*  TO ‘User_name’@’Host_name’;

To see current users granted permission:

  • SHOW GRANTS;

 

To Show information about what all access user has granted.

  • SHOW GRANTS FOR ‘User_name’@’Host_name’;

To show information about user an account, use SHOW CREATE USER:

  • SHOW CREATE USER ‘User_name’@’Host_name’;

 

  •  

To revoke the granted access from user:

 

  • REVOKE priv_type ON [object_type] priv_level FROM user

 

To revoke all privileges

  • REVOKE ALL [PRIVILEGES], GRANT OPTION FROM user

 

  •  

To Drop the User

  • DROP USER ‘User_name’@’Host_name’;

 

 

To Change the password for the user before 5.7.6:

  • SET PASSWORD FOR ‘User_name’@’Host_name’ = <Pwd>;

 

or

  • GRANT  USAGE ON *.* TO ‘User_name’@’Host_name’ IDENTIFIED BY <pwd>;

 

For locking and unlocking user accounts using the ACCOUNT LOCK and ACCOUNT UNLOCK clauses for the CREATE USER and ALTER USER statements:

Alter user were used only for Password Expire options. But from 5.7.6 on ALTER USER is more options included.

Mysql version 5.7.6 on this has changed with ALTER USER:

ALTER USER

User: <USER NAME>

auth_option: < Identified by “XXX”>

tls_option: <SSL/xx>

resource_option: {MAX_QUERIES_PER_HOUR count | MAX_UPDATES_PER_HOUR count | MAX_CONNECTIONS_PER_HOUR count| MAX_USER_CONNECTIONS count}

password_option: {PASSWORD EXPIRE| PASSWORD EXPIRE DEFAULT| PASSWORD EXPIRE NEVER| PASSWORD EXPIRE INTERVAL N DAY}

lock_option: ACCOUNT LOCK/UNLOCK

tls_option : are the encryption options we can use for user password

resource_option: we can restrict user to use the limited resources from the system.

Password options: for password expire policy.

Lock option: account can be locked or unlocked.

eg

  • ALTER USER ‘user_name’@’localhost’ IDENTIFIED BY ‘new_password’ ACCOUNT LOCK/UNLOCK SSL WITH MAX_CONNECTIONS_PER_HOUR 20 PASSWORD EXPIRE INTERVAL 180 DAY

 

    •  
  • ALTER USER ‘user_name’@’localhost’ ACCOUNT LOCK/UNLOCK;

 

  •  

As you know we can perform the task with other ways as well like to change the password expire using my.cnf.

[mysqld]default_password_lifetime=180

 

Using global variable

SET GLOBAL default_password_lifetime = 180;

 

The default password_expired value is ‘N’ -0

We can provide resource options and other options at the time of creation of the user as well.

  • CREATE USER username IDENTIFIED BY ‘xxx’ 

 

  • WITH {resource_option:}

Some important points to be noted:

MySQL internally stores user information at mysql.user table(MySQL System Database). So this database should be highly protected.

Tables: userdbtables_privcolumns_priv, and procs_priv contains all user related information. DBA/Developer can directly work on this tables to perform the user access related activity. Beware this may have major impact if you unware of table structures.

Privilege Effects:

When Mysql starts, it retrieves information from system tables into memory. The in-memory tables become effective for access control at that point.

On changes of GRANTREVOKESET PASSWORD, or RENAME USER, the server notices these changes and loads the grant tables into memory again immediately.

For other DML access level information DBA has to explicitly reload the memory with flush-privileges operations by either of this command:

FLUSH PRIVILEGES statement or

mysqladmin flush-privileges

mysqladmin reload command

 

 

Reference:

https://www.ppgia.pucpr.br/pt/arquivos/techdocs/mysql/security.html

Advertisements
This entry was posted in MySQL, Others, Security, What I learned today and tagged , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.