Most of the engineers and database developers often overlooked the importance of database role in database integretiy and security. Often use db_owner during development to make it easy and simple but often carried over in production. This role gives the user full control of the database and can even drop the database.
Follow the steps below to configure database account to have least privilege roles.
- Open SQL Server Management Studio.
- Go to Security -> Logins.
- Right click the user you want to modify and select Properties.
- Select User Mapping.
- Select the database and from the Database role membership list check only the following - db_datareader, db_datawriter, and public.
- Then click OK button.
- The roles we assigned does not allow the user to execute stored procedure so we need to grant EXECUTE command to the user. Calling stored procedure without EXECUTE privilege it will raise "EXECUTE permission denied on object '<stored procedure name>', database 'AdventureWorks', schema 'dbo'.". To grant execute run this script in query window.
GRANT EXECUTE TO [testuser]
- We are not yet finish. We need to ensure that the user has no privilege to view table schemas using system tables and INFORMATION_SCHEMA. This prevents scripts to view object definitions. To deny view run this script in query window.
-- create role
CREATE ROLE [DenySelectToSystemTables]
--deny view schema access to member of DenySelectToSystemTables
DENY VIEW DEFINITION to [DenySelectToSystemTables]
--add user to DenySelectToSystemTables role
sp_addrolemember 'DenySelectToSystemTables', 'testuser'
To test the user login account we configure we need to run series of scripts to AdventureWorks database. Connect to AdventreWorks database using testuser login.
Execute Stored Procedure. User should be able to execute stored procedure.
Run Data Definition Language (DDL) command. User should not allowed to execute DDL (ALTER, DROP, CREATE) commands. You will receive error similar to "Cannot find the object 'ErrorLog' because it does not exist or you do not have permissions."
ALTER TABLE ErrorLog
ADD AddDate smalldatetime NULL
View Table Schema. These query should return empty data because we deny VIEW DEFINITION.
SELECT * FROM information_schema.tables
SELECT * FROM sys.tables
Database Role and Permissions will act as a last line of defence against attacks when all hell breaks loose.
My one cent of advice for SQL Injection, use Stored Procedure!