0

I would like to be able to grant read privileges to all information within a view except the rows where DOB is not empty (i.e. user can only read row where DOB is null). Is this possible? An example (which doesn't work that I would like to work) is:

GRANT SELECT ON DRVADM WHERE DOB IS NULL TO user_2;

Thanks

Animorphs
  • 3
  • 1
  • 2
  • why don't you add the condition to the view itself? or make one more view with dob is null condition – Vamsi Prabhala May 28 '16 at 04:17
  • 1
    this is for a homework task unfortunately, and it has to use the DRVADM view, which already has a different user with different read privileges – Animorphs May 28 '16 at 04:19
  • apologies, its simply local sql executed through Ubuntu terminal – Animorphs May 28 '16 at 04:35
  • Possible duplicate of [mysql GRANT + WHERE](http://stackoverflow.com/questions/11693727/mysql-grant-where) – sstan May 28 '16 at 04:37
  • Ah yes, I think it is, sorry I couldn't find anything like that after searching. Guess we were set an impossible homework task – Animorphs May 28 '16 at 04:47
  • This looks so very similar to the following question by a different user 30 minutes before http://stackoverflow.com/q/37494907 – Drew May 28 '16 at 05:25
  • @Drew haha so it is, I assume that's someone in the same course as me – Animorphs May 28 '16 at 16:32

1 Answers1

0
  1. I assume you are using mysql (to confirm that, post the commands you use to connect to the database from your terminal session).
  2. You can not GRANT based on rows, only on the level of columns/tables. There is no WHERE clause to a GRANT SELECT statement in mysql docs
  3. You could consider creating a VIEW with only selected rows (the ones he has to see). Then grant user access to this VIEW (=to all its rows)
Artur Opalinski
  • 1,052
  • 7
  • 12
  • Yes, using mysql in terminal, connected with use database1; And as this is a homework task (as mentioned above) I cannot unfortunately create a new view, i have to use DRVADM which has another use allowed to read everything – Animorphs May 28 '16 at 04:52
  • If your task is literally 'to grant read privileges to all information within a view except some rows' then I do not see a possible solution with `mysql` (due to the reason explained in the second item of my answer). – Artur Opalinski May 28 '16 at 05:00
  • That is literally that part of the task, so thank you anyway, I'll have to speak to our tutor about it. It is as follows (i did 3, but 4 is related to this question): (3) The script grants a read privilege to all information included a view DRVADM to user_1. (4) Next, the script grants a read privilege to all information included in a view DRVADM except the rows where a date of birth is not empty to user_2 – Animorphs May 28 '16 at 05:10
  • If you look at the answer I posted to your class mate (the comment under your question) ... I could modify that to hide (blank out) but still show the row. Blanking out at the cell level. All it takes is a little ingenuity to solve your particular problem. Because #3 above inadequately solves the problem for all users. – Drew May 28 '16 at 16:42