0

I have a table '[Employee]' , and I want to allow access to certain people only through [View] , using a grant.I am trying first to do it with a single user [User] and then do it for a collection of users through a role. I know I can use :

GRANT SELECT ON OBJECT : : [VIEW] TO [User] ;

And I can also create a role [Role] , and use:

GRANT SELECT ON OBJECT : : [VIEW] TO [Role]

My question is whether this permission somehow implicitly excludes all others, i.e., do the two grants above prevent any of the [Users][Roles] from having any other type of access to '[Employee]' ( Read, Select, Update ), or do I have to expressly prohibit said users, roles from having additional access?

I assume this has to see with membership of [User] and the people in [Role] in other logins or existing user types which may have been granted said permissions either explicitly or as part of role membership? Thanks.

2

Unless you grant SELECT to 'public' role, you must explicitly grant access to each user, group or database role.

For simplicity, you can create a new database role, (i.e. my_users), then grant select to this role and finally add new users to the role.

  • Thanks @McNets, I just want to make sure that no other type of permission is granted to [user]. May I just assign [user] to 'public' and only Grant 'Select' to 'public'. Would this guarantee that my user can only select? Sorry if I am confused, I just want to see what I need to do so that [user] can only use 'select'. – MSIS May 23 '17 at 21:34
  • It is not recommended, create a new database role (i.e. company_users), grant access to it, and then add users to this role. – McNets May 23 '17 at 21:37

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.