Voice of the DBA

Tag: security

  • The Principle of Least Privilege – Skill #3

    This series of blog posts are related to my presentation, The Top Ten Skills You Need, which is scheduled for a few deliveries in 2011.

    Secure by Default

    SQL Server doesn’t give logins or users any rights by default. That means when you add a login or user to the SQL Server instance, the user cannot access any of the data or objects in the instance until you grant rights.

    That’s not the model that so many people have learned in many applications where once a user has access, they can view anything. This leads to many administrators and developers thinking something is wrong when they create a new login and data cannot be accessed.

    So they start by granting one of two rights initially: sysadmin or db_owner.

    That’s a huge mistake, and leads to security issues down the road if the database contains any type of sensitive information.

    The Principle of Least Privilege

    There’s a security tenet that is known as the principle of least privilege. This essentially means that any user is only allowed to the minimum amount of access needed to accomplish their job. A few examples of what this means in practice:

    • If a user is supposed to only use the HR application to add new employees, they shouldn’t have administrator access.
    • If a web application provides read only views of sales data, the account it uses to access SQL Server should only have read (SELECT) access, and no rights to change data (no INSERT/UPDATE/DELETE)
    • A manager that only maintains an employee’s address information in a self service situation should have read/write access to the address data, but not the salary data, name data, or any other employee data.
    • A developer that is allowed to back up a particular database from the production systems to restore this on the development server should not have system administrator access to production. They should have backup rights only for the database(s) the developer needs.
    • An auditing application that writes to an audit table needs INSERT rights on the table, but not UPDATE, DELETE, or SELECT.

    There are many more examples, but the basic idea is that you grant the rights needed, not every right.

    In Practice

    It feels like a lot of work to deal with roles, or think about the rights needed. It’s really not. Set up a role when someone needs access and grant the rights they need to that object. If they need more rights, grant more rights.

  • The Special Cloud

    A new Amazon cloud for the US government

    It’s not a short yellow bus special cloud, at least I hope not. Amazon is introducing a cloud for the US government, designed to meet their regulatory and compliance requirements. The idea here is that there are different needs for some sensitive information that the US government deals with, or at least some departments. I think most of us recognize this might be the case, and the same cloud infrastructure that some company like SQLServerCentral needs wouldn’t be appropriate for OSHA or some other department.

    Amazon introduced this US only cloud that is physically and logically accessible in the US only and adheres to some alphabet soup of regulatory requirements. It should allow more government organizations to store or process data in a cloud environment. I think this is a good move on Amazon’s part, and a potential benefit for some US agencies that might process lots of data at times, but not want the overhead of a large IT infrastructure to maintain permanently.

    This is a good step in the evolution of cloud computing, and I hope that Microsoft and other vendors start to develop specialized clouds as well. There are PCI compliant hosting services, but I can imagine that there might be semi-private clouds for medical, research, or other specialized data sets, which conform to the specific security and regulatory need of that industry. At some point I can imagine that Amazon or Microsoft might have parts of their data centers partitioned into different industry verticals that provide services according to different criteria.

    The cloud can provide a great place for periodic and bursty workloads for many applications and companies, but there are definitely security concerns that need to be addressed. The development of specific clouds for specialized needs seems to be a positive step in allowing more companies to take advantage of cloud computing in the future.

    Steve Jones

    The Voice of the DBA Podcasts

  • The Basic Security Model in SQL Server – Skill #3

    This series of blog posts are related to my presentation, The Top Ten Skills You Need, which is scheduled for a few deliveries in 2011.

    Users and Objects

    The basic security model diagram that I use is the one below. It’s not fancy, but it conveys the basics of security in SQL Server.

    security

    From left to right, users or clients are mapped to principals. Those principals are both in the instance (login) and database (user) as well as roles. Permissions are assigned to roles on objects.

    That’s essentially what the basic security model should be for most people. There are other types of structures (credentials, certificates, etc), but in terms of the 80/20 rule, here’s what most DBAs should do:

    • Create a login for a person (either Windows or SQL Server login)
    • Map this login to a user with the same name in those databases that person needs access to. Only pick those databases needed, not all databases.
    • Create a role in each database for each group of users/permissions.
    • Add the users to this role
    • Grant permissions on the objects needed to these roles.

    It’s not complicated, and sticking to this simple scheme, and not granting db_owner or sysadmin to logins or users will allow you to implement basic, easy to understand security in SQL Server.

    References

  • Virtual Security

    The State of New Mexico is being careful with virtual machine security

    I have a few friends that are working virtualize almost their entire computer infrastructures.  They work in large and small companies, but there is a constant push to avoid the bare metal installation of any operating system onto physical hardware, making every Windows or Unix machine a virtual machine on top of a hypervisor. I was surprised to hear that companies were being to aggressive, but the cost benefits can be huge, and when virtualization is done in a smart way, performance doesn’t suffer.

    However virtualization can change security, especially when you have VMs that are allowed to move from physical host to physical host. The state of New Mexico embarked on a similar project, and were concerned over security of the virtual machines. Their department had dismissed some employees because of a security breach a few years earlier and security was on the forefront of their minds. Additional security as well as network controls were used in their project, and I hope they also implemented strong auditing procedures.

    As we move to newer infrastructures that include virtualization, physical security becomes more important, and additional controls are needed. The ability for someone to potentially move a VM outside of a data center, or even to a less secure remote data center becomes a point of concern. Moving the storage itself might be an even bigger problem as virtual storage becomes more commonplace.

    Ultimately, however, we can’t all have dedicated security employees, nor can we expect every DBA, sysadmin or even security officer to be able to protect against and mitigate all attack vectors. Auditing is ultimately the best way to handle breaches. We can’t prevent all of them, but responding quickly, learning, and perhaps more importantly informing the appropriate people to be ready to respond to the information disclosure.

    Steve Jones

    The Voice of the DBA Podcasts