Transcript SQL HelpDesk

About [email protected]

DBA for ~4+years, IT Professional for 7.5 years.

Currently I am a Data Architect

Worked with SQL 2000/2005/2008/2012/2014

I have worked in a large environment: 300+ Instances, ~6000 databases, 10s
TBs of data.

I have worked in a small environments: 5 instances, 60 databases, 1TB of data

Automation is my favorite tool.
SQL Self Service
Repeatable, Automated, and Safe Leverage
SQL Saturday #513 Albany, NY
Overview: Outsourcing Database
Administration to your Users

How do we enable self service? And why?

How do we secure it?

How do we build it?
Why would you want to use a self
service option?

The little things are interruptive to the DBA(s).

Empower the users safely, reducing time for both parties.

DBAs have More time for the hard problems.

Reduce administrative errors.
Enabling Self Service : Controlled Access
and Security Elevation (C.A.S.E.)

In order to create a self service option we have to grant people access.

There are two ways to do this, the unsafe way and the safe way.

Taking advantage of temporary security elevation is the key idea.
How it normally works
User Needs
Something
User
Submits a
ticket
User
confirms
DBA follows
up with user
User pings
DBA
DBA Does Everything
DBA pauses,
reviews
request
DBA
executes
DBA reviews
again
DBA request
more info
User sends
more details
Lets compare how it works
Give the Helpdesk direct access aka SA
Give Helpdesk Self Service via
Controls
HelpDesk
executes
proc
HelpDesk Has
Elevated Access
HD tries to drop
Login
DBA URLT
DBA scrambles
to figure out
what
happened.
DBA has no clue
Business is
down!
Wrong Login is
dropped!
User URLT
Tries to drop
Login
Business is
safe.
Action is
blocked
DBA notified
Action is
logged
How to secure it

Make it SAFE

Using role based permissions

Passing parameters safely with QuoteName() and injection checks

Executing procedures as OWNER

Using existing tool sets: SQL and powershell.

Using something scary: TRUSTWORTHY Database.
A Trustworthy Database Is Dangerous

Trustworthy lets the dbo do anything he can normally do outside of the
database.

Because of this we have to be careful with what we allow our users can do.

We have to leverage Stored Procs and Role based permissions.

We have to have safe repeatable code, with repeatable outcomes.

Remember the goal: give the users some access into the admin world without
compromising the integrity of our Server.
How to Build a little leverage with SQL!
Things you need:

A database, where users can ONLY execute a limited set of objects.

TRUSTWORTHY and “sa” as the owner.

An audit and SQL Injection Check

ORIGINAL_LOGIN()

EXECUTE AS OWNER

QUOTENAME()

A Separate Schema and Role
Auditing, and Verification

Auditing is hard when our sprocs are executed as owner…..

ORIGINAL_LOGIN()

Verify with IS_Member(), this may be overkill. But it helps!

LOG EVERYTHING!


All Variables used

Sproc name

Who did it

Date and Time
Logs can drive alerts!
SQL INJECTION!

Watch out for the clever ones!

Encapsulate your string inputs with QUOTENAME()

LOG LOG LOG! Evidence may be necessary. You need to know what
happened.
Demo Audit, Verify, and Injection
Expanding from here

Increase your injection checks

Add additional safety checks

Add more to the log function and log tables.

Raise eventlog errors

Dump the users output somewhere, incase you need it later.

Don’t copy paste, make a sproc or a function!

Reusable code! Never write it twice!