Getting Started with Adobe After Effects - Part 6: Motion Blur

Upload Image Close it
Select File

All About Database Recovery Blog about database recovery software, database corruption error solutions for SQL, Access, MySQL, Oracle, DB2 etc...
Browse by Tags · View All
MySQL Recovery 2
restore sql database 2
sql database backup 2
sql database corruption 2
MySQL table is marked as crashed 1
Clustered index Error in SQL Server 1
fix index error 1
access database repair 1
aoindex error 1
repair corrupt MySQL database 1

Archive · View All
August 2012 3
March 2013 2
December 2012 2
November 2012 2
September 2012 2
June 2012 2
May 2016 1
January 2016 1
August 2015 1
February 2015 1

Strengthening Data Security through MS SQL Server Security Architecture

Apr 30 2013 12:00AM by adamgorge   

Data security is the cornerstone of every business application. There is no arguing to the fact that confidential information needs to be protected by overlapping layers of security. Another aspect of security is data integrity. It is incumbent on every organization to periodically verify if the legal requirements of data security and integrity are being adhered to, i.e. the data is not inadvertently modified, correct values of data are stored, etc.

The database applications that primarily use SQL Server can take advantage of the security features built into the platform. Every new version of SQL Server builds on its existing capabilities and the security features have also seen some radical changes. However, a database application cannot solely depend on these security features as other factors need to be considered when planning for data security, such as application requirements, execution environment, physical location, and more. The best time in the development cycle to decide these requirements is the design phase. It empowers you to prepare for all the security threats and vulnerabilities that may be confronted in the foreseeable future.

Every SQL Server database comprises a list of securable objects This SQL Server securable has permissions defined for any individual, group, or process that needs access to SQL Server. Two primary concepts are associated with the SQL Server security framework, i.e. 'authentication' and 'authorization'. Authentication refers to the process of identifying a user's identity or validating a process. On the contrary, authorization is the process of identifying the access rights of a principal (user, group, or process) on various securable resources and determining the operations that can be performed for those resources by the principal.

SQL Server Authentication

There are two types of authentication modes in SQL Server, i.e. Windows Authentication and Mixed mode authentication:

Windows Authentication

Windows authentication in SQL Server is known as integrated security that allows Windows user and group accounts to securely log on to SQL Server without having to provide additional credentials. Generally, the following connection string would suffice to log on to SQL Server using Windows authentication:

"Server=MSSQL1;Database=AdventureWorks;Integrated Security=true;

Mixed Mode Authentication

Mixed mode authentication in SQL Server implies a user is authenticated by both Windows and SQL Server. This type of authentication requires you to create SQL Server logins and the user needs to provide his login credentials at run time. SQL Server logins can be created by using the Windows password policy mechanisms (if SQL Server is installed on Windows Server 2003 and later).

Server and Database Roles in SQL Server

SQL Server incorporates database and server roles to grant permissions to a specific group of users rather than individual users.

Fixed Server Roles

Fixed server roles are largely responsible for administering SQL Server. They are granted specific permissions that cannot be changed. These server roles can be granted access to the server without having any user account in a database. You should diligently create members of this role as these roles have unlimited scope.

Fixed Database Roles

Fixed database roles have some associated set of permissions. These roles help you manage groups of permissions.

You need to map logins to database user accounts in order to allow users work with database objects. Once this is done, users are added to the database roles for assigning them specific sets of permissions associated with these roles.

Ownership and User-Schema Separation

SQL Server security is primarily based on ownerships. If you are an object owner, then your permissions are assigned irrevocably, i.e. they cannot be changed. Further, an object owner cannot be dropped from a database.

User-Schema Separation

A user-schema is considered as a unique namespace for grouping database objects. This provides additional level of flexibility and makes managing database permissions much easier. When referring to objects, you include the schema name as follows:


Schema Owners and Permissions

You can make multiple database principals as the owners of a schema. If you set certain rules and permissions for a schema, they are automatically set for all objects within the schema. You can also assign the same schema to multiple database users.

All objects that are added to a schema are owned by the owner of that schema. The objects belonging to a schema may have associated granular permissions. However, this introduces complexity in the SQL Server security architecture.

Authorization and Permissions in SQL Server

SQL Server has securable objects and each securable object has permissions that are assigned to a database principal for allowing access to this object.

The Principle of Least Privilege

In order to drive away all data security threats, applications are created that can be accessed only with a least-privileged user account. When a user logs on using this account, he has limited access rights. The administrative tasks are divided into several fixed server roles and the sysadmin fixed server role has limited scope.

This principle should be followed while assigning permissions to database users and roles.

Role-Based Permissions

It is easier to implement a role-based security model rather than creating permissions sets for individual users. You can use fixed database users and add users to them to further simplify security administration. Roles are often nested; however, this nesting may impact performance dramatically.

Data Encryption in SQL Server

SQL Server facilitates data encryption using various built-in functions. It features an internal certificate store that manages various certificates and keys used for data encryption. These certificates and keys are stored using a secret mechanism in which the layer above acts as a guard for the certificates and key at one level. Symmetric key encryption is ideal when dealing with large volumes of data.

Keys and Algorithms

You can use any of these algorithms in SQL Server to implement symmetric key encryption: DES, Triple DES, RC2, RC4, 128-bit RC4, DESX, 128-bit AES, 192-bit AES, and 256-bit AES. These algorithms employ Windows Crypto API.

A proper implementation of all the above techniques will suffice to counter most of the security threats in your SQL Server environment.

Tags:  SQL Server Data Security

147 · 1% · 340


Your Comment

Sign Up or Login to post a comment.

"Strengthening Data Security through MS SQL Server Security Architecture" rated 5 out of 5 by 1 readers
Strengthening Data Security through MS SQL Server Security Architecture , 5.0 out of 5 based on 1 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]