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 5 years • Based out of Denmark, with 18 employees, recently opened two branches in Germany • Full time Access/SQL server developer for 12 years, 9 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 • • • Data on 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 each year 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 Note: Contained databases have contained user where LOGIN and USER are defined within the database. Good for Azure SQL

Imagine we have lots of users DEMO Both are members of AD group: sales_people 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)? • Within SQL server we can use “Execute as …”. It is fine for testing on-server stuff, but we really need to test our app. • Windows has a program called “Runas” which can be used to execute under a different security context. • The syntax which we us in a shortcut file • C: WindowsSystem 32runas. exe /user: AEC-T 480 SJJ "C: Program Files (x 86)Microsoft OfficerootOffice 16MSACCESS. EXE "C: Sql Server SecurityAccess App. accdb“ Run Access App as JJ

You just made friends with IT • Now IT will be able to handle users: • Adding or removing them from the AD group “sales_people” • They don’t have to even touch the database. (which makes me happy)

Permissions • Grant RIGHTS on OBJECTS to PRINCIPAL • Rights: • • • Insert (C) Select (R) Update (U) Delete (D) Execute (Procs and functions) • (Others, such as Create Login. . Create Table)

Permissions • Grant RIGHTS on OBJECTS to PRINCIPAL • Objects: • • Tables Views Stored Procedures Functions Schemas Single columns in a table Other?

Permissions • Grant RIGHTS on OBJECTS to PRINCIPAL • Principal: • User (Windows user or Windows Group) • Custom Role • Other? • Don’t grant rights directly on users

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_person, sales_amount, sales_date) on tbl_sales • Grant update (sales_amount) on tbl_sales DEMO

Row Level Security(RLS) • 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 in Access 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