August 5, 2011 at 5:30 pm
Hi
I am having the following situation and not quite sure how to get on with it, would be grateful for any assistance provided.
DB details are:
SQL 2005 SP4
a DB migrated from SQL 2000
contains all the objects in one schema, which is DBO
Front end is ASP Web application
currently used by admin staff in the organization to record infomation about the non admin staff who belongs to the organization.
I need to extend this system so that non admin staff can access this DB and view certain information recorded by the admin staff but they should not alter those info. Also they should be able to record other info about them in the DB in other tables.
I am planning the following to achieve this:
create a new schema in which create all the non admin user's tables
create a login with only db reader role - which will be used in my web application to log into the DB when non admin users accessing the system
create a user in the DB and assign above login to it and make this user as owner of the above schema
By this I am hoping the new user can write any tables in the new schema, and read (but not write) all the objects in the dbo schema
Please let me know whether this is the right way to go, if not please let me know how to do it?
Thanks.
August 10, 2011 at 2:10 pm
Giving any user dbadmin rights is not the best way to go about it. Your schema idea is the first step forward. Give the non admin user db_datareader in the Admin schema and db_datawriter for the non admin schema. or you can give rights to just certain views if you want more granularity.
-Roy
August 10, 2011 at 2:41 pm
Also you could create to roles. One for Admins and the other for the non admins. Then it makes giving granular permission easier.
-Roy
August 10, 2011 at 3:19 pm
Users do not need to own anything, objects or schema, to access them.
Use a roles, grant the appropriate rights to the role for the object(s). In your case it looks like you would use two roles, each with different permissions to different objects.
August 10, 2011 at 5:13 pm
Thanks a lot for the advice, as you said I should go
with the roles, however just wondering whether it is a good
Idea to keep the objects in different schema so that permissions can
Delegated collectively?
August 10, 2011 at 6:11 pm
schema has nothing to do necessarily with permissions. Roles can do that. Schemas are for object organization. You can use them for security, but there's no need to do so.
August 31, 2011 at 7:12 am
Hi All,
I thank you all for your inputs, been on holiday and unable to get on the net so couldn’t follow the topic, but please help me to conclude this topic.
According to your advice, I am thinking to create a role and assign write, delete and update permissions to the new tables and stored procedures that I am creating to the non admin users (drop the idea of creating new Schema).
Admin users will get db_reader, and db_writer role to the database
Non admin users get the db_reader and the new role that I created.
In this way I am hoping that non admin users will only able to write/modify the tables assigned to the new role, however admin users will be able to write/modify all the tables in the DB.
Is this the right approach?
Thanks in advance.
August 31, 2011 at 7:56 am
It is a start. What I see are complications that can arise at a later point. What happens when a new table is added to the DB that has very sensitive information that you do not want even the admins to write? What if the table cannot be read by non admins? By giving a role like data reader and data writer might be a bad idea at that time.
What I would suggest would be to create two roles. One for the admins and one for non admins. Put the admins in the role created for them and the non admins to the role created for the non admins. Grant specific permissions for each table according to the requirements for these roles. In this case, you can manage security for the new tables that could be created in the future.
One question that I have is what is the access method to the DB for the Admins and Non Admins? (SSMS or Application?)
Just my 2 cents
-Roy
August 31, 2011 at 8:22 am
I agree with Roy that I might not use db_datareader/writer. If you have exceptions, you get into DENYs in roles and that gets confusing.
Create your own two role and assign permissions. It's not that time consuming and gives you a good layer of abstraction.
August 31, 2011 at 2:37 pm
Firstly, the access is through for both non admin and admin users through a web application.
I see the problems that Roy points out, something that I never thought about it, thank you Roy for this valuable input; it would be ideal to have separtae roles, but my concern is that because admin users already use loads of tables, views, SP etc (application already exists and admin users already have reader/writer roles assigned to them), then I have to assign permissions individually for all these entities - this may be very time intensive procedure.
Please let me know when I create roles, what user should I use to create them (who should be the owner?). Any syntax help would be appreciated.
I sincerely thank you both for your feedbacks.
August 31, 2011 at 5:49 pm
Assigning permissions to a role is a one time thing. It might seem intensive, but it's not much work and it can easily be scripted. There are scripts on this site that can help.
It doesn't matter who owns or creates the role.
September 1, 2011 at 2:21 am
Thank you both for all your inputs on this matter,
its been really helpful.
September 1, 2011 at 5:34 am
If the database is accessed from a web application, I would handle all of the appropriate access through the application itself.
In other words, I would have a table of users, a table of permissions, and a third table that combines a user and his/her permissions. Then, leave it up to the middle tier/front end to determine what each user is able to access within the site, and what he/she is allowed to see/add/update/remove.
Then you can create one or two generic SQL logins to do whatever access they need.
This method is how I've done it in the past, and gives you a fair amount of flexibility with setting various levels of permissions, without having to deal with much in database itself.
September 1, 2011 at 6:54 am
If you have control over the front end, why would you even bother giving permission to individual table? You could make all access go through Stored procs and give rights to the SPs based on the business requirements.
-Roy
September 1, 2011 at 6:58 am
the web application contains fair amount of controls as you suggested, but I guess having a control on the DB gives additional security, for example, in the case of someone hacking the application.
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply