Voice of the DBA

Tag: security

  • Understanding Security

    We have to learn to use better encryption than this.

    SQL Server has become a very complicated product, with so many subsystems and features that I don’t know anyone that is an expert in all of them. There are lots of people that become somewhat familiar with many features, and learn to understand enough to use them competently. However there is one area that seems to confuse many people, but is one area that is also quite important to a secure SQL Server: encryption.

    It seems that the idea of encryption is easy, but once we get into the actual practice of managing keys, indexing encrypted columns, and dealing with disaster recovery techniques, encryption quickly becomes complex. If the technical people managing servers struggle to deal with encryption, what hope does the average user have to implement encryption? Likely little to no hope of doing it well, which is a problem as many end users will have data on their machines. TDE is supposed to make this easy, but it solves only certain problems and isn’t available in all editions.

    I ran across a very interesting article in the Economist on what a general understanding of what encryption means in a practical sense. The article is somewhat based on the Dropbox issues I wrote about recently, but also speaks to the general misunderstanding many people have about what encryption actually means.

    I’ve always been hesitant to implement encryption widely, mostly because of the problems of managing keys. Keeping track of them, ensuring they are safe, in multiple places, and easily deployed in a DR situation, is a complex task, and making a mistake can have permanent consequences.

    I don’t know how to both maintain security, and also implement enough safety to ensure access to encrypted data is available, but I do know that this is a task data professionals need to learn to accomplish.

    Steve Jones

    The Voice of the DBA Podcasts

  • The Care of Data

    It's not just a Dropbox problem

    DBAs are supposed to be trustworthy. After all, they are the custodians of data and often have access to sensitive information because of the nature of their system administrator level privileges. Their turnover ought to be low, and hopefully they have spent years building skills and a reputation that will provide them with a good job. They ought to value this investment and treat it seriously. Customer Service people are not usually in the same position in their careers, often at the beginning of their technical careers and usually change jobs regularly.

    Recently Dropbox had to back off the stance that it’s employees can’t view your data, with this note that their staff can access your files in some circumstances. There’s no evidence that employees have mis-used their access, but it could happen, and that’s a concern. It’s a concern with any cloud based service, and I think this is one area that cloud-type vendors really need to assure their customers that it won’t happen.

    Another major concern is overall security. If a cloud vendor’s employees can access your files, so can a hacker that gains access. That is, likely, an overriding concern of many customers, and it’s an area that I think that we really need to disclose openly the measures taken, the auditing in place, and the monitoring to detect any issues. Better encryption that actually prevents access by the vendor or it’s employees is a better solution.

    I hope we get encryption methods because the third major concern with cloud data is access by legal authorities. There are cases where the government might have a right to access your data, but that should be when they serve you with notice, not some company that is holding your data.

    There are many great customer service people and DBAs with strong worth ethics and morals. They take their responsibility as the custodian of your data seriously. There are a few, however, that are not so professional, and release information, sell data, put stories or video on some site like YouTube or TheDailyWTF for a laugh. Something that I’m not sure most of us that store data in the cloud would like to see happen with our pictures or video, and definitely something that companies would not want to see with proprietary information.

    Steve Jones

    The Voice of the DBA Podcasts

  • Stalking the Bad Guys

    Tracking down bad guys

    Suppose you found some malicious code on your system. Maybe it’s a login that didn’t below at the sysadmin level, or maybe a stored procedure that might disable a trigger, make a change, and re-enable the trigger. What do you do?

    Suppose you stumbled upon a strange stored procedure in one of your databases. It wasn’t something you had coded, and it appeared to be altering permissions, maybe adding a login, or even querying sensitive data. What do you do?

    The easy answer is to delete the procedure and go on about your day. Some of you might save a copy of the code, along with its permissions and make a note to check the system a week or two later and see if it needed to be there.  However I’m not sure that is the best action to take.

    If you are unaware of the origins of code, it can be detrimental to your system if you remove object. A legitimate stored procedure might cause application errors, which can result in some type of discipline. At the very least, managers might be upset with you for taking action without understanding the consequences.

    I think a different approach might be better. Set up auditing or tracing, focusing on the code and documenting any actions. I would talk to security people and potentially initiate lower level tracing of the network to determine who is making use of this code. If it’s truly malicious code, it’s not necessarily your job to stop someone. Setting up the honeypot, documenting actions, and then allowing someone else to determine the final action might be the best way to deal with the situation.

    Steve Jones

    The Voice of the DBA Podcasts

  • Checking Permissions

    Someone posted this query recently:

    select a.*,name, b.* from sys.database_principals a, sys.database_permissions b

where permission_name = 'INSERT' and b.grantee_principal_id = a.principal_id

    That’s a little ugly, so let’s fix it:

    SELECT  a.name, a.principal_id, a.is_fixed_role
      , a.default_schema_name
      , b.permission_name, b.permission_name
      , b.state_desc
 FROM sys.database_principals a
  INNER JOIN sys.database_permissions b
  ON b.grantee_principal_id = a.principal_id
WHERE permission_name = 'INSERT'

    If you run this, you’ll get INSERT permissions in your database. In this case, the person had one row returned that had “public” in it, as shown.

    results1

    I normally don’t have permissions for public, but in this case I had run this first:

    GRANT INSERT ON Person.Address TO Public

    I don’t recommend permissions for public, and you really ought to run this on all your servers:

    SELECT a.name, a.principal_id, a.is_fixed_role
      , a.default_schema_name
      , b.permission_name, b.permission_name
      , b.state_desc
 FROM sys.database_principals a
  INNER JOIN sys.database_permissions b
  ON b.grantee_principal_id = a.principal_id
WHERE a.name = 'public' AND major_id > 0

    How do you find out which objects have permissions? There’s a clue in the last query. If you scroll across in the results, there’s a major_id column. You can use that to find the object.

    results2

    The OBJECT_NAME function is handy here, and it takes an object_id, which is the major_id. If I run this:

    SELECT OBJECT_NAME(85575343)

    I get “Address” back, which is the object I altered.

    And, of course, we need to clean up

    REVOKE INSERT ON Person.Address TO Public