mirror of
https://github.com/MariaDB/server.git
synced 2025-10-31 19:06:14 +01:00
143 lines
8.8 KiB
Text
143 lines
8.8 KiB
Text
CREATE DATABASE test_user_db;
|
|
========== test 1.1.3.2 ====================================
|
|
=== check contens of components of info ====================
|
|
CREATE USER qa_test_1_user IDENTIFIED WITH qa_auth_interface AS 'qa_test_1_dest';
|
|
CREATE USER qa_test_1_dest IDENTIFIED BY 'dest_passwd';
|
|
GRANT ALL PRIVILEGES ON test_user_db.* TO qa_test_1_dest identified by 'dest_passwd';
|
|
GRANT PROXY ON qa_test_1_dest TO qa_test_1_user;
|
|
GRANT ALL ON test_user_db.* TO 'qa_test_1_user'@'%';
|
|
SELECT user,plugin,authentication_string FROM mysql.user WHERE user != 'root';
|
|
User plugin authentication_string
|
|
mariadb.sys mysql_native_password
|
|
qa_test_1_dest mysql_native_password *DFCACE76914AD7BD801FC1A1ECF6562272621A22
|
|
qa_test_1_user qa_auth_interface qa_test_1_dest
|
|
SELECT @@proxy_user;
|
|
@@proxy_user
|
|
NULL
|
|
SELECT @@external_user;
|
|
@@external_user
|
|
NULL
|
|
exec MYSQL -h localhost -u qa_test_1_user --password=qa_test_1_dest test_user_db -e "SELECT current_user(),user(),@@local.proxy_user,@@local.external_user;" 2>&1
|
|
current_user() user() @@local.proxy_user @@local.external_user
|
|
qa_test_1_user@% qa_test_1_user@localhost NULL NULL
|
|
SELECT user,plugin,authentication_string FROM mysql.user WHERE user != 'root';
|
|
User plugin authentication_string
|
|
mariadb.sys mysql_native_password
|
|
qa_test_1_dest mysql_native_password *DFCACE76914AD7BD801FC1A1ECF6562272621A22
|
|
qa_test_1_user qa_auth_interface qa_test_1_dest
|
|
DROP USER qa_test_1_user;
|
|
DROP USER qa_test_1_dest;
|
|
=== Assign values to components of info ====================
|
|
CREATE USER qa_test_2_user IDENTIFIED WITH qa_auth_interface AS 'qa_test_2_dest';
|
|
CREATE USER qa_test_2_dest IDENTIFIED BY 'dest_passwd';
|
|
CREATE USER authenticated_as IDENTIFIED BY 'dest_passwd';
|
|
GRANT ALL PRIVILEGES ON test_user_db.* TO qa_test_2_dest identified by 'dest_passwd';
|
|
GRANT PROXY ON qa_test_2_dest TO qa_test_2_user;
|
|
GRANT PROXY ON authenticated_as TO qa_test_2_user;
|
|
GRANT ALL ON test_user_db.* TO 'authenticated_as'@'%';
|
|
SELECT user,plugin,authentication_string FROM mysql.user WHERE user != 'root';
|
|
User plugin authentication_string
|
|
authenticated_as mysql_native_password *DFCACE76914AD7BD801FC1A1ECF6562272621A22
|
|
mariadb.sys mysql_native_password
|
|
qa_test_2_dest mysql_native_password *DFCACE76914AD7BD801FC1A1ECF6562272621A22
|
|
qa_test_2_user qa_auth_interface qa_test_2_dest
|
|
SELECT @@proxy_user;
|
|
@@proxy_user
|
|
NULL
|
|
SELECT @@external_user;
|
|
@@external_user
|
|
NULL
|
|
exec MYSQL -h localhost -u qa_test_2_user --password=qa_test_2_dest test_user_db -e "SELECT current_user(),user(),@@local.proxy_user,@@local.external_user;" 2>&1
|
|
current_user() user() @@local.proxy_user @@local.external_user
|
|
authenticated_as@% user_name@localhost 'qa_test_2_user'@'%' externaluser
|
|
SELECT user,plugin,authentication_string FROM mysql.user WHERE user != 'root';
|
|
User plugin authentication_string
|
|
authenticated_as mysql_native_password *DFCACE76914AD7BD801FC1A1ECF6562272621A22
|
|
mariadb.sys mysql_native_password
|
|
qa_test_2_dest mysql_native_password *DFCACE76914AD7BD801FC1A1ECF6562272621A22
|
|
qa_test_2_user qa_auth_interface qa_test_2_dest
|
|
DROP USER qa_test_2_user;
|
|
DROP USER qa_test_2_dest;
|
|
DROP USER authenticated_as;
|
|
=== Assign too high values for *length, which should have no effect ====
|
|
CREATE USER qa_test_3_user IDENTIFIED WITH qa_auth_interface AS 'qa_test_3_dest';
|
|
CREATE USER qa_test_3_dest IDENTIFIED BY 'dest_passwd';
|
|
GRANT ALL PRIVILEGES ON test_user_db.* TO qa_test_3_dest identified by 'dest_passwd';
|
|
GRANT PROXY ON qa_test_3_dest TO qa_test_3_user;
|
|
exec MYSQL -h localhost -u qa_test_3_user --password=qa_test_3_dest test_user_db -e "SELECT current_user(),user(),@@local.proxy_user,@@local.external_user;" 2>&1
|
|
current_user() user() @@local.proxy_user @@local.external_user
|
|
qa_test_3_dest@% qa_test_3_user@localhost 'qa_test_3_user'@'%' qa_test_3_dest
|
|
DROP USER qa_test_3_user;
|
|
DROP USER qa_test_3_dest;
|
|
=== Assign too low values for *length, which should have no effect ====
|
|
CREATE USER qa_test_4_user IDENTIFIED WITH qa_auth_interface AS 'qa_test_4_dest';
|
|
CREATE USER qa_test_4_dest IDENTIFIED BY 'dest_passwd';
|
|
GRANT ALL PRIVILEGES ON test_user_db.* TO qa_test_4_dest identified by 'dest_passwd';
|
|
GRANT PROXY ON qa_test_4_dest TO qa_test_4_user;
|
|
exec MYSQL -h localhost -u qa_test_4_user --password=qa_test_4_dest test_user_db -e "SELECT current_user(),user(),@@local.proxy_user,@@local.external_user;" 2>&1
|
|
current_user() user() @@local.proxy_user @@local.external_user
|
|
qa_test_4_dest@% qa_test_4_user@localhost 'qa_test_4_user'@'%' qa_test_4_dest
|
|
DROP USER qa_test_4_user;
|
|
DROP USER qa_test_4_dest;
|
|
=== Assign empty string especially to authenticated_as (in plugin) ====
|
|
CREATE USER qa_test_5_user IDENTIFIED WITH qa_auth_interface AS 'qa_test_5_dest';
|
|
CREATE USER qa_test_5_dest IDENTIFIED BY 'dest_passwd';
|
|
CREATE USER ''@'localhost' IDENTIFIED BY 'dest_passwd';
|
|
GRANT ALL PRIVILEGES ON test_user_db.* TO qa_test_5_dest identified by 'dest_passwd';
|
|
GRANT ALL PRIVILEGES ON test_user_db.* TO ''@'localhost' identified by 'dest_passwd';
|
|
GRANT PROXY ON qa_test_5_dest TO qa_test_5_user;
|
|
GRANT PROXY ON qa_test_5_dest TO ''@'localhost';
|
|
SELECT user,plugin,authentication_string,password FROM mysql.user WHERE user != 'root';
|
|
User plugin authentication_string Password
|
|
mysql_native_password *DFCACE76914AD7BD801FC1A1ECF6562272621A22 *DFCACE76914AD7BD801FC1A1ECF6562272621A22
|
|
mariadb.sys mysql_native_password
|
|
qa_test_5_dest mysql_native_password *DFCACE76914AD7BD801FC1A1ECF6562272621A22 *DFCACE76914AD7BD801FC1A1ECF6562272621A22
|
|
qa_test_5_user qa_auth_interface qa_test_5_dest
|
|
exec MYSQL -h localhost --user=qa_test_5_user --password=qa_test_5_dest test_user_db -e "SELECT current_user(),user(),@@local.proxy_user,@@local.external_user;" 2>&1
|
|
ERROR 1045 (28000): Access denied for user 'qa_test_5_user'@'localhost' (using password: YES)
|
|
DROP USER qa_test_5_user;
|
|
DROP USER qa_test_5_dest;
|
|
DROP USER ''@'localhost';
|
|
=== Assign 'root' especially to authenticated_as (in plugin) ====
|
|
CREATE USER qa_test_6_user IDENTIFIED WITH qa_auth_interface AS 'qa_test_6_dest';
|
|
CREATE USER qa_test_6_dest IDENTIFIED BY 'dest_passwd';
|
|
GRANT ALL PRIVILEGES ON test_user_db.* TO qa_test_6_dest identified by 'dest_passwd';
|
|
GRANT PROXY ON qa_test_6_dest TO qa_test_6_user;
|
|
SELECT user,plugin,authentication_string,password FROM mysql.user WHERE user != 'root';
|
|
User plugin authentication_string Password
|
|
mariadb.sys mysql_native_password
|
|
qa_test_6_dest mysql_native_password *DFCACE76914AD7BD801FC1A1ECF6562272621A22 *DFCACE76914AD7BD801FC1A1ECF6562272621A22
|
|
qa_test_6_user qa_auth_interface qa_test_6_dest
|
|
exec MYSQL -h localhost --user=qa_test_6_user --password=qa_test_6_dest test_user_db -e "SELECT current_user(),user(),@@local.proxy_user,@@local.external_user;" 2>&1
|
|
ERROR 1045 (28000): Access denied for user 'qa_test_6_user'@'localhost' (using password: YES)
|
|
GRANT PROXY ON qa_test_6_dest TO root IDENTIFIED WITH qa_auth_interface AS 'qa_test_6_dest';
|
|
SELECT user,plugin,authentication_string,password FROM mysql.user WHERE user != 'root';
|
|
User plugin authentication_string Password
|
|
mariadb.sys mysql_native_password
|
|
qa_test_6_dest mysql_native_password *DFCACE76914AD7BD801FC1A1ECF6562272621A22 *DFCACE76914AD7BD801FC1A1ECF6562272621A22
|
|
qa_test_6_user qa_auth_interface qa_test_6_dest
|
|
exec MYSQL -h localhost --user=root --password=qa_test_6_dest test_user_db -e "SELECT current_user(),user(),@@local.proxy_user,@@local.external_user;" 2>&1
|
|
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
|
|
REVOKE PROXY ON qa_test_6_dest FROM root;
|
|
SELECT user,plugin,authentication_string FROM mysql.user WHERE user != 'root';
|
|
User plugin authentication_string
|
|
mariadb.sys mysql_native_password
|
|
qa_test_6_dest mysql_native_password *DFCACE76914AD7BD801FC1A1ECF6562272621A22
|
|
qa_test_6_user qa_auth_interface qa_test_6_dest
|
|
exec MYSQL -h localhost --user=root --password=qa_test_6_dest test_user_db -e "SELECT current_user(),user(),@@local.proxy_user,@@local.external_user;" 2>&1
|
|
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
|
|
DROP USER qa_test_6_user;
|
|
DROP USER qa_test_6_dest;
|
|
DELETE FROM mysql.user WHERE user='root' AND plugin='qa_auth_interface';
|
|
SELECT user,plugin,authentication_string,password FROM mysql.user WHERE user != 'root';
|
|
User plugin authentication_string Password
|
|
mariadb.sys mysql_native_password
|
|
=== Test of the --default_auth option for clients ====
|
|
CREATE USER qa_test_11_user IDENTIFIED WITH qa_auth_interface AS 'qa_test_11_dest';
|
|
CREATE USER qa_test_11_dest IDENTIFIED BY 'dest_passwd';
|
|
GRANT ALL PRIVILEGES ON test_user_db.* TO qa_test_11_dest identified by 'dest_passwd';
|
|
GRANT PROXY ON qa_test_11_dest TO qa_test_11_user;
|
|
exec MYSQL --default_auth=qa_auth_client -h localhost -u qa_test_11_user --password=qa_test_11_dest test_user_db -e "SELECT current_user(),user(),@@local.proxy_user,@@local.external_user;" 2>&1
|
|
ERROR 1045 (28000): Access denied for user 'qa_test_11_user'@'localhost' (using password: YES)
|
|
DROP USER qa_test_11_user, qa_test_11_dest;
|
|
DROP DATABASE test_user_db;
|