Root user from another VM (local network) on Mysql 5.7 while using plugin auth_socket
I have one virtual machine for my website, and one for my databases (mysql 5.7). The VMs are on debian 9
I would like to connect to mysql from my website VM.
I install everything using ansible so it does not create a root password and that means it uses the plugin auth_socket by default. That also means I can't connect from my website VM with the root user of that VM. I can only connect to the root account if I'm on my databases VM and on the root system user (unless you know how to change this).
So I don't know exactly what to do, but I have found two solutions:
- attribute a password to root and change the plugin to use passwords like here https://www.percona.com/blog/2016/03/16/change-user-password-in-mysql-5-7-with-plugin-auth_socket/
- create a new user called "root2" and give it full privileges on my local network only
What other solution do I have? which one seems optimal?
mysql ansible mariadb
asked Feb 18 at 16:42
pedrotesterpedrotester
11
add a comment |
I have one virtual machine for my website, and one for my databases (mysql 5.7). The VMs are on debian 9
I would like to connect to mysql from my website VM.
I install everything using ansible so it does not create a root password and that means it uses the plugin auth_socket by default. That also means I can't connect from my website VM with the root user of that VM. I can only connect to the root account if I'm on my databases VM and on the root system user (unless you know how to change this).
So I don't know exactly what to do, but I have found two solutions:
- attribute a password to root and change the plugin to use passwords like here https://www.percona.com/blog/2016/03/16/change-user-password-in-mysql-5-7-with-plugin-auth_socket/
- create a new user called "root2" and give it full privileges on my local network only
What other solution do I have? which one seems optimal?
mysql ansible mariadb
asked Feb 18 at 16:42
pedrotesterpedrotester
11
add a comment |
I have one virtual machine for my website, and one for my databases (mysql 5.7). The VMs are on debian 9
I would like to connect to mysql from my website VM.
I install everything using ansible so it does not create a root password and that means it uses the plugin auth_socket by default. That also means I can't connect from my website VM with the root user of that VM. I can only connect to the root account if I'm on my databases VM and on the root system user (unless you know how to change this).
So I don't know exactly what to do, but I have found two solutions:
- attribute a password to root and change the plugin to use passwords like here https://www.percona.com/blog/2016/03/16/change-user-password-in-mysql-5-7-with-plugin-auth_socket/
- create a new user called "root2" and give it full privileges on my local network only
What other solution do I have? which one seems optimal?
mysql ansible mariadb
asked Feb 18 at 16:42
pedrotesterpedrotester
11
I have one virtual machine for my website, and one for my databases (mysql 5.7). The VMs are on debian 9
I would like to connect to mysql from my website VM.
I install everything using ansible so it does not create a root password and that means it uses the plugin auth_socket by default. That also means I can't connect from my website VM with the root user of that VM. I can only connect to the root account if I'm on my databases VM and on the root system user (unless you know how to change this).
So I don't know exactly what to do, but I have found two solutions:
- attribute a password to root and change the plugin to use passwords like here https://www.percona.com/blog/2016/03/16/change-user-password-in-mysql-5-7-with-plugin-auth_socket/
- create a new user called "root2" and give it full privileges on my local network only
What other solution do I have? which one seems optimal?
mysql ansible mariadb
mysql ansible mariadb
asked Feb 18 at 16:42
pedrotesterpedrotester
11
asked Feb 18 at 16:42
pedrotesterpedrotester
11
edited Feb 18 at 22:36
pedrotester
asked Feb 18 at 16:42
pedrotesterpedrotester
11
asked Feb 18 at 16:42
pedrotesterpedrotester
11
asked Feb 18 at 16:42
pedrotesterpedrotester
11
11
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
You can't use an auth socket from another machine, you need to create a mysql user with password and connect over tcp/ip.
Don't use root, that's such a bad idea. I would suggest an Option 3:
Create a new user(normally each app would have its own user and database) and give it as few rights, to as few objects as possible (the principle of least privilege). Using root would give your webserver rights to create or drop databases, users, set runtime config etc. All of which is unnessesary and dangerous.
Ansible provides mysql_user which can create database users: https://docs.ansible.com/ansible/latest/modules/mysql_user_module.html
Then update your connection string on the webserver and make sure tcp/ip port 3306 is accessible on the database server. Ideally only your dev ip and the webserver should be allowed (again with the least privilege approach. See http://en.wikipedia.org/wiki/Principle_of_least_privilege).
answered Feb 18 at 19:16
MisterSmithMisterSmith
2945
Thank you for confirmation about auth_socket. Now my issue is a bit more complex, it's because I need to run my ansible role directly on my webserver VM and not on my databases VM, and then create mysql users. But for this I would need either root access or a user that can create users and grant privileges. So I guess your advice is to create this user during mysql installation, and then use this user to create the other users from the webserver VM. Do you confirm?
– pedrotester
Feb 18 at 19:43
In fact, I can't create any user because Ansible does not connect as root for this task (even when I tried to force it to "become" root, I don't know why). So I have no idea how I can change any of this. Impossible to connect to mysql root user to do anything with ansible
– pedrotester
Feb 18 at 22:36
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "3"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1407092%2froot-user-from-another-vm-local-network-on-mysql-5-7-while-using-plugin-auth-s%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
You can't use an auth socket from another machine, you need to create a mysql user with password and connect over tcp/ip.
Don't use root, that's such a bad idea. I would suggest an Option 3:
Create a new user(normally each app would have its own user and database) and give it as few rights, to as few objects as possible (the principle of least privilege). Using root would give your webserver rights to create or drop databases, users, set runtime config etc. All of which is unnessesary and dangerous.
Ansible provides mysql_user which can create database users: https://docs.ansible.com/ansible/latest/modules/mysql_user_module.html
Then update your connection string on the webserver and make sure tcp/ip port 3306 is accessible on the database server. Ideally only your dev ip and the webserver should be allowed (again with the least privilege approach. See http://en.wikipedia.org/wiki/Principle_of_least_privilege).
answered Feb 18 at 19:16
MisterSmithMisterSmith
2945
Thank you for confirmation about auth_socket. Now my issue is a bit more complex, it's because I need to run my ansible role directly on my webserver VM and not on my databases VM, and then create mysql users. But for this I would need either root access or a user that can create users and grant privileges. So I guess your advice is to create this user during mysql installation, and then use this user to create the other users from the webserver VM. Do you confirm?
– pedrotester
Feb 18 at 19:43
In fact, I can't create any user because Ansible does not connect as root for this task (even when I tried to force it to "become" root, I don't know why). So I have no idea how I can change any of this. Impossible to connect to mysql root user to do anything with ansible
– pedrotester
Feb 18 at 22:36
add a comment |
You can't use an auth socket from another machine, you need to create a mysql user with password and connect over tcp/ip.
Don't use root, that's such a bad idea. I would suggest an Option 3:
Create a new user(normally each app would have its own user and database) and give it as few rights, to as few objects as possible (the principle of least privilege). Using root would give your webserver rights to create or drop databases, users, set runtime config etc. All of which is unnessesary and dangerous.
Ansible provides mysql_user which can create database users: https://docs.ansible.com/ansible/latest/modules/mysql_user_module.html
Then update your connection string on the webserver and make sure tcp/ip port 3306 is accessible on the database server. Ideally only your dev ip and the webserver should be allowed (again with the least privilege approach. See http://en.wikipedia.org/wiki/Principle_of_least_privilege).
answered Feb 18 at 19:16
MisterSmithMisterSmith
2945
Thank you for confirmation about auth_socket. Now my issue is a bit more complex, it's because I need to run my ansible role directly on my webserver VM and not on my databases VM, and then create mysql users. But for this I would need either root access or a user that can create users and grant privileges. So I guess your advice is to create this user during mysql installation, and then use this user to create the other users from the webserver VM. Do you confirm?
– pedrotester
Feb 18 at 19:43
In fact, I can't create any user because Ansible does not connect as root for this task (even when I tried to force it to "become" root, I don't know why). So I have no idea how I can change any of this. Impossible to connect to mysql root user to do anything with ansible
– pedrotester
Feb 18 at 22:36
add a comment |
You can't use an auth socket from another machine, you need to create a mysql user with password and connect over tcp/ip.
Don't use root, that's such a bad idea. I would suggest an Option 3:
Create a new user(normally each app would have its own user and database) and give it as few rights, to as few objects as possible (the principle of least privilege). Using root would give your webserver rights to create or drop databases, users, set runtime config etc. All of which is unnessesary and dangerous.
Ansible provides mysql_user which can create database users: https://docs.ansible.com/ansible/latest/modules/mysql_user_module.html
Then update your connection string on the webserver and make sure tcp/ip port 3306 is accessible on the database server. Ideally only your dev ip and the webserver should be allowed (again with the least privilege approach. See http://en.wikipedia.org/wiki/Principle_of_least_privilege).
answered Feb 18 at 19:16
MisterSmithMisterSmith
2945
You can't use an auth socket from another machine, you need to create a mysql user with password and connect over tcp/ip.
Don't use root, that's such a bad idea. I would suggest an Option 3:
Create a new user(normally each app would have its own user and database) and give it as few rights, to as few objects as possible (the principle of least privilege). Using root would give your webserver rights to create or drop databases, users, set runtime config etc. All of which is unnessesary and dangerous.
Ansible provides mysql_user which can create database users: https://docs.ansible.com/ansible/latest/modules/mysql_user_module.html
Then update your connection string on the webserver and make sure tcp/ip port 3306 is accessible on the database server. Ideally only your dev ip and the webserver should be allowed (again with the least privilege approach. See http://en.wikipedia.org/wiki/Principle_of_least_privilege).
answered Feb 18 at 19:16
MisterSmithMisterSmith
2945
answered Feb 18 at 19:16
MisterSmithMisterSmith
2945
answered Feb 18 at 19:16
MisterSmithMisterSmith
2945
answered Feb 18 at 19:16
MisterSmithMisterSmith
2945
2945
Thank you for confirmation about auth_socket. Now my issue is a bit more complex, it's because I need to run my ansible role directly on my webserver VM and not on my databases VM, and then create mysql users. But for this I would need either root access or a user that can create users and grant privileges. So I guess your advice is to create this user during mysql installation, and then use this user to create the other users from the webserver VM. Do you confirm?
– pedrotester
Feb 18 at 19:43
In fact, I can't create any user because Ansible does not connect as root for this task (even when I tried to force it to "become" root, I don't know why). So I have no idea how I can change any of this. Impossible to connect to mysql root user to do anything with ansible
– pedrotester
Feb 18 at 22:36
add a comment |
Thank you for confirmation about auth_socket. Now my issue is a bit more complex, it's because I need to run my ansible role directly on my webserver VM and not on my databases VM, and then create mysql users. But for this I would need either root access or a user that can create users and grant privileges. So I guess your advice is to create this user during mysql installation, and then use this user to create the other users from the webserver VM. Do you confirm?
– pedrotester
Feb 18 at 19:43
In fact, I can't create any user because Ansible does not connect as root for this task (even when I tried to force it to "become" root, I don't know why). So I have no idea how I can change any of this. Impossible to connect to mysql root user to do anything with ansible
– pedrotester
Feb 18 at 22:36
Thank you for confirmation about auth_socket. Now my issue is a bit more complex, it's because I need to run my ansible role directly on my webserver VM and not on my databases VM, and then create mysql users. But for this I would need either root access or a user that can create users and grant privileges. So I guess your advice is to create this user during mysql installation, and then use this user to create the other users from the webserver VM. Do you confirm?
– pedrotester
Feb 18 at 19:43
Thank you for confirmation about auth_socket. Now my issue is a bit more complex, it's because I need to run my ansible role directly on my webserver VM and not on my databases VM, and then create mysql users. But for this I would need either root access or a user that can create users and grant privileges. So I guess your advice is to create this user during mysql installation, and then use this user to create the other users from the webserver VM. Do you confirm?
– pedrotester
Feb 18 at 19:43
In fact, I can't create any user because Ansible does not connect as root for this task (even when I tried to force it to "become" root, I don't know why). So I have no idea how I can change any of this. Impossible to connect to mysql root user to do anything with ansible
– pedrotester
Feb 18 at 22:36
In fact, I can't create any user because Ansible does not connect as root for this task (even when I tried to force it to "become" root, I don't know why). So I have no idea how I can change any of this. Impossible to connect to mysql root user to do anything with ansible
– pedrotester
Feb 18 at 22:36
add a comment |
Thanks for contributing an answer to Super User!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1407092%2froot-user-from-another-vm-local-network-on-mysql-5-7-while-using-plugin-auth-s%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
