Home / server / Questions / 632719
In Process
Sfisioza
Asked: 2014-10-02 03:58:23 +0800 CST

Allow mysql user to be used just by specified user

  • 772

Currently any system user can run:

systemuser$ mysql -u mysqluser -p

(assuming he knows the password).

Is there a way to deny mysql (cli) to use particular mysql user?

eg. 

systemuser$   mysql -u mysqluser -p'validmypass'
   (success)
otheruser$    mysql -u mysqluser -p'validpass'
   (you don't have access)
otheruser$    mysql -u anotheruser -p'validpass'
   (success)

(ie. just particular system user may use particular mysql user).

mysql

2 Answers

  1. I do not think, that this is a good idea. The mysqld process is listening to the network on some port (3306), so preventing particular system users to connect to particular port with particular mysql username could be hard. Why to give some people a mysql password, if you do not want them to login? What do you want to achieve with such setup?

    However, you could:

    1. Deny some local users from using mysql command (in this case they could use other software to login). See for details: Preventing users from running certain programs. The easiest way is to change file permissions of /usr/bin/mysql, but then the users will be still able to run other copies of mysql on the system. Even if you will block this, they will be able to connect using other software.

    2. Deny some local users from connecting to mysql port. See this question for details: How to block access to a local user to a local port?

    • 1

  2. Users in standard mysql auth are made up of a username and host portion (username@host) and protected by a password. That's it. If you want to use standard MySQL user authentication, you can't achieve this exactly.

    There are ways of doing operating system authentication within MySQL, but this is well documented. e.g. PAM authentication: http://dev.mysql.com/doc/refman/5.6/en/pam-authentication-plugin.html.

    Another way (MySQL 5.6+) may be to change the password, so it's unknown to any users, and use the MySQL Config Editor http://dev.mysql.com/doc/refman/5.6/en/mysql-config-editor.html to create a login file which specific users have access to, or have in their home directory. This way only certain users can see the config

    • 0