CODEWITHSUNDEEP
sqlsql-grant
Dangod
Dangod

Reputation: 35

How to use the SQL grant command to restrict access columns and rows in Access database

I am beginner to this subject and self studying. I think this is basic but I could not find it in the ebook I am using. I do not know whether this is relevant to others because when I search this site all the questions are advance to me. So as a beginner, I thought to ask this again because last time my question was discarded as not relevant and not adequate research. I tried my best to find an answer browsing the net but came non.

This is the table INVENTORY (BOOK_CODE, BRANCH_NUM, ON_HAND)

grant update on INVENTORY to Mark and this is the only way it explained in the pdf.

This is the question I want to answer. Users Chambers must be able to change the units on hand for books in branch number 2 but must be unable to access data in any other branch.

This is what I thought. 

GRANT UPDATE ON(ON_HAND WHERE BRANCH_NUM = ‘2’) ON BRANCH TO CHAMBERS

is this correct?

If you think this is not relevant to your community please discard.

Upvotes: 1

Views: 425

Answers (3)

A. Gilfrin
A. Gilfrin

Reputation: 302

As mentioned by Hogan you would need to create a view first and then assign update permissions to the user against that view so...

create view branch2_inventory as select book_code, on_hand from inventory where branch_num = 2;

grant update on branch2_inventory to chambers;

Upvotes: 0

Hogan
Hogan

Reputation: 70538

I'd say the best way to do this (on any RDBMS) is to create a view of the columns you want to grant access to and then give read access to that view.

Upvotes: 3

chrislhardin
chrislhardin

Reputation: 1745

I am not aware of any database engine that allows access to be defined at that level. I suspect there is not one. That type of micrologic logic is normally handled by a front end application that has some security model that is more granular than what the database provides.

Upvotes: 0

Related Questions