Home
Do you configure your web applications to use application specific accounts for database access?
  v4.0 Posted at 19/02/2015 2:21 PM by Michael Demarco

​​Do you configure your web applications to use application specific accounts for database access? 

An application's database access profile should be as restricted as possible, so that in the case that it is compromised, the damage will be limited. 

Application database access should be also be restricted to only the application's database, and none of the other databases on the server

administratorlogininitsconnectionstring.png 

Bad Example – Contract Manager Web Application using the administrator login in its connection string 

 

databaseuserconfiguredintheconnectionstring.png 

 

Good Example – Application specific database user configured in the connection string

Most web applications need full read and write access to one database.  In the case of EF Code first migrations, they might also need DDL admin rights.  These roles are built in database roles:

 db_ddladminMembers of the db_ddladmin fixed database role can run any Data Definition Language (DDL) command in a database.
 db_datawriterMembers of the db_datawriter fixed database role can add, delete, or change data in all user tables.
 db_datareaderMembers of the db_datareader fixed database role can read all data from all user tables.

Table: Database roles taken from Database-Level Roles

If you are running a web application on Azure as you should configure you application to use its own specific account that has some restrictions.  The following script demonstrates setting up an sql user for myappstaging and another for myappproduction that also use EF code first migrations:

USE master

GO

CREATE LOGIN myappstaging WITH PASSWORD = '************';

GO

CREATE USER myappstaging FROM LOGIN myappstaging;

GO

USE myapp-staging-db;

GO

CREATE USER myappstaging FROM LOGIN myappstaging;

GO

EXEC sp_addrolemember 'db_datareader', myappstaging;

EXEC sp_addrolemember 'db_datawriter', myappstaging;

EXEC sp_addrolemember 'db_ddladmin', myappstaging;

Script: Example script to create a service user for myappstaging

Note: If you are using stored procedures, you will also need to grant execute permissions to the user.  E.g.:

GRANT EXECUTE TO myappstaging

Data Source=tcp:xyzsqlserver.database.windows.net,1433; Initial Catalog=myapp-staging-db; User ID=myappstaging@xyzsqlserver; Password='*************' 

Figure: Example connection string

Related rules

    Do you feel this rule needs an update?

    If you want to be notified when this rule is updated, please enter your email address:

    Comments: