Hi, Vicențiu,
This is a review of _test cases only_.
Mainly, to discuss the behavior.
Random thoughts here and comments inline in the diff.
There are two models for DENY that I can think of: "a hole in grants"
and "a mask".
The difference lies in where DENY is applied. In the "a hole in grants"
model one has a certain set of grants (may be with "holes"), a role has
another set of grants, and when one does SET ROLE, one gets a union of
those grants. Like, one has SELECT on db1.* and DENY to db1.t1, and
some role has SELECT on db1.t1 - by setting this role a user will have
full SELECT on db1.*
While in the "mask" model, denies are separate entities, when setting a
role a union of denies is subtracted from the union of grants. In the
example above a user would still be denied access to db1.t1.
I personally see the "hole" model as more logical and easier to
understand. It also allows to "patch" holes with roles, what the "mask"
model cannot do.
The "mask" model needs getting used to, the fact that after SET ROLE one
can end up having less access than before. It's not clear who can DENY
what and to whom. And DENY is very difficult to work around, esp. as one
can DENY IGNORE DENIES.
But it allows to DENY something to PUBLIC, and the "hole" model cannot
really do that. And I think it'd be a popular use case.
Ideally, I'd prefer to have some kind of a compromise model that's as
simple to understand as a "hole", but allows to deny to public.
Unfortunately, I don't see how to do that.
You've implemented the "mask" behavior. Okay. It solves an important use
case.
More comments below:
> diff --git a/mysql-test/suite/deny/columns.result b/mysql-test/suite/deny/columns.result
> --- /dev/null
> +++ b/mysql-test/suite/deny/columns.result
> @@ -0,0 +1,126 @@
> +create user foo;
> +create database deny_db;
> +create table deny_db.t1 (a int, b int, secret int);
> +create table deny_db.t2 (a2 int, b2 int, secret2 int);
> +insert into deny_db.t2 values (100, 200, 300);
> +grant all on *.* to foo;
> +revoke ignore denies on *.* from foo;
> +grant all on deny_db.* to foo;
> +grant all on deny_db.t1 to foo;
> +grant all on deny_db.t2 to foo;
> +grant select (a, b, secret) on deny_db.t1 to foo;
> +grant insert (a, b, secret) on deny_db.t1 to foo;
> +grant update (a, b, secret) on deny_db.t1 to foo;
> +grant references (a, b, secret) on deny_db.t1 to foo;
> +grant select (a2, b2, secret2) on deny_db.t2 to foo;
> +grant insert (a2, b2, secret2) on deny_db.t2 to foo;
> +grant update (a2, b2, secret2) on deny_db.t2 to foo;
> +grant references (a2, b2, secret2) on deny_db.t2 to foo;
this is quite confusing, tons of redundant grants on different levels,
it makes it very unclear of what causes what.
> +#
> +# Test select and insert ... select commands.
> +#
> +deny select (secret) on deny_db.t1 to foo;
> +connect con1,localhost,foo,,;
> +select * from deny_db.t1;
> +ERROR 42000: SELECT command denied to user 'foo'@'localhost' for column 'secret' in table 't1'
case in point. Normally, this should've been
SELECT command denied to user 'foo'@'localhost' for table 't1'
like in
create database db;
create table db.t1 (a int, b int, secret int);
insert db.t1 values (1,2,100);
create user foo@localhost;
grant select (a,b) on db.t1 to foo@localhost;
connect foo,localhost,foo;
select a,b from db.t1;
--error ER_TABLEACCESS_DENIED_ERROR
select * from db.t1;
--error ER_COLUMNACCESS_DENIED_ERROR
select a,b,secret from db.t1;
that is `select *` is ER_TABLEACCESS_DENIED_ERROR, and if I mention
the column by name, then it's ER_COLUMNACCESS_DENIED_ERROR
But you have so many redundant overlapping grants, that I really
cannot see what error is correct in your test here.
> +select secret from deny_db.t1;
> +ERROR 42000: SELECT command denied to user 'foo'@'localhost' for column 'secret' in table 't1'
> +#
> +# These are different code paths. Check both.
> +#
> +insert into deny_db.t1 (a, b, secret) values (1, 2, 3);
> +insert into deny_db.t1 values (1, 2, 3);
> +#
> +# Check INSERT SELECT as we need to account for 2 different kinds of
> +# privileges.
> +#
> +insert into deny_db.t1 select * from deny_db.t1;
> +ERROR 42000: SELECT command denied to user 'foo'@'localhost' for column 'secret' in table 't1'
> +insert into deny_db.t1 select * from deny_db.t2;
> +show full columns from deny_db.t1;
> +Field Type Collation Null Key Default Extra Privileges Comment
> +a int(11) NULL YES NULL select,insert,update,references
> +b int(11) NULL YES NULL select,insert,update,references
> +secret int(11) NULL YES NULL insert,update,references
do you have a test where a denied column isn't shown by SHOW COLUMNS?
that is, where you don't have other privileges on it?
> +show full columns from deny_db.t2;
> +Field Type Collation Null Key Default Extra Privileges Comment
> +a2 int(11) NULL YES NULL select,insert,update,references
> +b2 int(11) NULL YES NULL select,insert,update,references
> +secret2 int(11) NULL YES NULL select,insert,update,references
> +disconnect con1;
> +connection default;
> +revoke deny select (secret) on deny_db.t1 from foo;
> +deny select(secret2) on deny_db.t2 to foo;
> +connect con1,localhost,foo,,;
> +insert into deny_db.t1 (a, b, secret) values (1, 2, 3);
> +insert into deny_db.t1 values (1, 2, 3);
> +insert into deny_db.t1 select * from deny_db.t1;
> +insert into deny_db.t1 select * from deny_db.t2;
> +ERROR 42000: SELECT command denied to user 'foo'@'localhost' for column 'secret2' in table 't2'
> +show full columns from deny_db.t1;
> +Field Type Collation Null Key Default Extra Privileges Comment
> +a int(11) NULL YES NULL select,insert,update,references
> +b int(11) NULL YES NULL select,insert,update,references
> +secret int(11) NULL YES NULL select,insert,update,references
> +show full columns from deny_db.t2;
> +Field Type Collation Null Key Default Extra Privileges Comment
> +a2 int(11) NULL YES NULL select,insert,update,references
> +b2 int(11) NULL YES NULL select,insert,update,references
> +secret2 int(11) NULL YES NULL insert,update,references
> +disconnect con1;
> +#
> +# Test insert commands
> +#
> +connection default;
> +deny insert (secret) on deny_db.t1 to foo;
> +connect con1,localhost,foo,,;
> +insert into deny_db.t1 (a, b, secret) values (1, 2, 3);
> +ERROR 42000: INSERT command denied to user 'foo'@'localhost' for column 'secret' in table 't1'
> +insert into deny_db.t1 values (1, 2, 3);
> +ERROR 42000: INSERT command denied to user 'foo'@'localhost' for column 'secret' in table 't1'
didn't test this one, but likely should behave as select?
that is, ER_TABLEACCESS_DENIED_ERROR
please, test with GRANT (without DENY) before making any changes
> +insert into deny_db.t1 select * from deny_db.t1;
> +ERROR 42000: INSERT command denied to user 'foo'@'localhost' for column 'secret' in table 't1'
> +insert into deny_db.t1 select * from deny_db.t2;
> +ERROR 42000: SELECT command denied to user 'foo'@'localhost' for column 'secret2' in table 't2'
> +show full columns from deny_db.t1;
> +Field Type Collation Null Key Default Extra Privileges Comment
> +a int(11) NULL YES NULL select,insert,update,references
> +b int(11) NULL YES NULL select,insert,update,references
> +secret int(11) NULL YES NULL select,update,references
> +disconnect con1;
> +#
> +# Test update commands.
> +#
> +connection default;
> +deny select (secret) on deny_db.t1 to foo;
> +deny select (secret2) on deny_db.t2 to foo;
> +connect con1, localhost,foo,,;
> +update deny_db.t1 set a=111, b=222 where secret=10;
> +ERROR 42000: SELECT command denied to user 'foo'@'localhost' for column 'secret' in table 't1'
> +update deny_db.t1 set a=123 where a = (select secret2 from deny_db.t2);
> +ERROR 42000: SELECT command denied to user 'foo'@'localhost' for column 'secret2' in table 't2'
> +show full columns from deny_db.t1;
> +Field Type Collation Null Key Default Extra Privileges Comment
> +a int(11) NULL YES NULL select,insert,update,references
> +b int(11) NULL YES NULL select,insert,update,references
> +secret int(11) NULL YES NULL update,references
> +show full columns from deny_db.t2;
> +Field Type Collation Null Key Default Extra Privileges Comment
> +a2 int(11) NULL YES NULL select,insert,update,references
> +b2 int(11) NULL YES NULL select,insert,update,references
> +secret2 int(11) NULL YES NULL insert,update,references
> +disconnect con1;
> +connection default;
> +deny update (secret) on deny_db.t1 to foo;
> +connect con1, localhost,foo,,;
> +update deny_db.t1 set a=111, b=222;
> +update deny_db.t1 set secret=333, a=111;
> +ERROR 42000: UPDATE command denied to user 'foo'@'localhost' for column 'secret' in table 't1'
> +update deny_db.t1 set secret=333;
> +ERROR 42000: UPDATE command denied to user 'foo'@'localhost' for column 'secret' in table 't1'
> +update deny_db.t1 set a=123, secret=333;
> +ERROR 42000: UPDATE command denied to user 'foo'@'localhost' for column 'secret' in table 't1'
> +disconnect con1;
> +connection default;
> +drop user foo;
> +drop database deny_db;
> diff --git a/mysql-test/suite/deny/columns_role.result b/mysql-test/suite/deny/columns_role.result
> --- /dev/null
> +++ b/mysql-test/suite/deny/columns_role.result
> @@ -0,0 +1,57 @@
> +create database some_db;
> +create view some_db.v1 as (select 1 as col);
> +create user foo;
> +create role bar;
> +grant select on *.* to foo;
> +grant show view on *.* to foo;
> +grant bar to foo;
> +grant select(col) on some_db.v1 to foo;
> +revoke ignore denies on *.* from foo;
why foo has ignore denies here? I don't see where it was granted.
> +deny select (col) on some_db.v1 to bar;
> +connect con1,localhost,foo,,;
> +describe some_db.v1;
> +Field Type Null Key Default Extra
> +col int(1) NO 0
> +set role bar;
> +describe some_db.v1;
> +ERROR 42000: SELECT command denied to user 'foo'@'localhost' for table 'v1'
this is very questionable. Should SET ROLE ever restrict what one can do?
> +show create view some_db.v1;
> +View Create View character_set_client collation_connection
> +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `some_db`.`v1` AS (select 1 AS `col`) latin1 latin1_swedish_ci
> +disconnect con1;
> +connection default;
> +drop user foo;
> +drop role bar;
> +drop database some_db;
> +#
> +# Test if only role level denies are active.
> +#
> +create database some_db;
> +create view some_db.v1 as (select 1 as col);
> +create user foo;
> +create role bar;
> +grant bar to foo;
> +grant select(col) on some_db.v1 to foo;
> +deny select (col) on some_db.v1 to bar;
> +connect con1,localhost,foo,,;
> +#
> +# Due to select(col) privilege on v1 we can see some_db here.
> +#
> +show databases;
> +Database
> +information_schema
> +some_db
> +test
> +set role bar;
> +#
> +# Now the role's denies take effect and we can no longer see some_db.
> +#
> +show databases;
> +Database
> +information_schema
> +test
> +disconnect con1;
> +connection default;
> +drop user foo;
> +drop role bar;
> +drop database some_db;
> diff --git a/mysql-test/suite/deny/deny_datastructures.result b/mysql-test/suite/deny/deny_datastructures.result
> --- /dev/null
> +++ b/mysql-test/suite/deny/deny_datastructures.result
> @@ -0,0 +1,109 @@
> +create user foo;
> +create role bar;
> +#
> +# Test deny format for global privileges.
> +#
> +deny select on *.* to foo;
> +deny insert on *.* to bar;
> +select user, host, JSON_EXTRACT(priv, '$.deny') from mysql.global_priv where user = 'foo';
> +user host JSON_EXTRACT(priv, '$.deny')
> +foo % {"global": 1, "version_id": VERSION_ID}
> +select user, host, JSON_EXTRACT(priv, '$.deny') from mysql.global_priv where user = 'bar';
> +user host JSON_EXTRACT(priv, '$.deny')
> +bar {"global": 2, "version_id": VERSION_ID}
> +flush privileges;
> +deny select on some_db.* to foo;
> +deny show view on some_db.* to foo;
> +deny insert on some_other_db.* to foo;
> +deny insert on some_other_db.* to bar;
> +deny select on some_other_db.some_table to foo;
> +deny insert on some_other_db.some_table_second to foo;
> +deny update on some_other_db.some_table_third to foo;
> +deny select(a, b) on some_other_db.some_table_third to foo;
> +deny select, insert(a, b, c) on some_other_db.some_table_fourth to foo;
> +select user, host, JSON_DETAILED(JSON_EXTRACT(priv, '$.deny')) from mysql.global_priv where user = 'foo';
> +user host JSON_DETAILED(JSON_EXTRACT(priv, '$.deny'))
> +foo % {
> + "global": 1,
> + "db":
> + [
> +
> + {
> + "name": "`some_db`",
> + "access": 4194305
> + },
> +
> + {
> + "name": "`some_other_db`",
> + "access": 2
> + }
> + ],
> + "table":
> + [
> +
> + {
> + "name": "`some_other_db`.`some_table_second`",
you sure you want that? you'd need to parse the name back when reading
privileges, why would you need this extra complexity?
I'd suggest something like
"name" : [ "some_other_db", "some_table_second" ]
(one component for db grants, three components for column grants)
This way you don't need to parse the identifier to split on dots and remove
backticks. You can even use [] for global grants and not split them into
global/db/table/column lists, but have simply
[
{ "name ": [], "access": 1 },
{ "name": ["some_db"], "access": 4194305 },
{ "name": ["some_other_db"], "access": 2 }
{ "name": ["some_other_db", "some_table_second"], "access": 2 },
{ "name": ["some_other_db", "some_table"], "access": 1 },
{ "name": ["some_other_db", "some_table_third"], "access": 4 },
{ "name": ["some_other_db", "some_table_fourth"], "access": 1 }
{ "name": ["some_other_db", "some_table_third", "a"], "access": 1 },
{ "name": ["some_other_db", "some_table_fourth", "b"], "access": 2 },
{ "name": ["some_other_db", "some_table_fourth", "c"], "access": 2 },
{ "name": ["some_other_db", "some_table_third", "b"], "access": 1 },
]
May be it's easier if they're split into global/db/table/column, but there's
defenitely no reason to put all identifier chain in one json property, quoted
according to SQL rules.
> + "access": 2
> + },
> +
> + {
> + "name": "`some_other_db`.`some_table`",
> + "access": 1
> + },
> +
> + {
> + "name": "`some_other_db`.`some_table_third`",
> + "access": 4
> + },
> +
> + {
> + "name": "`some_other_db`.`some_table_fourth`",
> + "access": 1
> + }
> + ],
> + "column":
> + [
> +
> + {
> + "name": "`some_other_db`.`some_table_third`.`a`",
> + "access": 1
> + },
> +
> + {
> + "name": "`some_other_db`.`some_table_fourth`.`b`",
> + "access": 2
> + },
> +
> + {
> + "name": "`some_other_db`.`some_table_fourth`.`c`",
> + "access": 2
> + },
> +
> + {
> + "name": "`some_other_db`.`some_table_third`.`b`",
> + "access": 1
> + },
> +
> + {
> + "name": "`some_other_db`.`some_table_fourth`.`a`",
> + "access": 2
> + }
> + ],
> + "version_id": VERSION_ID
> +}
> +select user, host, JSON_DETAILED(JSON_EXTRACT(priv, '$.deny')) from mysql.global_priv where user = 'bar';
> +user host JSON_DETAILED(JSON_EXTRACT(priv, '$.deny'))
> +bar {
> + "global": 2,
> + "db":
> + [
> +
> + {
> + "name": "`some_other_db`",
> + "access": 2
> + }
> + ],
> + "version_id": VERSION_ID
> +}
> +drop user foo;
> +drop role bar;
> diff --git a/mysql-test/suite/deny/dml.test b/mysql-test/suite/deny/dml.test
> --- /dev/null
> +++ b/mysql-test/suite/deny/dml.test
> @@ -0,0 +1,241 @@
would be good to have a comment at the top of every test file you add
to mention the 1) MDEV and 2) the purpose for that file to exist.
E.g. in this case I wouldn't need to waste time guessing how dml.test
is different from columns.test, if both have column-level grants.
And even the content looks suspiciously the same.
> +--source include/not_embedded.inc
> +
> +create user foo;
> +
> +create database deny_db;
> +create table deny_db.t1 (a int, b int, secret int);
> +insert into deny_db.t1 values (1, 2, 3);
> +
> +create table deny_db.t2 (a int, b int, secret int);
> +insert into deny_db.t2 values (10, 20, 30);
> +
> +grant select (a) on deny_db.t1 to foo;
> +grant insert (a) on deny_db.t1 to foo;
> +grant update (a) on deny_db.t1 to foo;
> +grant delete on deny_db.t1 to foo;
> +
> +--connect (con1,localhost,foo,,)
> +insert into deny_db.t1 (a) values (1), (2), (3);
> +disconnect con1;
> +
> +connection default;
> +deny insert on deny_db.* to foo;
> +
> +--connect (con1,localhost,foo,,)
> +--error ER_TABLEACCESS_DENIED_ERROR
> +insert into deny_db.t1 (a) values (1), (2), (3);
> +update deny_db.t1 set a=a*10;
> +delete from deny_db.t1 where a = 20;
> +disconnect con1;
> +
> +connection default;
> +select * from deny_db.t1 order by a;
> +truncate deny_db.t1;
> +
> +revoke deny insert on deny_db.* from foo;
> +
> +
> +--connect (con1,localhost,foo,,)
> +insert into deny_db.t1 (a) values (1), (2), (3);
> +--echo #
> +--echo # Test that revoking insert deny allows inserting only into allowed
> +--echo # to insert columns.
> +--echo #
> +insert into deny_db.t1 (a) values (1), (2), (3);
> +
> +--error ER_COLUMNACCESS_DENIED_ERROR
> +insert into deny_db.t1 (a,b) values (1,10), (2, 20), (3, 30);
> +disconnect con1;
> +
> +connection default;
> +--echo #
> +--echo # Test delete/update command denies.
> +--echo #
> +deny select on deny_db.* to foo;
> +
> +--connect (con1,localhost,foo,,)
> +--echo #
> +--echo # Test not allowed to delete / update because of lack of select
> +--echo # rights.
> +--echo #
> +
> +show grants;
> +--error ER_COLUMNACCESS_DENIED_ERROR
> +update deny_db.t1 set a=a*10 where a = 1;
Did you verify that in all cases the error is the same as if the privilege
simply wasn't granted in the first place?
In this case the error is correct, I've checked.
> +
> +--error ER_COLUMNACCESS_DENIED_ERROR
> +delete from deny_db.t1 where a = 20;
> +disconnect con1;
> +
> +
> +connection default;
> +--echo #
> +--echo # Now deny update and delete directly, but keep select.
> +--echo #
> +revoke deny select on deny_db.* from foo;
> +deny delete, update on deny_db.* to foo;
> +
> +--connect (con1,localhost,foo,,)
> +--echo #
> +--echo # Test not allowed to delete / update because of lack of delete/update
> +--echo # rights.
> +--echo #
> +
> +--error ER_TABLEACCESS_DENIED_ERROR
> +update deny_db.t1 set a=a*10;
> +
> +--error ER_TABLEACCESS_DENIED_ERROR
> +delete from deny_db.t1 where a = 20;
> +disconnect con1;
> +
> +--echo #
> +--echo # Test masking table level grants.
> +--echo #
> +
> +connection default;
> +grant select on deny_db.t1 to foo;
> +grant insert on deny_db.t1 to foo;
> +grant update on deny_db.t1 to foo;
> +grant delete on deny_db.t1 to foo;
> +
> +--connect (con1,localhost,foo,,)
> +insert into deny_db.t1 (a,b) values (1,10), (2, 20), (3, 30);
> +disconnect con1;
> +
> +connection default;
> +deny insert on deny_db.* to foo;
> +
> +--connect (con1,localhost,foo,,)
> +--error ER_TABLEACCESS_DENIED_ERROR
> +insert into deny_db.t1 (a,b) values (1,10), (2, 20), (3, 30);
> +
> +--error ER_TABLEACCESS_DENIED_ERROR
> +update deny_db.t1 set a=a*10;
> +
> +--error ER_TABLEACCESS_DENIED_ERROR
> +delete from deny_db.t1 where a = 20;
> +
> +disconnect con1;
> +
> +connection default;
> +revoke deny update, delete on deny_db.* from foo;
> +
> +--connect (con1,localhost,foo,,)
> +--error ER_TABLEACCESS_DENIED_ERROR
> +insert into deny_db.t1 (a,b) values (1,10), (2, 20), (3, 30);
> +
> +update deny_db.t1 set a=a*10;
> +delete from deny_db.t1 where a = 20;
> +disconnect con1;
> +
> +--echo #
> +--echo # Test masking database & global level grants.
> +--echo #
> +connection default;
> +grant select, insert, update, delete on *.* to foo;
> +grant select, insert, update, delete on deny_db.* to foo;
> +grant select, insert, update, delete on deny_db.t1 to foo;
> +deny select, insert on deny_db.* to foo;
> +
> +--connect (con1,localhost,foo,,)
> +--error ER_TABLEACCESS_DENIED_ERROR
> +insert into deny_db.t1 (a,b) values (1,10), (2, 20), (3, 30);
> +
> +--error ER_COLUMNACCESS_DENIED_ERROR
> +update deny_db.t1 set a=a*10;
> +
> +--error ER_COLUMNACCESS_DENIED_ERROR
> +delete from deny_db.t1 where a = 20;
> +disconnect con1;
> +
> +connection default;
> +revoke deny select on deny_db.* from foo;
> +deny update, delete on deny_db.* to foo;
> +
> +--connect (con1,localhost,foo,,)
> +--error ER_TABLEACCESS_DENIED_ERROR
> +insert into deny_db.t1 (a,b) values (1,10), (2, 20), (3, 30);
> +
> +--error ER_TABLEACCESS_DENIED_ERROR
> +update deny_db.t1 set a=a*10;
> +
> +--error ER_TABLEACCESS_DENIED_ERROR
> +delete from deny_db.t1 where a = 20;
> +disconnect con1;
> +
> +connection default;
> +deny select on deny_db.* to foo;
> +
> +--connect (con1,localhost,foo,,)
> +--error ER_TABLEACCESS_DENIED_ERROR
> +insert into deny_db.t1 (a,b) values (1,10), (2, 20), (3, 30);
> +
> +--error ER_TABLEACCESS_DENIED_ERROR
> +update deny_db.t1 set a=a*10;
> +
> +--error ER_TABLEACCESS_DENIED_ERROR
> +delete from deny_db.t1 where a = 20;
> +disconnect con1;
> +
> +--echo #
> +--echo # Test revoking all denies.
> +--echo #
> +connection default;
> +revoke deny select, insert, update, delete on deny_db.* from foo;
by the way, what does REVOKE ALL PRIVILEGES do, does it remove denies?
please, add a test.
> +truncate deny_db.t1;
> +
> +--connect (con1,localhost,foo,,)
> +insert into deny_db.t1 (a,b) values (1,10), (2, 20), (3, 30);
> +update deny_db.t1 set a=a*10;
> +select * from deny_db.t1;
> +delete from deny_db.t1 where a = 20;
> +disconnect con1;
> +
> +connection default;
> +
> +--echo #
> +--echo # Test masking via table level denies
> +--echo #
> +
> +deny select on deny_db.t1 to foo;
> +
> +--connect (con1,localhost,foo,,)
> +insert into deny_db.t1 (a,b) values (1,10), (2, 20), (3, 30);
> +--error ER_COLUMNACCESS_DENIED_ERROR
> +update deny_db.t1 set a=a*10;
> +--error ER_COLUMNACCESS_DENIED_ERROR
> +update deny_db.t1 set a=a*10 where a = 10;
> +--echo #
> +--echo # Delete works without select.
> +--echo #
> +delete from deny_db.t1;
> +--error ER_COLUMNACCESS_DENIED_ERROR
> +delete from deny_db.t1 where a = 20;
> +
> +--error ER_TABLEACCESS_DENIED_ERROR
> +select * from deny_db.t1;
> +disconnect con1;
> +
> +connection default;
> +revoke deny select on deny_db.t1 from foo;
> +deny insert on deny_db.t1 to foo;
> +deny update on deny_db.t1 to foo;
> +deny delete on deny_db.t1 to foo;
> +
> +--connect (con1,localhost,foo,,)
> +--error ER_TABLEACCESS_DENIED_ERROR
> +insert into deny_db.t1 (a,b) values (1,10), (2, 20), (3, 30);
> +--error ER_TABLEACCESS_DENIED_ERROR
> +update deny_db.t1 set a=a*10;
> +--error ER_TABLEACCESS_DENIED_ERROR
> +update deny_db.t1 set a=a*10 where a = 10;
> +--error ER_TABLEACCESS_DENIED_ERROR
> +delete from deny_db.t1;
> +--error ER_TABLEACCESS_DENIED_ERROR
> +delete from deny_db.t1 where a = 20;
> +
> +select * from deny_db.t1;
> +
> +connection default;
> +drop database deny_db;
> +drop user foo;
> diff --git a/mysql-test/suite/deny/global.result b/mysql-test/suite/deny/global.result
> --- /dev/null
> +++ b/mysql-test/suite/deny/global.result
> @@ -0,0 +1,327 @@
> +create user foo;
> +create database deny_db;
> +create table deny_db.t1 (a int, b int, secret int);
> +insert into deny_db.t1 values (1, 2, 3);
> +create table deny_db.t2 (a int, b int, secret int);
> +insert into deny_db.t2 values (10, 20, 30);
> +#
> +# Test global denies.
> +#
> +show databases;
> +Database
> +deny_db
> +information_schema
> +mtr
> +mysql
> +performance_schema
> +sys
> +test
> +grant select (secret) on deny_db.t1 to foo;
> +connect con1,localhost,foo,,;
> +select * from deny_db2.t1;
> +ERROR 42000: SELECT command denied to user 'foo'@'localhost' for table 't1'
> +show databases;
> +Database
> +deny_db
> +information_schema
> +test
> +use deny_db;
> +#
> +# It is still possible to leak column names without denies.
> +#
> +select * from deny_db.t1;
> +ERROR 42000: SELECT command denied to user 'foo'@'localhost' for column 'a' in table 't1'
this is a bug. See my test above, it's ER_TABLEACCESS_DENIED_ERROR.
Apparently, it returns ER_TABLEACCESS_DENIED_ERROR if there's at least
one granted column before the non-granted column.
That is, if you'd have
create table deny_db.t1 (secret int, a int, b int);
you'd get ER_TABLEACCESS_DENIED_ERROR.
please fix it (where applicable, 10.3, perhaps?)
> +select a from deny_db.t1;
> +ERROR 42000: SELECT command denied to user 'foo'@'localhost' for column 'a' in table 't1'
> +select b from deny_db.t1;
> +ERROR 42000: SELECT command denied to user 'foo'@'localhost' for column 'b' in table 't1'
> +select table_name, column_name from information_schema.columns
> +where table_schema = 'deny_db';
> +table_name column_name
> +t1 secret
> +#
> +# Invalid field error is returned if table exists.
> +#
> +select c from deny_db.t1;
> +ERROR 42S22: Unknown column 'c' in 'field list'
I feel like the whole column-leaking behavior is a bug too.
This, likely, should be ER_COLUMNACCESS_DENIED_ERROR too
> +#
> +# Table access denied if table does exist but is not granted.
> +#
> +select c from deny_db.t2;
> +ERROR 42000: SELECT command denied to user 'foo'@'localhost' for table 't2'
> +#
> +# Same error if table does not exist.
> +#
> +select c from deny_db.t_not_exists;
> +ERROR 42000: SELECT command denied to user 'foo'@'localhost' for table 't_not_exists'
> +select secret from deny_db.t1;
> +secret
> +3
> +#
> +# Now apply deny.
> +#
> +connection default;
> +deny select on *.* to foo;
I see you reconnect to apply denies. What are the rules here?
With grants, table/column grants work right away, while
global and current-db grants are cached in the thd.
but table/column denies aren't stored in separate tables,
that's why the question. What are the rules here?
> +disconnect con1;
> +connect con1,localhost,foo,,;
> +show databases;
> +Database
> +information_schema
> +test
> +use information_schema;
> +use deny_db;
> +ERROR 42000: Access denied for user 'foo'@'%' to database 'deny_db'
> +select * from deny_db.t1;
> +ERROR 42000: SELECT command denied to user 'foo'@'localhost' for table 't1'
> +select c from deny_db.t1;
> +ERROR 42000: SELECT command denied to user 'foo'@'localhost' for table 't1'
> +select c from deny_db.t2;
> +ERROR 42000: SELECT command denied to user 'foo'@'localhost' for table 't2'
> +select c from deny_db.t_not_exists;
> +ERROR 42000: SELECT command denied to user 'foo'@'localhost' for table 't_not_exists'
> +select secret from deny_db.t1;
> +ERROR 42000: SELECT command denied to user 'foo'@'localhost' for table 't1'
> +#
> +# Grant table access then test deny.
> +#
> +connection default;
> +grant select on deny_db.t1 to foo;
> +connection con1;
> +show databases;
> +Database
> +information_schema
> +test
> +use information_schema;
> +use deny_db;
> +ERROR 42000: Access denied for user 'foo'@'%' to database 'deny_db'
> +select * from deny_db.t1;
> +ERROR 42000: SELECT command denied to user 'foo'@'localhost' for table 't1'
> +select c from deny_db.t1;
> +ERROR 42000: SELECT command denied to user 'foo'@'localhost' for table 't1'
> +select c from deny_db.t2;
> +ERROR 42000: SELECT command denied to user 'foo'@'localhost' for table 't2'
> +select c from deny_db.t_not_exists;
> +ERROR 42000: SELECT command denied to user 'foo'@'localhost' for table 't_not_exists'
> +select secret from deny_db.t1;
> +ERROR 42000: SELECT command denied to user 'foo'@'localhost' for table 't1'
> +#
> +# Grant database access then test deny.
> +#
> +connection default;
> +grant select on deny_db.* to foo;
> +connection con1;
> +show databases;
> +Database
> +information_schema
> +test
> +use information_schema;
> +use deny_db;
> +ERROR 42000: Access denied for user 'foo'@'%' to database 'deny_db'
> +select * from deny_db.t1;
> +ERROR 42000: SELECT command denied to user 'foo'@'localhost' for table 't1'
> +select c from deny_db.t1;
> +ERROR 42000: SELECT command denied to user 'foo'@'localhost' for table 't1'
> +select c from deny_db.t2;
> +ERROR 42000: SELECT command denied to user 'foo'@'localhost' for table 't2'
> +select c from deny_db.t_not_exists;
> +ERROR 42000: SELECT command denied to user 'foo'@'localhost' for table 't_not_exists'
> +select secret from deny_db.t1;
> +ERROR 42000: SELECT command denied to user 'foo'@'localhost' for table 't1'
> +#
> +# Grant global access then test deny.
> +#
> +connection default;
> +grant select on *.* to foo;
> +connection con1;
> +show databases;
> +Database
> +information_schema
> +test
> +use information_schema;
> +use deny_db;
> +ERROR 42000: Access denied for user 'foo'@'%' to database 'deny_db'
> +select * from deny_db.t1;
> +ERROR 42000: SELECT command denied to user 'foo'@'localhost' for table 't1'
> +select c from deny_db.t1;
> +ERROR 42000: SELECT command denied to user 'foo'@'localhost' for table 't1'
> +select c from deny_db.t2;
> +ERROR 42000: SELECT command denied to user 'foo'@'localhost' for table 't2'
> +select c from deny_db.t_not_exists;
> +ERROR 42000: SELECT command denied to user 'foo'@'localhost' for table 't_not_exists'
> +select secret from deny_db.t1;
> +ERROR 42000: SELECT command denied to user 'foo'@'localhost' for table 't1'
> +connection default;
> +#
> +# Test underlying data loading.
> +#
> +select user, host, JSON_EXTRACT(priv, '$.deny') from mysql.global_priv where user = 'foo';
> +user host JSON_EXTRACT(priv, '$.deny')
> +foo % {"global": 1, "version_id": 101100}
> +show grants for foo;
> +Grants for foo@%
> +GRANT SELECT ON *.* TO `foo`@`%`
> +DENY SELECT ON *.* TO `foo`@`%`
> +GRANT SELECT ON `deny_db`.* TO `foo`@`%`
> +GRANT SELECT, SELECT (secret) ON `deny_db`.`t1` TO `foo`@`%`
meaning, the user will see what is denied to him?
I expect it'll be a questionable item, some users will want denies to be not shown.
Although I don't see how they cannot be.
> +flush privileges;
> +show grants for foo;
> +Grants for foo@%
> +GRANT SELECT ON *.* TO `foo`@`%`
> +DENY SELECT ON *.* TO `foo`@`%`
> +GRANT SELECT ON `deny_db`.* TO `foo`@`%`
> +GRANT SELECT, SELECT (secret) ON `deny_db`.`t1` TO `foo`@`%`
> +connection con1;
> +show databases;
> +Database
> +information_schema
> +test
> +use information_schema;
> +use deny_db;
> +ERROR 42000: Access denied for user 'foo'@'%' to database 'deny_db'
> +select * from deny_db.t1;
> +ERROR 42000: SELECT command denied to user 'foo'@'localhost' for table 't1'
> +select c from deny_db.t1;
> +ERROR 42000: SELECT command denied to user 'foo'@'localhost' for table 't1'
> +select c from deny_db.t2;
> +ERROR 42000: SELECT command denied to user 'foo'@'localhost' for table 't2'
> +select c from deny_db.t_not_exists;
> +ERROR 42000: SELECT command denied to user 'foo'@'localhost' for table 't_not_exists'
> +select secret from deny_db.t1;
> +ERROR 42000: SELECT command denied to user 'foo'@'localhost' for table 't1'
> +disconnect con1;
> +connection default;
> +#
> +# Now test insert not being denied.
> +#
> +grant insert(secret) on deny_db.t1 to foo;
> +connect con1,localhost,foo,,;
> +show databases;
> +Database
> +deny_db
> +information_schema
> +test
> +insert into deny_db.t1(secret) values (10000);
> +disconnect con1;
> +connection default;
> +drop database deny_db;
> +drop user foo;
> +#
> +# Test denying all rights globally.
please, add tests with DENY TO PUBLIC
> +# User should still be able to connect and see information_schema
> +# *at least*.
> +#
> +create user foo;
> +create database some_db;
> +grant select on *.* to foo;
> +show grants for foo;
> +Grants for foo@%
> +GRANT SELECT ON *.* TO `foo`@`%`
> +deny select on *.* to foo;
> +show grants for foo;
> +Grants for foo@%
> +GRANT SELECT ON *.* TO `foo`@`%`
> +DENY SELECT ON *.* TO `foo`@`%`
> +deny all on *.* to foo;
> +show grants for foo;
> +Grants for foo@%
> +GRANT SELECT ON *.* TO `foo`@`%`
> +DENY ALL PRIVILEGES ON *.* TO `foo`@`%`
> +connect con1,localhost,foo,,information_schema;
> +show databases;
> +Database
> +information_schema
> +disconnect con1;
> +connection default;
> +drop user foo;
> +drop database some_db;
> +##############################################
> +# Test SELECT command interacting with deny. #
> +##############################################
> +create user foo;
> +create user bar;
> +create database some_db;
> +create table some_db.t1 (a int, secret int);
> +insert into some_db.t1 values (1, 100);
> +use some_db;
> +create view v1 as (select a from t1);
> +create view v2 as (select secret from t1);
> +connect con1,localhost,foo,,;
> +select table_name, table_type from information_schema.tables where table_schema like 'some_db';
> +table_name table_type
> +disconnect con1;
> +connection default;
> +grant select on *.* to foo;
> +grant select on some_db.* to foo;
> +grant select on some_db.t1 to foo;
> +grant select(a) on some_db.t1 to foo;
> +#
> +# See what foo sees before denies.
> +#
> +connect con1,localhost,foo,,;
> +select table_name, table_type from information_schema.tables where table_schema like 'some_db';
> +table_name table_type
> +t1 BASE TABLE
> +v1 VIEW
> +v2 VIEW
> +disconnect con1;
> +connection default;
> +show grants for foo;
> +Grants for foo@%
> +GRANT SELECT ON *.* TO `foo`@`%`
> +GRANT SELECT ON `some_db`.* TO `foo`@`%`
> +GRANT SELECT, SELECT (a) ON `some_db`.`t1` TO `foo`@`%`
> +deny select on *.* to foo;
> +show grants for foo;
> +Grants for foo@%
> +GRANT SELECT ON *.* TO `foo`@`%`
> +DENY SELECT ON *.* TO `foo`@`%`
> +GRANT SELECT ON `some_db`.* TO `foo`@`%`
> +GRANT SELECT, SELECT (a) ON `some_db`.`t1` TO `foo`@`%`
> +connect con1,localhost,foo,,;
> +show grants;
> +Grants for foo@%
> +GRANT SELECT ON *.* TO `foo`@`%`
> +DENY SELECT ON *.* TO `foo`@`%`
> +GRANT SELECT ON `some_db`.* TO `foo`@`%`
> +GRANT SELECT, SELECT (a) ON `some_db`.`t1` TO `foo`@`%`
> +use some_db;
> +ERROR 42000: Access denied for user 'foo'@'%' to database 'some_db'
> +select * from some_db.t1;
> +ERROR 42000: SELECT command denied to user 'foo'@'localhost' for table 't1'
> +select * from some_db.v1;
> +ERROR 42000: SELECT command denied to user 'foo'@'localhost' for table 'v1'
> +select * from some_db.v2;
> +ERROR 42000: SELECT command denied to user 'foo'@'localhost' for table 'v2'
> +select table_name, table_type from information_schema.tables where table_schema like "some_db";
> +table_name table_type
> +select table_name, column_name from information_schema.columns where table_schema like "some_db";
> +table_name column_name
> +disconnect con1;
> +connection default;
> +grant insert(a) on some_db.t1 to bar;
> +grant insert(a) on some_db.t1 to foo;
> +connect con1,localhost,foo,,;
> +select table_name, column_name from information_schema.columns where table_schema like "some_db";
> +table_name column_name
> +t1 a
> +disconnect con1;
> +connect con2,localhost,bar,,;
> +select table_name, column_name from information_schema.columns where table_schema like "some_db";
> +table_name column_name
> +t1 a
> +disconnect con2;
> +connection default;
> +deny insert on *.* to foo;
> +deny insert on *.* to bar;
> +connect con1,localhost,foo,,;
> +select table_name, column_name from information_schema.columns where table_schema like "some_db";
> +table_name column_name
> +disconnect con1;
> +connect con2,localhost,bar,,;
> +select table_name, column_name from information_schema.columns where table_schema like "some_db";
> +table_name column_name
> +disconnect con2;
> +connection default;
> +drop user foo;
> +drop user bar;
> +drop database some_db;
> diff --git a/mysql-test/suite/deny/ignore_denies.result b/mysql-test/suite/deny/ignore_denies.result
> --- /dev/null
> +++ b/mysql-test/suite/deny/ignore_denies.result
> @@ -0,0 +1,76 @@
> +create user foo;
> +create role bar;
> +grant select, ignore denies on *.* to foo;
> +deny select on *.* to foo;
> +deny select on mysql.* to foo;
> +deny select on mysql.global_priv to foo;
> +deny select(user) on mysql.global_priv to foo;
> +grant bar to foo;
> +grant ignore denies on *.* to bar;
> +connect con1,localhost,foo,,;
> +#
> +# The user should still be able to access mysql.global_priv due
> +# to the IGNORE DENIES privilege being active.
> +#
> +select JSON_EXTRACT(priv, '$.deny') from mysql.global_priv where user = 'foo';
> +JSON_EXTRACT(priv, '$.deny')
> +{"global": 1, "db": [{"name": "`mysql`", "access": 1}], "table": [{"name": "`mysql`.`global_priv`", "access": 1}], "column": [{"name": "`mysql`.`global_priv`.`user`", "access": 1}], "version_id": VERSION_ID}
> +disconnect con1;
> +connection default;
> +#
> +# Now test inheriting IGNORE DENIES from a role.
> +#
> +revoke ignore denies on *.* from foo;
> +connect con1,localhost,foo,,;
> +select JSON_EXTRACT(priv, '$.deny') from mysql.global_priv where user = 'foo';
> +ERROR 42000: SELECT command denied to user 'foo'@'localhost' for table 'global_priv'
> +#
> +# Bar provides foo with IGNORE DENIES privilege.
> +#
> +set role bar;
> +select JSON_EXTRACT(priv, '$.deny') from mysql.global_priv where user = 'foo';
> +JSON_EXTRACT(priv, '$.deny')
> +{"global": 1, "db": [{"name": "`mysql`", "access": 1}], "table": [{"name": "`mysql`.`global_priv`", "access": 1}], "column": [{"name": "`mysql`.`global_priv`.`user`", "access": 1}], "version_id": VERSION_ID}
> +disconnect con1;
> +connection default;
> +#
> +# Denying IGNORE DENIES, overrides a user's ability to ignore denies.
This is very questionable. One can DENY IGNORE DENIES TO PUBLIC
and basically lock the server down for everyone, there will be
no way to override that.
(this is an observation, not ready to suggest a solution atm)
> +#
> +deny ignore denies on *.* to foo;
> +grant ignore denies on *.* to foo;
> +connect con1,localhost,foo,,;
> +#
> +# Foo can't ignore the SELECT priv deny.
> +#
> +select JSON_EXTRACT(priv, '$.deny') from mysql.global_priv where user = 'foo';
> +ERROR 42000: SELECT command denied to user 'foo'@'localhost' for table 'global_priv'
> +#
> +# And neither if bar role provides the ignore deny priv.
> +#
> +set role bar;
> +select JSON_EXTRACT(priv, '$.deny') from mysql.global_priv where user = 'foo';
> +ERROR 42000: SELECT command denied to user 'foo'@'localhost' for table 'global_priv'
> +disconnect con1;
> +connection default;
> +#
> +# Now test what happens when IGNORE DENIES is denied from a role.
> +#
> +revoke deny ignore denies on *.* from foo;
> +deny ignore denies on *.* to bar;
> +connect con1,localhost,foo,,;
> +#
> +# Foo can ignore the SELECT priv deny when Bar isn't activated
> +#
> +select JSON_EXTRACT(priv, '$.deny') from mysql.global_priv where user = 'foo';
> +JSON_EXTRACT(priv, '$.deny')
> +{"global": 1, "db": [{"name": "`mysql`", "access": 1}], "table": [{"name": "`mysql`.`global_priv`", "access": 1}], "column": [{"name": "`mysql`.`global_priv`.`user`", "access": 1}], "version_id": VERSION_ID}
> +#
> +# But when Bar is active, denies can not be ignored any more.
> +#
> +set role bar;
> +select JSON_EXTRACT(priv, '$.deny') from mysql.global_priv where user = 'foo';
> +ERROR 42000: SELECT command denied to user 'foo'@'localhost' for table 'global_priv'
> +disconnect con1;
> +connection default;
> +drop user foo;
> +drop role bar;
> diff --git a/mysql-test/suite/deny/show_generic.result b/mysql-test/suite/deny/show_generic.result
> --- /dev/null
> +++ b/mysql-test/suite/deny/show_generic.result
> @@ -0,0 +1,197 @@
> +#
> +# This test checks that denies take part in SHOW commands properly.
> +#
> +create user foo;
> +create database some_db;
> +create table some_db.t1 (id int);
> +connect con1,localhost,foo,,;
> +show tables from some_db;
> +ERROR 42000: Access denied for user 'foo'@'%' to database 'some_db'
> +disconnect con1;
> +connection default;
> +grant insert on some_db.t1 to foo;
> +connect con1,localhost,foo,,;
> +show tables from some_db;
> +Tables_in_some_db
> +t1
> +disconnect con1;
> +connection default;
> +deny insert on some_db.* to foo;
> +connect con1,localhost,foo,,;
> +show databases;
> +Database
> +information_schema
> +test
> +show tables from some_db;
> +ERROR 42000: Access denied for user 'foo'@'%' to database 'some_db'
> +disconnect con1;
> +connection default;
> +#
> +# Test that dropping a user does not give access to connections that
> +# previously had elements denied.
> +#
> +# We will use two users, foo which will receive denies, bar which will
> +# not. This will showcase the difference in behaviour.
> +#
> +create user bar;
> +#
> +# This global grant is cached in sctx->master_access on connection.
> +#
> +grant select on *.* to foo;
> +grant select on *.* to bar;
> +connect con1,localhost,foo,,;
> +show tables from some_db;
> +Tables_in_some_db
> +t1
> +disconnect con1;
> +connection default;
> +#
> +# Here we add a mask for some_db, it should no longer be visible to foo.
> +#
> +deny select on some_db.* to foo;
> +connect con1,localhost,foo,,;
> +use some_db;
> +ERROR 42000: Access denied for user 'foo'@'%' to database 'some_db'
> +show tables from some_db;
> +ERROR 42000: Access denied for user 'foo'@'%' to database 'some_db'
> +#
> +# bar user has access to the database (no denies present)
> +#
> +connect con2,localhost,bar,,;
> +use some_db;
> +show tables from some_db;
> +Tables_in_some_db
> +t1
> +#
> +# Do not disconnect foo & bar, but drop the users.
> +#
> +connection default;
> +drop user foo;
> +drop user bar;
> +#
> +# Our current running connection, because it featured denies previously
> +# now doesn't have access.
> +#
> +connection con1;
> +use some_db;
> +ERROR 42000: Access denied for user 'foo'@'%' to database 'some_db'
> +show tables from some_db;
> +ERROR 42000: Access denied for user 'foo'@'%' to database 'some_db'
> +disconnect con1;
> +#
> +# However, bar does not have any denies active. In this case we can keep
> +# the previous MariaDB behaviour of using the "cache" within
> +# sctx->master_access to grant access.
This comment is confusing. As far as I can see both foo and bar preserve the
behavior after `drop user`. Which is fine and what I was expected anyway.
But comments imply there's some difference in behavior between them?
> +#
> +connection con2;
> +use some_db;
> +show tables from some_db;
> +Tables_in_some_db
> +t1
> +disconnect con2;
> +connection default;
> +drop database some_db;
> +#
> +# Test table level denies interacting with show tables
> +#
> +create database some_db;
> +create user foo;
> +create table some_db.t1 (a int, b int);
> +create table some_db.t2 (a int, b int);
> +grant select on *.* to foo;
> +deny select on some_db.t1 to foo;
> +deny insert on some_db.t2 to foo;
> +select user, host, JSON_EXTRACT(priv, '$.deny') from mysql.global_priv
> +where user = 'foo';
> +user host JSON_EXTRACT(priv, '$.deny')
> +foo % {"table": [{"name": "`some_db`.`t2`", "access": 2}, {"name": "`some_db`.`t1`", "access": 1}], "version_id": VERSION_ID}
> +connect con1,localhost,foo,,;
> +show tables from some_db;
> +Tables_in_some_db
> +t2
> +show columns from some_db.t1;
> +ERROR 42000: SELECT command denied to user 'foo'@'localhost' for table 't1'
> +show columns from some_db.t2;
> +Field Type Null Key Default Extra
> +a int(11) YES NULL
> +b int(11) YES NULL
> +select table_name, column_name, privileges from information_schema.columns
> +where table_schema like 'some_db' order by table_name, column_name;
> +table_name column_name privileges
> +t2 a select
> +t2 b select
> +disconnect con1;
> +connection default;
> +grant insert on some_db.* to foo;
> +connect con1,localhost,foo,,;
> +show tables from some_db;
> +Tables_in_some_db
> +t1
> +t2
> +#
> +# See MDEV-28783, this should not error out when global/db grants exist
> +# (except for SELECT priv).
> +#
> +show columns from some_db.t1;
> +ERROR 42000: SELECT command denied to user 'foo'@'localhost' for table 't1'
> +show columns from some_db.t2;
> +Field Type Null Key Default Extra
> +a int(11) YES NULL
> +b int(11) YES NULL
> +select table_name, column_name, privileges from information_schema.columns
> +where table_schema like 'some_db' order by table_name, column_name;
> +table_name column_name privileges
> +t1 a insert
> +t1 b insert
> +t2 a select
> +t2 b select
> +disconnect con1;
> +connection default;
> +deny insert on some_db.t1 to foo;
> +deny select on some_db.t2 to foo;
> +connect con1,localhost,foo,,;
> +#
> +# some_db should still be visible, but it should show up as empty.
> +#
> +show databases;
> +Database
> +information_schema
> +mtr
> +mysql
> +performance_schema
> +some_db
> +sys
> +test
> +show tables from some_db;
> +Tables_in_some_db
> +show columns from some_db.t1;
> +ERROR 42000: SELECT command denied to user 'foo'@'localhost' for table 't1'
> +show columns from some_db.t2;
> +ERROR 42000: SELECT command denied to user 'foo'@'localhost' for table 't2'
> +select table_name, column_name, privileges from information_schema.columns
> +where table_schema like 'some_db' order by table_name, column_name;
> +table_name column_name privileges
> +disconnect con1;
> +connection default;
> +grant select(a) on some_db.t1 to foo;
> +grant update(a) on some_db.t1 to foo;
> +connect con1,localhost,foo,,;
> +#
> +# Update privilege on the column is not masked, only see a column.
> +#
> +show tables from some_db;
> +Tables_in_some_db
> +t1
> +show columns from some_db.t1;
> +Field Type Null Key Default Extra
> +a int(11) YES NULL
> +show columns from some_db.t2;
> +ERROR 42000: SELECT command denied to user 'foo'@'localhost' for table 't2'
> +select table_name, column_name, privileges from information_schema.columns
> +where table_schema like 'some_db' order by table_name, column_name;
> +table_name column_name privileges
> +t1 a update
> +disconnect con1;
> +connection default;
> +drop user foo;
> +drop database some_db;
Regards,
Sergei
VP of MariaDB Server Engineering
and security(a)mariadb.org
