2

I have searched for this question but cannot find an answer. Or, the answers I have found have been related to SQL Server. This is for MySQL 5.x.

Anyway, say I have a table called Employees and in that table, I have various columns like:

id, lastname, firstname, salary, ssn, hiredate

Now, I have some users that need to be able to select from the VIEW but not the TABLE.

For example:

--user a
select * from employee_view;

--shows:
id, lastname, firstname, hiredate

--user a
select * from employee;   // access denied  (didn't use view)

Then I have an HR User:

--HR user
select * from employee_view;

-- shows:
id, lastname, firstname, ssn, salary, hiredate

-- HR user
select * from employee;  // access denied (didn't use view)

Is there any way I can do this?

Thanks.

  • 1
    Run SELECT * FROM mysql.user WHERE user='$youruser'; and edit your question with the result. – oNare Jul 6 '15 at 20:38
0

I figured it out myself.

First, I created an account that ONLY had permissions to certain columns (not all). I then created a view for those columns and used the user as the definer.

Then, I created another test account and ONLY gave them permissions to that view.

So the test user could not see the underlying table but only the view and columns.

Works great.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.