Grant a user permission to only view a mysql view

Question

The below question pertains to MySQL 5.1.44

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.

Answer 1

Besides

GRANT SELECT ON <database_name>.<view_name>
TO <user>@<host>
IDENTIFIED BY '<password>'

it's better to also do

GRANT SHOW VIEW
ON <database_name>.<view_name> TO <user>@<host>
IDENTIFIED BY '<password>'

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

Answer 2

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

Answer 3

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

Source: MySQL Documentation

Answer 4

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;

Answer 5

If you want to make the view read only which I suspect you do. Then you should create the view with the ALGORITHM = TEMPTABLE clause.

This will make the view read only virtue of it having to create a temp table.

Another way to achieve read only and this is depends on your data is to stick an aggregate function. For example if you have a view which is based on a table and shows all columns then you can stick a distinct onto the select.