Description:
Changing Host Limit value from '%' to restricted value like '10.10.%.%' results into `ERROR 1045 (28000): Access denied for user 'user'@'10.10.%.%' (using password: YES)` when selecting from views.
Also noticed below warning message while changing host value
---------------
14:52:48 RENAME USER 'inc30587'@'%' TO 'inc30587'@'10.10.%.%' 0 row(s) affected,
1 warning(s): 4005 User 'inc30587'@'%' is referenced as a definer account in a view. 0.048 sec
----------------
How to repeat:
Here are the detailed steps
1. Login as admin or other user who has permissions to create database and users
2. create two databases
CREATE DATABASE IF NOT EXISTS test_inc30587;
CREATE DATABASE IF NOT EXISTS test_inc30587_dw;
3. create user and grant permissions to DBs that are created in step 2.
CREATE USER 'inc30587'@'%' IDENTIFIED BY 'inc30587';
GRANT ALTER, CREATE, CREATE TEMPORARY TABLES, CREATE VIEW, DELETE, DROP, EXECUTE, INDEX, INSERT, LOCK TABLES, SELECT, SHOW VIEW, UPDATE on test_inc30587.* TO 'inc30587'@'%';
GRANT ALTER, CREATE, CREATE TEMPORARY TABLES, CREATE VIEW, DELETE, DROP, EXECUTE, INDEX, INSERT, LOCK TABLES, SELECT, SHOW VIEW, UPDATE on test_inc30587_dw.* TO 'inc30587'@'%';
FLUSH PRIVILEGES;
4. Login as the user created in step 3, in this case user inc30587. This is very important as the DEFINER of the view depends on logged-in user (CurrentUser)
5. create tables, views and insert some test data (make sure you are logged in as test user)
CREATE TABLE test_inc30587.Customer (
CustomerId int NOT NULL AUTO_INCREMENT,
CustomerName varchar(255),
ContactName varchar(255),
Address varchar(255),
City varchar(255),
PostalCode varchar(255),
Country varchar(255),
PRIMARY KEY (CustomerId)
);
CREATE OR REPLACE VIEW test_inc30587_dw.CustomerNameCountry as SELECT * FROM test_inc30587.Customer;
INSERT INTO test_inc30587.Customer (CustomerName, ContactName, Address, City, PostalCode, Country)
VALUES
('Cardinal', 'Tom B. Erichsen', 'Skagen 21', 'Stavanger', '4006', 'Norway'),
('Greasy Burger', 'Per Olsen', 'Gateveien 15', 'Sandnes', '4306', 'Norway'),
('Tasty Tee', 'Finn Egan', 'Streetroad 19B', 'Liverpool', 'L1 0AA', 'UK');
SELECT * from test_inc30587.Customer;
SELECT * from test_inc30587_dw.CustomerNameCountry;
6. Logout test user; in this case inc30587
7. login as admin or any other user who have permission to manage users
8. Add host restriction to the test-user
RENAME USER 'inc30587'@'%' TO 'inc30587'@'10.10.%.%';
FLUSH PRIVILEGES;
Note that IP restriction is based on my VPC. Change it to you VPC. At this step you can notice a warning message from DB server
---------------
14:52:48 RENAME USER 'inc30587'@'%' TO 'inc30587'@'10.10.%.%' 0 row(s) affected,
1 warning(s): 4005 User 'inc30587'@'%' is referenced as a definer account in a view. 0.048 sec
----------------
9. Login as test user (inc30587) from the allowed network or VPC
10. try selecting from view. Select from DB will work fine
select * from test_inc30587_dw.CustomerNameCountry;
above sql will result in `ERROR 1045 (28000): Access denied for user 'inc30587'@'10.10.%.%' (using password: YES)` error
Description: Changing Host Limit value from '%' to restricted value like '10.10.%.%' results into `ERROR 1045 (28000): Access denied for user 'user'@'10.10.%.%' (using password: YES)` when selecting from views. Also noticed below warning message while changing host value --------------- 14:52:48 RENAME USER 'inc30587'@'%' TO 'inc30587'@'10.10.%.%' 0 row(s) affected, 1 warning(s): 4005 User 'inc30587'@'%' is referenced as a definer account in a view. 0.048 sec ---------------- How to repeat: Here are the detailed steps 1. Login as admin or other user who has permissions to create database and users 2. create two databases CREATE DATABASE IF NOT EXISTS test_inc30587; CREATE DATABASE IF NOT EXISTS test_inc30587_dw; 3. create user and grant permissions to DBs that are created in step 2. CREATE USER 'inc30587'@'%' IDENTIFIED BY 'inc30587'; GRANT ALTER, CREATE, CREATE TEMPORARY TABLES, CREATE VIEW, DELETE, DROP, EXECUTE, INDEX, INSERT, LOCK TABLES, SELECT, SHOW VIEW, UPDATE on test_inc30587.* TO 'inc30587'@'%'; GRANT ALTER, CREATE, CREATE TEMPORARY TABLES, CREATE VIEW, DELETE, DROP, EXECUTE, INDEX, INSERT, LOCK TABLES, SELECT, SHOW VIEW, UPDATE on test_inc30587_dw.* TO 'inc30587'@'%'; FLUSH PRIVILEGES; 4. Login as the user created in step 3, in this case user inc30587. This is very important as the DEFINER of the view depends on logged-in user (CurrentUser) 5. create tables, views and insert some test data (make sure you are logged in as test user) CREATE TABLE test_inc30587.Customer ( CustomerId int NOT NULL AUTO_INCREMENT, CustomerName varchar(255), ContactName varchar(255), Address varchar(255), City varchar(255), PostalCode varchar(255), Country varchar(255), PRIMARY KEY (CustomerId) ); CREATE OR REPLACE VIEW test_inc30587_dw.CustomerNameCountry as SELECT * FROM test_inc30587.Customer; INSERT INTO test_inc30587.Customer (CustomerName, ContactName, Address, City, PostalCode, Country) VALUES ('Cardinal', 'Tom B. Erichsen', 'Skagen 21', 'Stavanger', '4006', 'Norway'), ('Greasy Burger', 'Per Olsen', 'Gateveien 15', 'Sandnes', '4306', 'Norway'), ('Tasty Tee', 'Finn Egan', 'Streetroad 19B', 'Liverpool', 'L1 0AA', 'UK'); SELECT * from test_inc30587.Customer; SELECT * from test_inc30587_dw.CustomerNameCountry; 6. Logout test user; in this case inc30587 7. login as admin or any other user who have permission to manage users 8. Add host restriction to the test-user RENAME USER 'inc30587'@'%' TO 'inc30587'@'10.10.%.%'; FLUSH PRIVILEGES; Note that IP restriction is based on my VPC. Change it to you VPC. At this step you can notice a warning message from DB server --------------- 14:52:48 RENAME USER 'inc30587'@'%' TO 'inc30587'@'10.10.%.%' 0 row(s) affected, 1 warning(s): 4005 User 'inc30587'@'%' is referenced as a definer account in a view. 0.048 sec ---------------- 9. Login as test user (inc30587) from the allowed network or VPC 10. try selecting from view. Select from DB will work fine select * from test_inc30587_dw.CustomerNameCountry; above sql will result in `ERROR 1045 (28000): Access denied for user 'inc30587'@'10.10.%.%' (using password: YES)` error