# How to login with created user from community.mysql.mysql\_user **URL:** \ **Category:** Get Help\ **Tags:** awx\ **Created:** [September 19, 2024, 12:24pm UTC](https://forum.ansible.com/t/how-to-login-with-created-user-from-community-mysql-mysql-user/8626 "2024-09-19T12:24:04Z")\ **Posts on this page:** 7\ **Page:** 1
**Author:** ![gothsome](https://avatars.discourse-cdn.com/v4/letter/g/278dde/32.png) [@gothsome](https://forum.ansible.com/u/gothsome)\ **Post date:** [September 19, 2024, 12:24pm UTC](https://forum.ansible.com/t/how-to-login-with-created-user-from-community-mysql-mysql-user/8626/1 "2024-09-19T12:24:04Z")
I created a user: ```auto community.mysql.mysql_user: name: testbob password: testbobspassword host: "%" priv: '*.*:ALL,GRANT' state: present login_unix_socket: /var/run/mysqld/mysqld.sock ``` But when i try to login it failes: ```auto root@testsrv-02:~# sudo mysql -u bob -p Enter password: ERROR 1045 (28000): Access denied for user 'bob'@'localhost' (using password: YES) ``` In the sql log the user is there: ```auto 2024-09-19T12:01:17.955304Z 69 Connect root@localhost on using Socket 2024-09-19T12:01:17.958421Z 69 Query SELECT VERSION() 2024-09-19T12:01:17.971061Z 69 Query SELECT @@GLOBAL.sql_mode 2024-09-19T12:01:17.972091Z 69 Query SELECT count(*) FROM mysql.user WHERE user = 'testbob' AND host = '%' 2024-09-19T12:01:17.972842Z 69 Query SELECT VERSION() AS version 2024-09-19T12:01:17.973368Z 69 Query SELECT CONCAT('*', UCASE(SHA1(UNHEX(SHA1('testbobspassword'))))) 2024-09-19T12:01:17.974411Z 69 Query CREATE USER 'testbob'@'%' IDENTIFIED WITH mysql_native_password AS '*8883E66DB88B90BEE52B188C64946CA656AFA046' 2024-09-19T12:01:17.987176Z 69 Query GRANT ALL ON *.* TO 'testbob'@'%' WITH GRANT OPTION ``` Im askin because for an worklflow with db migration i have to set a new user with permissions for the migrated DB and in the src host change the password of the same user so nobody can login in the old DB accidently just for admin uses. But i noted i could not login with the credentials so i created an test playbook, but even without vars i cannot login. Testplay: ```auto --- - name: Create MySQL user and grant privileges on a specific database hosts: - "{{ sql_dest_srv }}" # - "{{ sql_src_srv }}" become: true vars: ansible_python_interpreter: /usr/bin/python3 tasks: - name: Ensure pip and venv are installed ansible.builtin.apt: name: - python3-pip - python3-venv - python3-dev - build-essential - libssl-dev - libffi-dev - libmysqlclient-dev - pkg-config - python3-pymysql state: present - name: Create a virtual environment ansible.builtin.command: cmd: python3 -m venv /opt/myenv args: creates: /opt/myenv - name: Install required Python packages in virtual environment ansible.builtin.pip: executable: /opt/myenv/bin/pip name: - pymysql - name: Generate a random password ansible.builtin.command: "openssl rand -base64 12" register: password_result no_log: true - name: Create MySQL user {{ sql_src_db }} with a random password and additional parameters community.mysql.mysql_user: name: testbob password: testbobspassword host: "%" priv: '*.*:ALL,GRANT' state: present login_unix_socket: /var/run/mysqld/mysqld.sock ``` Thanks again!!! ---
**Author:** ![gothsome](https://avatars.discourse-cdn.com/v4/letter/g/278dde/32.png) [@gothsome](https://forum.ansible.com/u/gothsome)\ **Post date:** [September 19, 2024, 1:20pm UTC](https://forum.ansible.com/t/how-to-login-with-created-user-from-community-mysql-mysql-user/8626/2 "2024-09-19T13:20:26Z")
I can login as my testuser, but not from my other playbook. doesnt get the password overwritten each time i execute this?: ```auto - name: Create MySQL user {{ sql_src_db }} with a random password and additional parameters community.mysql.mysql_user: name: "{{ sql_src_db }}" password: "{{ password_result.stdout }}" host: "%" priv: "{{ sql_src_db }}.*:ALL" state: present login_unix_socket: /var/run/mysqld/mysqld.sock ``` ---
**Author:** ![gothsome](https://avatars.discourse-cdn.com/v4/letter/g/278dde/32.png) [@gothsome](https://forum.ansible.com/u/gothsome)\ **Post date:** [September 19, 2024, 1:25pm UTC](https://forum.ansible.com/t/how-to-login-with-created-user-from-community-mysql-mysql-user/8626/3 "2024-09-19T13:25:18Z")
its confusing because i added an test of the userdata in my playboook to ceck if the new credentials are alright and the playbook doesnt fail there: ```auto --- - name: Create MySQL user and grant privileges on a specific database hosts: - "{{ sql_dest_srv }}" - "{{ sql_src_srv }}" become: true vars: ansible_python_interpreter: /usr/bin/python3 tasks: - name: Ensure pip and venv are installed ansible.builtin.apt: name: - python3-pip - python3-venv - python3-dev - build-essential - libssl-dev - libffi-dev - libmysqlclient-dev - pkg-config - python3-pymysql state: present - name: Create a virtual environment ansible.builtin.command: cmd: python3 -m venv /opt/myenv args: creates: /opt/myenv - name: Install required Python packages in virtual environment ansible.builtin.pip: executable: /opt/myenv/bin/pip name: - pymysql - name: Generate a random password ansible.builtin.command: "openssl rand -base64 12" register: password_result no_log: true - name: Output the generated password ansible.builtin.debug: msg: "Generated password for MySQL user {{ sql_src_db }}: {{ password_result.stdout }}" when: ansible_check_mode is not defined or not ansible_check_mode - name: Create MySQL user {{ sql_src_db }} with a random password and additional parameters community.mysql.mysql_user: name: "{{ sql_src_db }}" password: "{{ password_result.stdout }}" host: "%" priv: "{{ sql_src_db }}.*:ALL" state: present login_unix_socket: /var/run/mysqld/mysqld.sock - name: Append the generated password to the text file ansible.builtin.lineinfile: path: "/tmp/{{ sql_src_db }}_usrpw.txt" line: "{{ password_result.stdout }}" create: yes insertafter: EOF - name: Test SQL connection with the created user ansible.builtin.shell: > mysql -u {{ sql_src_db }} -p{{ password_result.stdout }} -e "SHOW DATABASES;" --socket=/var/run/mysqld/mysqld.sock register: sql_test_result ignore_errors: yes - name: Output the SQL connection test result ansible.builtin.debug: msg: > "SQL connection successful with user {{ sql_src_db }}." when: sql_test_result.rc == 0 - name: Output SQL connection test failure ansible.builtin.debug: msg: > "SQL connection failed with user {{ sql_src_db }}. Error: {{ sql_test_result.stderr }}" when: sql_test_result.rc != 0 ``` ---
**Author:** ![bvitnik](https://sea1.discourse-cdn.com/ansible/user_avatar/forum.ansible.com/bvitnik/32/4132_2.png) [@bvitnik](https://forum.ansible.com/u/bvitnik)\ **Post date:** [September 19, 2024, 5:57pm UTC](https://forum.ansible.com/t/how-to-login-with-created-user-from-community-mysql-mysql-user/8626/4 "2024-09-19T17:57:27Z")
What I can see here: ```auto root@testsrv-02:~# sudo mysql -u bob -p Enter password: ERROR 1045 (28000): Access denied for user 'bob'@'localhost' (using password: YES) ``` is that you have tried to test user `bob` instead of `testbob`. So I wonder if this is just a simple mixup. I see no apparent error in you playbook. ---
**Author:** ![gothsome](https://avatars.discourse-cdn.com/v4/letter/g/278dde/32.png) [@gothsome](https://forum.ansible.com/u/gothsome)\ **Post date:** [September 20, 2024, 5:47am UTC](https://forum.ansible.com/t/how-to-login-with-created-user-from-community-mysql-mysql-user/8626/5 "2024-09-20T05:47:36Z")
yeah thats wy i changed my statement that i can just not login with the user/pw created in the playbook above Somehow when using vars in the sql usercreation the test of the playbook can use the credential, but when i use the password outputted from the debug password output(wich matches the one in the .txt files) i cannot login with them in the sql server ---
**Author:** ![gothsome](https://avatars.discourse-cdn.com/v4/letter/g/278dde/32.png) [@gothsome](https://forum.ansible.com/u/gothsome)\ **Post date:** [September 20, 2024, 6:20am UTC](https://forum.ansible.com/t/how-to-login-with-created-user-from-community-mysql-mysql-user/8626/6 "2024-09-20T06:20:08Z")
it seems i did misspelled the user with bob and sakilla/sakila ---
**Author:** ![system](https://us1.discourse-cdn.com/ansible/original/1X/1882d9a01da5d8996a862359b0913cc568a39b84.svg) [@system](https://forum.ansible.com/u/system)\ **Post date:** [October 20, 2024, 6:21am UTC](https://forum.ansible.com/t/how-to-login-with-created-user-from-community-mysql-mysql-user/8626/7 "2024-10-20T06:21:01Z")
This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.