Database security should always be at the forefront of you mind. Having collected all this data you want to know it is safe, and the people you have data on want to be reassured it is secure.
One of the biggest steps in security is managing the user accounts that have access to your database. Within Microsoft SQL DB Server the main two types of logons are:
SQL Accounts
Windows
SQL accounts are created and managed within MS SQL DB, they are unique to the application and not used for accessing other products. Whereas Windows authentication uses your normal Windows logon account for DB access. It is possible to have a hybrid of SQL and Windows.
Login & User Accounts
An important concept to grasp is the difference between login and user accounts. Your login gets you access to the database server, but your user account gives you permission to access the databases stored on that servers.
If you think of your server as a hotel, then once you have a login you have access to all the shared areas within the hotel, reception, restaurant, etc., but you do not have access to the rooms. The rooms contain the databases and you are granted access on a per room basis. If you have access to the database Sales, that does not mean you have access to the HR database.
SA Account - System Administrator
By default, MS SQL DB Server creates an account called SA, this is the god account and you should be careful when using it. It is also a wise decision to change the account name to make hacking your system harder.
I have talked with other professionals who have arrived onsite to find organisations only using the SA account. Everyone from support to developments were using the SA account and the password was scattered through unencrypted config files to allow services to run from the account. This is not a good idea. Guard your SA account carefully and create separate accounts for your staff and your services.
The Principle of Least Privilege (PoLP)
Whenever you are creating accounts use the principle of least privilege (PoLP). This means you only allocate the account the access it needs to do the job. If you have first-line support staff they do not need SA access. If a service only reads data than allocate it read-only access.
Database Roles
Once an account has access it needs the ability to do things. MS SQL DB comes with a number of fixed roles which helps you get started. You can create your own roles, too, but these are a good starting point. In general, you should start by giving accounts the db_datareader role, then they can run Select SQL Queries. The more responsible staff can then be added to further roles, such as db_datawriter and db_ddladmin. The most access is given to db_owner.
The table below lists the fixed roles as described by Microsoft:
Fixed Database Role Name | Description |
|---|---|
db_owner | Members of the db_owner fixed database role can perform all configuration and maintenance activities on the database, and can also DROP the database in SQL Server. (In SQL Database and Azure Synapse, some maintenance activities require server-level permissions and can't be performed by db_owners.) |
db_securityadmin | Members of the db_securityadmin fixed database role can modify role membership for custom roles only and manage permissions. Members of this role can potentially elevate their privileges and their actions should be monitored. |
db_accessadmin | Members of the db_accessadmin fixed database role can add or remove access to the database for Windows logins, Windows groups, and SQL Server logins. |
db_backupoperator | Members of the db_backupoperator fixed database role can back up the database. |
db_ddladmin | Members of the db_ddladmin fixed database role can run any Data Definition Language (DDL) command in a database. Members of this role can potentially elevate their privileges by manipulating code that might get executed under high privileges and their actions should be monitored. |
db_datawriter | Members of the db_datawriter fixed database role can add, delete, or change data in all user tables. In most use cases, this role is combined with db_datareader membership to allow reading the data that is to be modified. |
db_datareader | Members of the db_datareader fixed database role can read all data from all user tables and views. User objects can exist in any schema except sys and INFORMATION_SCHEMA. |
db_denydatawriter | Members of the db_denydatawriter fixed database role can't add, modify, or delete any data in the user tables within a database. |
db_denydatareader | Members of the db_denydatareader fixed database role can't read any data from the user tables and views within a database. |
This has been a brief overview to accounts within a Microsoft SQL DB Server. I'm sure you have more questions, so please ask them in the comments below. At DB Backbone we are here to answer your questions.
