This year, digital security has risen as a top concern for many people, especially after the recent security compromises with Cloudflare in February and ransomware in May. Throughout the last two years, we’ve also seen the rise of sim-swapping where a hacker is able to extract a user’s cell phone data and compromise all emails and two-factor authentication. Unfortunately, I expect these security issues to continue and cause compromises for user’s data while negatively impacting companies involved.
For those working on the database side, what lessons can we learn in design to prevent users from loss, while preventing compromises?
Security SQL Server audits
In the below example script, we look at the users on the database and their role with the create and modify date of the users:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
CREATE TABLE ##QuickAudit (ServerName VARCHAR(250),DatabaseName VARCHAR(250), DatabaseUser VARCHAR(250), DatabaseRole VARCHAR(250), CreateDate DATETIME, ModifyDate DATETIME) EXEC sp_msforeachdb ' INSERT INTO ##QuickAudit SELECT @@SERVERNAME AS ServerName , ''?'' AS DatabaseName , t3.name as DatabaseUser , t2.name AS DatabaseRole , t2.create_date AS CreateDate , t2.modify_date AS ModifyDate FROM sys.database_role_members t INNER JOIN sys.database_principals t2 on (t.role_principal_id = t2.principal_id) INNER JOIN sys.database_principals t3 on (t.member_principal_id = t3.principal_id) ' ---- Extract report SELECT * FROM ##QuickAudit DROP TABLE ##QuickAudit |
In the above example, one of the keys we’re tracking is the user, create and modify date, as any new user or changed user may be cause for a concern. We can query this temporary table and monitor the results, raising alerts if we see a cause for a concern. We can also add a filter by recent date either for the create or modify date and have the results emailed.
|
1 2 3 4 5 6 7 8 9 |
SELECT type_desc , create_date , modify_date FROM sys.database_principals WHERE create_date > DATEADD(DD,-1,GETDATE()) OR modify_date > DATEADD(DD,-1,GETDATE()) |
One alternate route to creating permissions here is to script all windows user, SQL user and role creation and create only through a table or file based configuration. This provides a trail for every user that is created where any information read from these queries could be “checked” against the table or file required before creation. This process could follow the model of user submits a request for a role or user creation or modification, the creation or modification is approved, and daily audit checks against this table.
When we think about security design and auditing this information, here’s questions to keep in mind:
- How often should audits of users, roles and permissions be done?
- If an audit passes successfully, should the audit data be saved and archived, or removed after a period of time?
- What teams need to be informed if the audit fails?
- Should audits include other checks, such as users who are currently online?
Federated vs. Decentralized Design
Most companies approach user data storage with a federated data model – they store some of the user’s information while the user also stores their information. This creates two points of failure; the user can lose his information and the company can lose his information. It also creates two points of compromise: both may be targets. Both users and companies hold the user data while the company provides a service that requires a piece of that user data – such as a service which allows a user to pay an electric bill.
An alternative to this data storage approach is a decentralized model where only the user has access to data, while the company exclusively provides a service which only requires a key without further information. An example using an electric bill would be a key (like a meter id) for each user and data showing whether that user paid, without any information retained about the user. In the event of a data compromise with the company, the hacker would only have access to the key – they would have nothing about the user.
In jurisdictions where this architecture is allowed, I suggest considering this model, as it reduces the probability of users being impacted during a data compromise of a company. Currently when a compromise occurs at a company, the company faces damages in multiple fronts and this becomes expensive, while the above model prevents a company from losing key customer data.
Build Architecture for Optional Inconvenience
Some users may prefer inconvenient design, as inconvenient is generally more secure. Consider a person who keeps money in a bank at a remote city without any digital access to the funds and with a requirement of two forms of ID provided to obtain access to the funds. This is incredibly inconvenient, but it’s also incredibly secure.
Some people may prefer to pay for inconvenience because it enhances security. In the case of sim-swapping cell phones, this only occurs because people are able to do this over the phone through social engineering techniques; what would happen if a cell phone company allowed people to pay annually for a feature that made all cell phone changes required in person at a cell phone branch? For these customers, sim-swapping would become impossible.
On the database side, we have a few options here:
- We could use a bit field where 1 translates into a feature being disallowed, such as an operator being unable to switch a cell phone over the phone (on the application side, the information could all be faded out).
- We could store the data in a separate table/database/server. If an operator cannot find the customer over the phone (because some cells wouldn’t allow any remote operator access), it means all changes must occur in person at a physical branch. This helps prevents an update to a table, in the instance that a server or database is hacked.
In both of these examples, our application for phone operators and branches may be different as well, preventing data from entering certain applications.
When we consider our users, let’s remember that some users may prefer less convenience for more security.
Offer Incentives for Enhanced Security
In most cases, a hacker is responding to an economic incentive where the hack yields a return – in most cases, financial. For instance, the recent ransomware wanted payout for access and the cloudbleed issue involved compromising user information, which could have also resulted in financial loss for the users.
What prevents companies from offering bug bounties or security bounties to people who identify weaknesses? If someone spots a security weakness, contacts the company, and the company is able to path the weakness, the nature of providing an incentive for this encourages people to help report problems, rather than use them against companies. I’m surprised how few companies offer these bounties, yet I’ve seen the few who do increase their security, as it offers rewards to people who help strengthen their system.
In addition, a person who continues to spot weaknesses, or helps a company improve should be considered as a candidate that could make a great addition to a company. A person who finds problems on their own time and makes suggestions for improvement is a person that makes a great addition to a strong security team.
Summary
Security will continue to rise as a major concern for both companies and users. Consider some of the following architecture and audit ideas as methods to increase your environment’s security. Depending on how much flexibility you have in your design, you can significantly reduce the negative impact of a compromise on users – even if it does occur.
He has spent a decade working in FinTech, along with a few years in BioTech and Energy Tech.He hosts the West Texas SQL Server Users' Group, as well as teaches courses and writes articles on SQL Server, ETL, and PowerShell.
In his free time, he is a contributor to the decentralized financial industry.
View all posts by Timothy Smith
- Data Masking or Altering Behavioral Information - June 26, 2020
- Security Testing with extreme data volume ranges - June 19, 2020
- SQL Server performance tuning – RESOURCE_SEMAPHORE waits - June 16, 2020