Why Schema Is So Important When Securing SQL Servers
Why Good Schema Design Matters In Securing Your SQL Server
SQL Server is an important component that helps manage your crucial and important data about your application or system. It could be user data, application data, and other confidential information and you will want to make sure your server is secured from every end.
Structuring your application database schema is very important as it determines how optimized your data structure is being tabled when inserting and querying data. It adds a level of security to who can access your SQL servers assuming its done right.
A good schema design reduces the complexity of the database, optimized query time of the database, and leave no room for a security breach on your SQL server. A bad schema design can expose your SQL server to vulnerabilities. You can find out more information from this article to see some of the vulnerabilities exposed by un-secure SQL servers.
To understand why Schema is important in adding a layer of security on SQL Servers, we need to established some terms and know the differences between schemas, logins, users, and user roles, understand whats a bad and good schema and practices to fix it.
What is SQL Server?
SQL Server is simply a relational database management system, or RDBMS for short, which is developed, managed and marketed by Microsoft. Similar to other RDBMS software, SQL Server is developed on top of SQL, which is a standard programming language for working with relational databases.
A schema is a defined as collection of database objects which is made up of tables, triggers, views, stored procedures, database indexes, and a lot more. A schema is usually linked together with a username which is referred to as the schema owner.
The schema gives an overview of the data in the database (for instance, of the current tables and their relationships). This makes it easier to track some many details like what data is and not available in the database and what it represents, not only for those that created the database but also for those who are going to use and maintain it later in the future.
It is very important to note that a schema always belongs to one database, whereas, a database might be connected to multiple schemas.
Importance of Schema Design in Securing You SQL Servers
The primary purpose of SQL schema is to help with security management such as defining who can “see and access” what. This was actually made much simpler starting from SQL 2005 when the database schema stopped being directly linked to the owner.
The schema is basically like any other database object, a container for other objects that makes it easier to manage groups of objects in complex databases and this could help in giving roles and permission to any part.
The relationship of schemas with roles and permissions is an important security concept in SQL Server. You can assign permissions on a schema that apply to all objects in the schema. This will help ensure the right people are accessing the right data.
Schema-Based Access Control is a concept used for SQL databases as it is very important for the security of data, and should be simple to implement. Databases must have their own built-in access-control.
SQL schema is to serve as a namespace, this helps in preventing duplicate names across objects that are from different schemas. The original use of this was to allow several users of a given database to create their own data tables or stored procedures, without having to worry about the existence of other objects possibly introduced by other users whose names are similar.
Schema design gives you a high-level view of the structure and relationship of the tables in your database. It helps you understand whether tables have a one-to-one or a one-to-many relationship. It contains the database integrity constraints that need to be maintained by the DBMS. It binds the application developer to the schema design and enforces the rules found in the schema design. Without this constraints and designs, the developer can create rules on the fly and introduce chaos to the system.
When designing a good schema design, it is very important that you watch out while choosing a primary key so as to not mistakenly create hotspots in your database. One of the major causes of hotspots is having a column in your database whose value monotonically increases as the first key part because this would result in all the data inserts happening at the end of your keyspace.
Poor usability of a good schema design could lead to a lot of havocs and opening yourself to attacks. When designing a good schema design, it's very important you put security in mind as this could make developers, DBA and other users do what they are not supposed to and could end up opening a vulnerability. You can read more here: https://www.sisense.com/blog/better-sql-schema/
Best Practices of A Good Schema Design
Do not choose a column whose value monotonically increases or decreases such as the first key part for a table that would have a high write rate.
When you generate unique primary keys that are numerical, the high order bits of subsequent numbers should be distributed roughly equally over the entire number space. One way to do this is to generate sequential numbers by conventional means, then bit-reverse them to obtain the final values.
Reversing the bits maintains unique values across the primary keys. You need to store only the reversed value because you can recalculate the original value in your application code.
The size of a row in the database should be lower than 4 GB to have the best performance. The size of the row includes the top-level row and all of its interleaved child and plus indexed rows.
If you have a table for your history that is keyed by timestamp, consider using descending order for the key column(s)
Make use of an interleaved index on a column that its value monotonically increases or decreases
Similar to the previous primary key anti-pattern, it’s also a bad idea to create non-interleaved indexes on columns whose values are monotonically increasing or decreasing, even if they aren’t primary key columns.
SQL server best configuration practices
When working with the SQL server, whether installing a new SQL or already installed an instance of SQL Server, it always a good idea to ensure the server is up and running with the right configuration and settings for better performance.
Below are best configuration practices to follow:
- Using your SQL Server maximum setting: It always advisable to keep 20% or 20 GB (whichever is lower) memory for the operating system and give the rest of the amount to SQL Server for more memory resources.
- Change the default SQL Server Port: SQL Server uses the default port of 1433 which is widely known and most often DBAs don’t change it. It's a bad practice if you are planning to use the same port and would recommend not to use it.
- Service Accounts: The Service Account by default are NTService and this should be changed to a dedicated SQL Server Domain account which should have the file and system-level access. The Services like SQL Server Instance and SQL Server Agent should be separately handled.
- It’s advisable to have database instances to have separate locations for Data, Log, and Backup files. These default locations on the instance will be used to auto-fill the default location for creating new databases.
- As you have set the default database file locations at the instance level, now you also need to set the default settings for any new database you create.
