Sql-server – Adding Security to a legacy DB with Schema

database-designSecuritysql-server-2005

I am working on a DB where almost every technical user has full read / write permissions on all tables. We are working towards removing permissions, gradually, from certain users, and I had hoped that implementation of some new code on a new schema would be able to be strictly implemented from day 1.

However, I have realised today that most of our users have been granted db_datareader and db_datawriter role membership, which means that they all have access to this new schema by default. Therefore, I want to find a way forward that gets this schema secure without creating a load of additional work.

My idea is:

Create a new 'generalReader' role which has SELECT privilege on our huge dbo schema, and a couple of other schema we have (but are not being changed right now);
Create a new 'generalWriter' role with UPDATE, INSERT, DELETE privileges on the dbo and existing schema;
Drop the db_* roles from users, and replace them with the new generalReader and generalWriter roles;
Add new roles to control access to the new schema – I think most people could be allowed to SELECT from it, where only a few should be granted INSERT privileges. (e.g. I would create an LoanAccountReader and LoanAccountInserter roles)

Does this sound like a broadly appropriate plan to move forward – or am I missing some tricks or some security issues?

Before coming up with the above, I did some experimentation with roles and could create roles DENYing write access (that overrides the database writer role), but could not then re-enable that permission with a 'writer' role.

Best Answer

This sounds like a reasonable plan
DENY is tricky to get right and may not work anyway See:

Just in case you missed it, You can GRANT .. SCHEMA so you don't have to GRANT per object:

GRANT SELECT ON SCHEMA::dbo TO RoleGeneralReader

Related Solutions

Sql-server – The SELECT permission was denied on the (View) object after explicit grant select on view to user

Your response pointed me in the right direction.

After looking at the object and verifying the permissions many times, I shifted my focus to the Login and User.

Apparently the login is mapped to a different user. So when I checked the server_principal against the database_principal there was a mismatch.

The login is called appuser and i am also trying to grant permissions inside the database to appuser. Unfortunately someone created a different database user which is actually called appwebuser.

So once i discovered that we were granting permissions to the wrong user and update the code to the correct database user the problem was resolved.

I used the following query to track down the login. I add in the servername and dbname so i can check against my different environments and report to the customer.

SELECT 
@@servername  as [server_name]
,db_name()  as [database_name]
,sp.name as [server_principal_name]
,sp.type as [server_principal_type]
,sp.type_desc as [server_principal_type_desc]
,sp.create_date as [server_principal_create_date]
,sp.default_database_name as [server_principal_default_database_name]
,dp.name as [database_principal_name]
,dp.type as [database_principal_type]
,dp.type_desc as [database_principal_type_desc] 
,dp.default_schema_name as [database_principal_default_schema_name]
FROM 
    sys.database_principals  dp INNER JOIN
    sys.server_principals sp ON (dp.sid = sp.sid)
WHERE 
    sp.name like 'appuser'
    or dp.name like  'appuser'
    or sp.name like 'appwebuser'
    or dp.name like  'appwebuser'

What privileges does the SQL Azure Migration Wizard need to copy a SQL Azure database

From Copying Databases in SQL Azure

Permissions

In order to copy a database in SQL Azure, your login requires the following permissions:

On both servers: The login must have the same login name and password on both SQL Azure servers.

On the destination server: The login must be a member of the server-level dbmanager role. Note: the server-level principal of your SQL Azure server is not a member of the dbmanager role, but automatically has the same permissions. For more information about managing logins in SQL Azure, see Managing Databases and Logins in SQL Azure.

On the source server: The login must be the DBO of the source database. Only the login that created the source database, the DBO, can copy that database to another database on the same server or on a different server.

Related Question