Anders Ebro Building a Secure Access SQL Server

Anders Ebro Building a Secure Access & SQL Server Solution

Before we get started • How many of you use SQL server every day?

Security can be complex

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) Johnson (JJ)

Low Budget/low security solution • We can grant the user membership of the built in database roles db_data. Reader and db_data. Writer 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 onserver stuff, but we really need to test our app. • The syntax is simply • C: WindowsSystem 32runas. exe /user: AEC-T 480 SJJ "C: Program Files (x 86)Microsoft OfficerootOffice 16MSACCESS. EXE "C: UsersaecOne. DrivePresentationsSql Server SecurityAccess 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) isn’t 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