Building a Secure Access & SQL Server Solution with Anders Ebro
Anders Ebro, a Principal consultant with Exacto A/S, shares insights on building a secure access and SQL server solution. With a background in SQL development and experience in managing sensitive data, Anders emphasizes the importance of security aspects such as limiting access to sensitive data, role-based data updating, and account hacking prevention. The article also delves into security components at the server and database levels, showcasing practical examples and low-budget security solutions.
Download Presentation
Please find below an Image/Link to download the presentation.
The content on the website is provided AS IS for your information and personal use only. It may not be sold, licensed, or shared on other websites without obtaining consent from the author. Download presentation by click this link. If you encounter any issues during the download, it is possible that the publisher has removed the file from their server.
E N D
Presentation Transcript
Anders Ebro Building a Secure Access & SQL Server Solution
Before we get started How many of you use SQL server every day?
Background - Anders Ebro Principal consultant with Exacto A/S for 3 years Based out of Denmark, with 18 employees, recently opened two branches in Germany Full time Access/SQL server developer for 11 years, 7 of those using SQL server Last few years expanded with Excel/SQL interfaces Access/Excel/SQL based Incentives tool to handle performance review (and bonus payout) at a financial institution 25.000 employees 800 users (Managers) 100 local admin (Local HR) 15 super admin (Rewards Team and Group HR) XXX million worth of bonus assigned Sensitive data (salary)
Security covers many aspects Ensure that access to our sensitive data is limited Ensure that users can only update data according to their role (user / manager / admin) Limit what can happen in case of an account being hacked (don t give every users more rights than they need to do their job)
Application Security versus SQL server security DEMO! What makes Access different? Direct user access This is not just remote controlling Access. A user might open up SQL server management studio and do the same
Security Components At the SERVER level, we must define a LOGIN At the DATABASE level, we must define a USER account which is tied to a single login We can then define permissions (SELECT, INSERT, UPDATE, DELETE, EXECUTE) or role membership for that user account
Imagine we have lots of users Both are members of AD group: sales_people DEMO Billy Badger (BB) John Johnson (JJ)
Low Budget/low security solution We can grant the user membership of the built in database roles db_dataReader and db_dataWriter giving them full access READ/WRITE to all tables in our database. Similar to access Custom User roles DEMO
TESTING IS KEY! How can we fake being another user (Because our own user has full rights)? Windows has a program called Runas which can be used to execute under a different security context. Within SQL server we can use Execute as in SQL is great for testing on- server stuff, but we really need to test our app. The syntax is simply C:\Windows\System32\runas.exe /user:AEC-T480S\JJ "C:\Program Files (x86)\Microsoft Office\root\Office16\MSACCESS.EXE \"C:\Users\aec\OneDrive\Presentations\Sql Server Security\Access App.accdb Run Access App as JJ
You just made friends with IT Now IT can manage users, by adding or removing them from the AD group sales_people without having to even touch the database.
Permissions Grant RIGHTS on OBJECTS to PRINCIPAL Rights: Insert (C) Select (R) Update (U) Delete (D) Execute (Procs and functions) (Other .)
Permissions Grant RIGHTS on OBJECTS to PRINCIPAL Objects: Tables Views Stored Procedures Functions Schemas Single columns in a table Other?
Permissions Don t grant rights directly on users Grant RIGHTS on OBJECTS to PRINCIPAL Principal: User (Windows user or Windows Group) Custom Role Other?
Keeping app in synch So for a good user experience, the interface should adapt (Allow Edits/Allow Additions/Allow Deletions) If you have multiple roles, and need to know if the user is member of a role:
Column level security Grant select (sales_id,sales_amount) on tbl_sales Grant update (sales_amount) on tbl_sales DEMO
Row Level Security By using Views Security Policy RLS (SQL Server 2016+) Wait as admin, I can no longer view the table.. Admin Override
When simple (CRUD) isnt enough The more critical the data, or the more complex the update operation, the more likely we need a stored proc View for browsing Temp table for single record view Stored proc for updating Requery with position return when done DEMO
New Development We usually ask IT to have: A new database created on their server A user with db_owner membership to the database A test-user with regular access to the database Development is done either on our own server, and then ported, or done via VPN (usually if there are integration requirements, or GDPR restrictions)
Thank you for listening Followup questions: aec@exacto.dk
