July 20, 2006 at 5:11 pm
I NEED TO APPLY A BETTER SECURITY MODEL ON SEVERAL
SQL SERVERS (2000). ACTUALLY ALL LOGINS ARE MEMBERS OF
SYSADMIN SERVER ROLE. BECAUSE OF THIS NO ONE HAS AN SPECIFIC DATABASE ACCES AND A DATABASE ROLE ASIGNED.
I THINK THAT THE BEST WAY TO FIX IT IS TO CREATE A COUPLE OF DATABASE ROLES WITH SPECIFIC ACCESS FOR TABLES, COLUMNS AND TO GRANT PERMISSIONS TO EXECUTE
INSERTS, UPDATES, DELETE AND OTHERS.
OTHER REQUIREMENT IS THAT ONLY A FEW USERS ARE GOING TO EXECUTE UPDATES ON OLD DATABASES, OTHERS ONLY
COULD READ OR SELECT DATA.
IF I'M UNSERSTANDING SECURITY MODEL ON SQL SERVER I NEED TO CREATE DIFFERENT ROLES ON EVERY SINGLE DATABASE, SPECIFY ACCESS FOR DATABASES, SPECIFY DATABASE ROLES
(PUBLIC, DB_OWNER, DB_DATAREADE, DB_DENYDATAREADER) FOR ROLES CREATED.
I NEED SOME ADVICE TRYING TO CREATE THE BEST WAY TO IMPLEMENT THIS NEW SECURITY MODEL.
July 21, 2006 at 10:01 am
Your understanding is basically correct. You can create a role in each database with the specific permissions required, then assign users to those roles. If there are no sensitive columns or tables, you should use the system security groups.
Another option is create the roles, but work with your Active Directory guys to create NT Groups to move individual logins in and out. Then assign the NT groups to the database roles. This means less administration for you because you're not dealing with individual logins.
I would avoid granting db_owner access to anyone but DBA's because that will allow people to create, change or delete database objects and create all kinds of havoc.
Last bit of advice: do the initial setup using Enterprise Manager. Once you're happy with the permissions setup for the groups, script everything and archive it somewhere safe. That way you can get the ease-of-use that the GUI provides yet still be able to perform mass-updates should they be required. This becomes particularly useful if you need to tweak permissions for a dev or test environment.
Hope this helps.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply