Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Is there a way to restrict a user's host setting so that (s)he can only connect by socket and not TCP/IP? I'd like to have this setting for the root account.

edit: As Abdul Manaf pointed out skip-networking can be used to turn off TCP/IP connectivity altogether. But can it be done on a user account basis?

share|improve this question
add comment

2 Answers

If you only want to listen MySQL connections only using socket add a line in my.cnf and restart MySQL server.

skip-networking

This is recommended practice when there is no reason for it to listen on TCP.

share|improve this answer
add comment

To turn off specific users, simply assign that user at the localhost level.

GRANT ALL PRIVILEGES ON *.* TO myuser@localhost;

Logging in through localhost specifically looks for the socket file.

Suppose you have three users you want to limit to

  • user1@'127.0.0.1'
  • user2@'%'
  • user3@'10.20.%'

You could run

SHOW GRANTS FOR `user1@'127.0.0.1'`;
SHOW GRANTS FOR `user2@'%'`;
SHOW GRANTS FOR `user3@'10.20.%'`;

Record the output in a text file. Next run this:

DROP USER `user1@'127.0.0.1'`;
DROP USER `user2@'%'`;
DROP USER `user3@'10.20.%'`;

OK, users are gone. Now, put them back with localhost using the text file:

GRANT ... ON *.* TO `user1@localhost`;
GRANT ... ON *.* TO `user2@localhost`;
GRANT ... ON *.* TO `user3@localhost`;

QUICK AND DIRTY METHOD

# Global Privileges
CREATE TABLE mysql.usernew LIKE mysql.user;
INSERT INTO mysql.usernew SELECT * FROM mysql.user
WHERE user IN ('user1','user2','user3') AND host<>'localhost';
UPDATE mysql.usernew SET host='localhost';
# DB Privileges
CREATE TABLE mysql.dbnew LIKE mysql.db;
INSERT INTO mysql.dbnew SELECT * FROM mysql.db
WHERE user IN ('user1','user2','user3') AND host<>'localhost';
UPDATE mysql.dbnew SET host='localhost';
# Table Privileges
CREATE TABLE mysql.tables_privnew LIKE mysql.tables_priv;
INSERT INTO mysql.tables_privnew SELECT * FROM mysql.tables_priv
WHERE user IN ('user1','user2','user3') AND host<>'localhost';
UPDATE mysql.tables_privnew SET host='localhost';
# Column Privileges
CREATE TABLE mysql.columns_privnew LIKE mysql.columns_priv;
INSERT INTO mysql.columns_privnew SELECT * FROM mysql.columns_priv
WHERE user IN ('user1','user2','user3') AND host<>'localhost';
UPDATE mysql.columns_privnew SET host='localhost';
#
# Remove Users with non-localhost access
# Insert Same Users with localhost access
#
DELETE FROM mysql.user         WHERE user IN ('user1','user2','user3');
DELETE FROM mysql.db           WHERE user IN ('user1','user2','user3');
DELETE FROM mysql.tables_priv  WHERE user IN ('user1','user2','user3');
DELETE FROM mysql.columns_priv WHERE user IN ('user1','user2','user3');
INSERT INTO mysql.user         SELECT * FROM mysql.usernew;
INSERT INTO mysql.db           SELECT * FROM mysql.dbnew;
INSERT INTO mysql.tables_priv  SELECT * FROM mysql.tables_privnew;
INSERT INTO mysql.columns_priv SELECT * FROM mysql.columns_privnew;
DROP TABLE mysql.usernew;
DROP TABLE mysql.dbnew;
DROP TABLE mysql.tables_privnew;
DROP TABLE mysql.columns_privnew;
FLUSH PRIVILEGES;

WARNING : PLEASE TEST THIS ON A STAGING SERVER !!!

Give it a Try !!!

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.