Grant a user permission to only view a MySQL view and nothing else

Question

This question was originally using MySQL 5.1.44, but is applicable to MySQL 8.0+ too.

Let's say I have a table with records inserted by different users of my application. How can I give a specific user access to only see his/her records in that table? I've thought about creating a VIEW with his/her records, but I don't know how to create a MySQL user that can only see that VIEW.

So, is it possible to create a MySQL user that only has access to a single VIEW? Can this user also be made so they read-only access to that VIEW?

Thanks!

PS: What I call users in my example are really subsidiary offices that want to access their records with their own applications.

Solution 1

GRANT SELECT ON database1.view1 TO 'someuser'@'somehost';

Solution 2

Besides

GRANT SELECT ON <database_name>.<view_name>
TO <user>@<host>

it's better to also do

GRANT SHOW VIEW
ON <database_name>.<view_name> TO <user>@<host>

so that a lot of SQL UI tool can get the view definition and work appropriately for the view.

Solution 3

I believe the original question is actually asking how to limit the rows to those owned by a given user. (The idea of creating one view per user, and then granting just that, seems like a workaround.)

You can do this by inserting the user() reference into the data table, and then filtering on that.

Using MySQL 5.6. Create a view that limits SELECT to just records owned by the current user:

-- check the current user
select user();

create table t1 (myId int, mydata varchar(200), myName varchar(200));

insert t1 select 1, 'my data yes', user();
insert t1 select 2, 'my data yes2', user();
insert t1 select 3, 'my data no', 'joe';

select * from t1;

create or replace view v1 AS
select * from t1 where myName = user();

select * from v1;

Solution 4

GRANT SELECT ON <database name>.<view name>
TO <user>@<host> IDENTIFIED BY '<password>'

Source: MySQL Documentation

View more solutions

Comments

[Dominik Goltermann]

Please note that the user that issues the create view command (called the view definer) also has influence on this. if definer != user that uses the view, than this can be quite confusing. For example if the definer has only select permissions on the underlying table, and another user has update permissions on the view, then trying to update the view will cause a permission denied error.

[QkiZ]

There's no IDENTIFIED BY in GRANT command. dev.mysql.com/doc/refman/8.0/en/grant.html