#
# Test GRANT and REVOKE priviliges to users on DB objects
# Author: Bharath, bharath.bokka@mariadb.com
#
-- source ../include/have_columnstore.inc
-- source include/have_innodb.inc

SET default_storage_engine=InnoDB;

--disable_warnings
DROP DATABASE IF EXISTS mcs19_db;
DROP USER IF EXISTS 'user'@'localhost';
DROP USER IF EXISTS 'user1'@'localhost';
--enable_warnings

SET default_storage_engine=Columnstore;
SELECT @@default_storage_engine;

GRANT ALL on *.* TO 'user'@'localhost' IDENTIFIED BY 'Vagrant1|0000001';
SHOW GRANTS FOR 'user'@'localhost';

CREATE USER 'user1'@'localhost' IDENTIFIED BY 'Vagrant1|0000001';
SHOW GRANTS FOR 'user1'@'localhost';
GRANT ALL ON *.* TO 'user1'@'localhost';
SHOW GRANTS FOR 'user1'@'localhost';
REVOKE ALL ON *.* FROM 'user1'@'localhost';
SHOW GRANTS FOR 'user1'@'localhost';
GRANT CREATE, SELECT, INSERT ON mcs19_db.* TO 'user1'@'localhost';

--disable_query_log
GRANT ALL ON test.* TO 'user1'@'localhost';
--enable_query_log
connect(conn1, localhost, user1, Vagrant1|0000001,);
connect(conn2, localhost, user1, Vagrant1|0000001,);

connection conn1;
SELECT USER();
SHOW GRANTS FOR 'user1'@'localhost';
CREATE DATABASE mcs19_db;
USE mcs19_db;
--error ER_DBACCESS_DENIED_ERROR
CREATE DATABASE mcs19_db1;
CREATE TABLE t1(col INT) ENGINE=InnoDB;
--replace_regex /( COLLATE=latin1_swedish_ci)//
SHOW CREATE TABLE t1;
INSERT INTO t1 VALUES(1);
SELECT * FROM t1;
--error ER_DBACCESS_DENIED_ERROR
DROP DATABASE mcs19_db;

connection default;
SELECT USER();
REVOKE CREATE, SELECT, INSERT ON mcs19_db.* FROM 'user1'@'localhost';

connection conn2;
SELECT USER();
SHOW GRANTS FOR 'user1'@'localhost';
--error ER_DBACCESS_DENIED_ERROR
USE mcs19_db;
--disable_abort_on_error
--replace_regex /(`mcs19_db`.`t1`)/'t1'/
INSERT INTO mcs19_db.t1 VALUES(2);
--replace_regex /(`mcs19_db`.`t1`)/'t1'/
SELECT * FROM mcs19_db.t1;
--replace_regex /(`mcs19_db`.`t1`)/'t1'/
CREATE TABLE mcs19_db.t1;
--enable_abort_on_error

# Clean up
connection default;
SET default_storage_engine=default;
DROP DATABASE mcs19_db;
DROP USER 'user'@'localhost';
DROP USER 'user1'@'localhost';