• Skip to content
  • Skip to primary sidebar

DallasDBAs.com

Explaining SQL Server in plain english

Header Right

  • Home
  • Blog
  • Services
  • Pocket DBA™
  • Speaking
  • Contact
  • About

Security

TIL: Security Stuff™

March 12, 2018 by SQLDork 3 Comments

Testing Grant and Deny permissions in SQL Server.

Here’s a rough breakdown of the steps we followed:

  • Create a windows group, and create 2 users in the group (We’ll call them test1 and test2).
  • Make a new database called SecurityTest.
  • Add the windows group from earlier as a SQL LOGIN.
  • Add a USER for the login in the SecurityTest database, with no permissions (yet).
  • Create a table and put some data™ in it (We’ll call it SuperSecretTable™).
  • Grant select permission to the USER (Group).
  • Verify that users test1 and test2 can see the data™ (You’ll have to logon to the machine as these users).
  • Test2 takes a screenshot of the SuperSecretTable™ and posts it on Twitter, gets 3 likes.
  • I login as myself and specifically DENY test2 rights to view the table because he cannot be trusted with the data in the SuperSecretTable™ (Or crayons).
  • Test2 logs in again, and finds himself unable to even see the SuperSecretTable™, much less the data™ in it, even though he’s in a group that has been GRANTed SELECT permissions.

In doing this, we’ve proved that DENY takes precedence over GRANT, because we’re cool like that

/*Please work*/,

The Apprentice

Filed Under: Apprentice, Security, TIL

SQL Saturday Minnesota 2017

August 31, 2017 by Kevin3NF Leave a Comment

Now that the official schedule has been published, I am pleased to announce that I will be doing a pre-con and a regular session at SQL Saturday Minnesota on October 6-7. 2017.

The Friday pre-conference will be “DBA Fundamentals for the Junior, Accidental and non-DBAs.”  This will be a wide ranging day-long discussion on the underlying fundamental pieces of SQL Server Administration that are beneficial to both new DBAs, and those that have to work with or fill in for them.  Please see the link for details, and feel free to contact me with any questions.  There is a $110 (plus service fees) charge for this, which is split between me and the local SQL User group organizing the event, after expenses.

The Saturday regular session will be “Backups for new/non-DBAs…the Why, not the How”, where we will cover Disaster recovery scenarios and the part SQL Server plays in the recovery.  We won’t spend much time on specifically defining backup types, etc.    Currently this is scheduled for the 1-2pm time slot, but that may change.

Edit: my Saturday session is now 10:15am – 11:15am 🙂

I’m very excited to be coming to the MSP area!  I even have a friend that lives in the area that I get to see for the first time in 3 years…plus all of the new friends I will make at the events!

Thanks,

Kevin3NF

Filed Under: Accidental DBA, backup, Beginner, EntryLevel, HADR, Install, Security, SQLSaturday

xp_CmdShell is not a security risk…people are!

March 22, 2017 by Kevin3NF 1 Comment

Quickie today….

Someone please tell me why this is considered insecure:

A stored procedure that, out of the box, is disabled and has no explicit rights granted (or denied) is locked down to everyone but those in the sysadmin server role.

If someone exploits your SQL Server via xp_cmdshell, its because you LET them, either by granting permissions or by putting someone in sysadmin that clearly should not have been there.

Told you it was a quickie 🙂

Thanks for reading!

Kevin3NF

Filed Under: Security

SQL 101: The SQL Server Security Model

February 1, 2017 by Kevin3NF 5 Comments

The SQL Server Security model has a lot of moving parts to it.  Some friends of mine are so expert in it that it is their primary function on the DBA team they work for.  My friend (and recently minted Data Platform MVP) Kenneth Fisher (b|t) regularly presents on it.

Fear not!  There are a couple of key concepts that you have to get down early in order to not drown in all the other stuff.

As with most of my other 101 posts, this occurred to me while riding my bike.  Road bike…the only thing that occurs to me when mountain biking is “don’t hit that tree.”

Imagine if you will, that you own a house.

Because you are the owner, you have a key to the house.   You can do anything you want in any room of the house.  You decide who else gets keys, and what they can do inside.  We’ll assume your significant other also has a key and equal rights to all rooms.

Now just for kicks, lets assume you have 2 kids.  Suzie and Little Bobby Tables (Bobby for short).  Since mom and dad work, Suzie and Bobby also both have keys so they can get inside after school.   However, they are not allowed into each other’s rooms, nor are they allowed to change the locks, paint the walls or use the stove.  Because reasons.

So, 4 people can enter the house.   2 can do whatever they want, 2 have some restrictions.   Simple enough?

In the SQL Server security model the above translates as follows:

  • House – an instance of SQL Server
  • Keys to the house – Logins
  • Mom and Dad – Logins with sysadmin role (God rights…)
  • Suzie and Bobby – Logins without sysadmin
  • Rooms – databases

You have to have a key/Login, to even open the door/connect to the instance.

You need specific permissions to access a database/room.   Bobby is not allowed in Suzie’s room and vice-versa.  Neither kid is allowed in dad’s man-cave, but mom is.

Suzie and Bobby need to be granted access to use a database/room, so they are created as USERS in the database.   Mom and Dad do not explicitly need to be granted as USERS in any database, because they own the place and can do whatever they want at the server/house or database/room level.

If Uncle Joe is coming for a visit, he would also get a key/LOGIN and access to the rooms/databases he will be using.   When he leaves, he gives his key back (USER/LOGIN removed).

If you want, imagine Bobby has a brother and they share a room.  In their room there are two beds.  They don’t share and they don’t switch.  In this example the beds are database objects such as tables.  You can grant or deny access down to that level as well.

So, LOGIN is created first (can’t go into a room until you get in the house). USER access for non sysadmins is created next at the database level.  Inside the database, you GRANT or DENY permissions to objects in the room (if you choose).   You can get very, very granular if your application/employer requires it.

This is high level overview, with a lot of specifics left out.  Such as, give LOGINs to Groups (preferred, instead of individuals), server and database roles, etc.   Just knowing the difference between LOGIN and USER puts you ahead of non-DBAs who use the terms interchangeably.  Login can be either Windows based or created within SQL Server depending on how the instance is configured and your security teams requirements.

Hope this makes your life easier!  Comments are welcomed.

Update:   My Apprentice astutely made the observation that a physical or virtual server with more than one SQL Server Instance installed would be a “neighborhood” in this example.   Would that make a Failover Cluster a duplex? 🙂   Would a multi-tenant database be an apartment building?

Thanks for reading,

Kevin3NF

 

 

Filed Under: Accidental DBA, Apprentice, Beginner, EntryLevel, Security

Security fail.

August 11, 2016 by Kevin3NF Leave a Comment

This…just no:

 

 

Filed Under: Security

Primary Sidebar

Search

Sign up for blogs, DBA availability and more!


Copyright © 2019 · Genesis Sample on Genesis Framework · WordPress · Log in