Home Workload Solutions SQL Server Blogs

Database security methodologies of SQL Server

Mon, 03 Aug 2020 16:06:37 -0000

Read Time: 0 minutes

Anil Papisetty

In general, security touches every aspect and activity of an information system. The subject of security is vast, and we need to understand that security can never be perfect. Every organization has unique way of dealing with security based on their requirements. In this blog, I describe database security models and briefly review SQL Server security principles.

A few definitions:

Database: A collection of information stored in computer
Security: Freedom from danger
Database security: The mechanism that protects the database against intentional or accidental threats or that protects it against malicious attempts to steal (view) or modify data

Database security models

Today’s organizations rely on database systems as the key data management technology for a large variety of tasks ranging from regular business operations to critical decision making. The information in the databases is used, shared, and accessed by various users. It needs to be protected and managed because any changes to the database can affect it or other databases.

The main role of a security system is to preserve integrity of an operational system by enforcing a security policy that is defined by a security model. These security models are the basic theoretical tools to start with when developing a security system.

Database security models include the following elements:

Subject: Individual who performs some activity on the database
Object: Database unit that requires authorization in order to manipulate
Access mode/action: Any activity that might be performed on an object by a subject
Authorization: Specification of access modes for each subject on each object
Administrative rights: Who has rights in system administration and what responsibilities administrators have
Policies: Enterprise-wide accepted security rules
Constraint: A more specific rule regarding an aspect of an object and action

Database security approaches

A typical DBMS supports basic approaches of data security—discretionary control, mandatory control, and role-based access control.

Discretionary control: A given user typically has different access rights, also known as privileges, for different objects. For discretionary access control, we need a language to support the definition of rights—for example, SQL.

Mandatory control: Each data object is labeled with a certain classification level, and a given object can be accessed only by a user with a sufficient clearance level. Mandatory access control is applicable to the databases in which data has a rather static or rigid classification structure—for example, military or government environments.

In both discretionary and mandatory control cases, the unit of data and the data object to be protected can range from the entire database to a single, specific tuple.

Role-based access control (RBAC): Permissions are associated with roles, and users are made members of appropriate roles. However, a role brings together a set of users on one side and a set of permissions on the other, whereas user groups are typically defined as a set of users only.

Role-based security provides the flexibility to define permissions at a high level of granularity in Microsoft SQL, thus greatly reducing the attack surface area of the database system.

RBAC mechanisms are a flexible alternative to mandatory access control (MAC) and discretionary access control (DAC).

RBAC terminology:

Objects: Any system, resource file, printer, terminal, database record, etc.
Operations: An executable image of a program, which upon invocation performs some function for the user.
Permissions: An approval to perform an operation on one or more RBAC-protected objects
Role: A job function within the context of an organization with some associated semantics regarding the authority and responsibility conferred on the user assigned to the role.

For more information, see Database Security Models — A Case Study.

Note: Access control mechanisms regulate who can access which data. The need for such mechanisms can be concluded from the variety of actors that work with a database system—for example, DBA, application admin and programmer, and users. Based on actor characteristics, access control mechanisms can be divided into three categories – DAC, RBAC, and MAC.

Principles of SQL Server security

A SQL Server instance contains a hierarchical collection of entities, starting with the server. Each server contains multiple databases, and each database contains a collection of securable objects. Every SQL Server securable has associated permissions that can be granted to a principal, which is an individual, group, or process granted access to SQL Server.

For each security principal, you can grant rights that allow that principal to access or modify a set of the securables, which are the objects that make up the database and server environment. They can include anything from functions to database users to endpoints. SQL Server scopes the objects hierarchically at the server, database, and schema levels:

Server-level securables include databases as well as objects such as logins, server roles, and availability groups.
Database-level securables include schemas as well as objects such as database users, database roles, and full-text catalogs.
Schema-level securables include objects such as tables, views, functions, and stored procedures.

SQL Server authentication approaches include:

Authentication: Authentication is the SQL Server login process by which a principal requests access by submitting credentials that the server evaluates. Authentication establishes the identity of the user or process being authenticated. SQL Server authentication helps ensure that only authorized users with valid credentials can access the database server. SQL Server supports two authentication modes, Windows authentication mode and mixed mode.

Windows authentication is often referred to as integrated security because this SQL Server security model is tightly integrated with Windows.
Mixed mode supports authentication both by Windows and by SQL Server, using usernames and passwords.

Authorization: Authorization is the process of determining which securable resources a principal can access and which operations are allowed for those resources. Microsoft SQL -based technologies support this principle by providing mechanisms to define granular object-level permissions and simplify the process by implementing role-based security. Granting permissions to roles rather than users simplifies security administration.

It is a best practice to use server-level roles for managing server-level access and security, and database roles for managing database-level access.
Role-based security provides the flexibility to define permissions at a high level of granularity in Microsoft SQL, thus greatly reducing the attack surface area of the database system.

Here are a few additional recommended best practices for SQL Server authentication:

Use Windows authentication.

Enables centralized management of SQL Server principals via Active Directory
Uses Kerberos security protocol to authenticate users
Supports integrated password policy enforcement including complexity validation for strong passwords, password expiration, and account lockout

Use separate accounts to authenticate users and applications.

Enables limiting the permissions granted to users and applications
Reduces the risks of malicious activity such as SQL injection attacks

Use contained database users.

Isolates the user or application account to a single database
Improves performance, as contained database users authenticate directly to the database without an extra network hop to the master database
Supports both SQL Server and Azure SQL Database, as well as Azure SQL Data Warehouse

Conclusion

Database security is an important goal of any data management system. Each organization should have a data security policy, which is set of high-level guidelines determined by:

User requirements
Environmental aspects
Internal regulations
Government laws

Database security is based on three important constructs—confidentiality, integrity, and availability. The goal of database security is to protect your critical and confidential data from unauthorized access.

References

https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/overview-of-sql-server-security

https://sqlsunday.com/2014/07/20/the-sql-server-security-model-part-1/

https://www.red-gate.com/simple-talk/sysadmin/data-protection-and-privacy/introduction-to-sql-server-security-part-1/

Tags:

Controller	The controller provides management and security for the cluster. It contains the control service, the configuration store, and other cluster-level services such as Kibana, Grafana, and Elastic Search.
Compute pool	The compute pool provides computational resources to the cluster. It contains nodes running SQL Server on Linux pods. The pods in the compute pool are divided into SQL compute instances for specific processing tasks.
Data pool	The data pool is used for data persistence and caching. The data pool consists of one or more pods running SQL Server on Linux. It is used to ingest data from SQL queries or Spark jobs. SQL Server Big Data Cluster data marts are persisted in the data pool.
Storage pool	The storage pool consists of storage pool pods comprising SQL Server on Linux, Spark, and HDFS. All the storage nodes in a SQL Server Big Data Cluster are members of an HDFS cluster.

	The impact of the Vs on analytics	How a Big Data Cluster helps
Volume	The greater the volume of data processed by a machine learning algorithm, the more accurate the predictions will be.	Increases the data volume available for AI by capturing data in scalable, inexpensive big data storage in HDFS and by integrating data from multiple sources using PolyBase connectors.
Variety	The greater the variety of different sources of data, the more accurate the predictions will be.	Increases the number of varieties of data available for AI by integrating multiple data sources through the PolyBase connectors.
Velocity	Real-time predictions depend on up to-date data flowing quickly through the data processing pipelines.	Increases the velocity of data to enable AI by using elastic compute and caching to speed up queries.
Veracity	Accurate machine learning depends on the quality of the data going into the model training.	Increases the veracity of data available for AI by sharing data without copying or moving data, which introduces data latency and data quality issues. SQL Server and Spark can both read and write into the same data files in HDFS.

Your Browser is Out of Date

Database security methodologies of SQL Server

Database security models

Database security approaches

Principles of SQL Server security

Conclusion

References

Related Blog Posts

Manage and analyze humongous amounts of data with SQL Server 2019 Big Data Cluster

Big data analysis

Cluster management

Conclusion

How IT organizations benefit from the Dell EMC Live Optics monitoring tool

Traditional Monitoring Challenges

Modern Monitoring Tool Benefits

Dell EMC Live Optics