Read Access to Database Tables - How do you do it in MS-SQL ?

Question

Read Access to Database Tables - How do you do it in MS-SQL ?

szejiekoh

SSCarpal Tunnel

Points: 4155
More actions
July 30, 2017 at 9:57 am

#343782

Hi all,
I need to control access to the developers&business users whom (by business rules) are allow to look into certain tables containing actual in the Production database. I have been doing this (below) in Oracle and I am not sure if there is any difference to how it is done in MS-SQL. Hope gurus here can shed some light on the above.
In Oracle
In actual schema( containing production data) grant select on tab1,tab2,tab3 to readschema with grant option;
In readschema create view tab1 as select * from tab1 where condition; create view tab2 as select * from tab1 where condition; create view tab3 as select * from tab1 where condition;
grant select on tab1,tab2,tab3 to developerA, developerB; grant select on tab1 to developerC;
What is the recommended way to control read access in the SQL-Server ?
If I have 100 business users that need to read the views, I would have twice the amount, 100 logins + 100 username in the database ?
Best Regards,
Noob

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

pietlinden SSC Guru Points: 63509 More actions · Answer 1

Are the users in a Group in Active Directory? Are the objects they need permissions to in the same schema by themselves?

szejiekoh SSCarpal Tunnel Points: 4155 More actions · Answer 2

Hi Pietlinden,

Thank you for your reply.

Yes. The users are in AD.
The objects they need permission to are residing in the same schema and they will need to access several schema objects across different databases.

Regards,
Noob

Sue_H SSC Guru Points: 90860 More actions · Answer 3

szejiekoh - Sunday, July 30, 2017 8:42 PM
Hi Pietlinden,
Thank you for your reply.
Yes. The users are in AD.
The objects they need permission to are residing in the same schema and they will need to access several schema objects across different databases.
Regards,
Noob

But the question was if the users are in a group in Active Directory as this is generally the easier way to manage permissions. You can create roles in the databases for the permissions and add the Windows group or groups to the roles.
Using group, roles, etc is less time consuming than trying to manage 100 different logins/users.

Sue

mikeaspnet SSC Veteran Points: 218 More actions · Answer 4

Slightly diffrent question, can this also be achieved with the connection string?
if it is possible, how?
and would i be better for performance on a website, have connection string only read, and other read, write?

thanks

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 5

No, it can't be done with the connection string. Even if it could, you wouldn't want to, since any user with a little know-how can write his or her own connection string. Put all users you want to have the same permissions in a Windows group or a database role, then assign permissions at object or schema level to that group or role.

John

mikeaspnet SSC Veteran Points: 218 More actions · Answer 6

mmmm im talking about a public website, so there no users groups.
can't i create user with read permissions and use that as connection string?
then use that connection string just for the pages that display data and use the other to do data manipulations

but would that improve performance of the website?

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 7

But you said you had 100 users? And you mentioned developers - surely they don't access the database through a website? Never mind - create the login, create a database user based on that login, give the the database permissions it needs, and use the login in your connection string. Not sure where performance comes into it. You might need to provide a bit more information.

John

Lowell SSC Guru Points: 323518 More actions · Answer 8

a public site means you do not know or care who is connecting. you are presenting a limited set of data to the end users.

so the web page, when connecting the database on the server side of the scripting, would need to use or create a connection string, with a valid user to get to the resources.
so that web page would typically have a user ie [WebUser], and that user is limited to a handful of relevant tables, so it follows the least-access principle.
if you [WebUser] currently has access to a whole database, that's possible, but probably lazy permissions

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

mikeaspnet SSC Veteran Points: 218 More actions · Answer 9

now i only got 1 connections string, for the website application and that is the SA !
Which probably very stupid, thinking about it 😉
Would user with less permissions, have better performance? because the server know that only a limited amount of possibilities are possible?

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 10

No, I don't think so. As far as I know, the query optimizer doesn't take permissions into account when compiling an execution plan. And if you think about it, that makes sense - the plan is available to all users, not just the user who caused it to be compiled.

John

Eric M Russell SSC Guru Points: 125562 More actions · Answer 11

I'm not understanding the use of views for this. There is also no need to add logins for each individual user account, just add login and user access for the AD group containing business users who need read access. Then GRANT SELECT permission on appropriate tables to the AD group.

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho