April 11, 2014 at 7:37 am
Gary Varga (4/11/2014)
john.riley-1111039 (4/11/2014)
There are many circumstances in life where we prepare for relatively unlikely events. We install sprinklers and fire extinguishers in buildings,air bags in cars, pilots train for engine failures and forced landings and many other emergencies. We back up our computer systems.
When these things happen, it is better that we have thought about how we respond to them, and put facilities in place to mitigate the consequences.
I am sure many of us have participated in Disaster Recovery exercises. Sorry, it is called Business Continuity these days. Data breaches are just
one of the scenarios you cater for in BC planning.
I suppose just like the security procedures air stewards and stewardesses go through with each flight. They never do it because they believe that there is a risk for that particular flight but because in the unlikely situation that there is a problem then everyone is best prepared.
Even if we think (we can't know) that there is only a very slim chance that the organization's data will become the target of a hacker, another reason for having security best practices in place is to demonstrate due dilligence. In the aftermath of an attack, there is going to be a lot of finger pointing, both within the organization and from external sources like the media, class action lawyers, and law enforcement.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
April 11, 2014 at 8:53 am
Eric M Russell (4/10/2014)
The standards would not have to be very technical. Dedicated sysadmin accounts, removal of service accounts from sysadmin role, seperation duties, application accounts with minimal privilege (ie: no ad-hoc sql and access only to required tables), encryption at rest for columns containing sensitive data, encrypted backups, encrypted connections between application and database layer: these basic best practices would apply to any enterprise database platform. If a database platform doesn't provide support, then the organization has simply chosen the wrong platform.
Sounds good in practice, but this is somewhat how PCI and SOX are written. The problems come in when the encryption is poor, i.e. using MD5 for passwords, or someone argues about what minimal privilege is.
I do think the government should lay out some framework and then industries, perhaps with groups like SANS, should give more guidance and detail on what would constitute good security for a platform and version.
April 11, 2014 at 9:05 am
Steve Jones - SSC Editor (4/11/2014)
Eric M Russell (4/10/2014)
The standards would not have to be very technical. Dedicated sysadmin accounts, removal of service accounts from sysadmin role, seperation duties, application accounts with minimal privilege (ie: no ad-hoc sql and access only to required tables), encryption at rest for columns containing sensitive data, encrypted backups, encrypted connections between application and database layer: these basic best practices would apply to any enterprise database platform. If a database platform doesn't provide support, then the organization has simply chosen the wrong platform.Sounds good in practice, but this is somewhat how PCI and SOX are written. The problems come in when the encryption is poor, i.e. using MD5 for passwords, or someone argues about what minimal privilege is.
I do think the government should lay out some framework and then industries, perhaps with groups like SANS, should give more guidance and detail on what would constitute good security for a platform and version.
Obviously the government, wether it be Congress or some agency dedicated to the task, can't come up with the standards; it has to be the industry putting their heads together, sort of like the various standards working groups for HTML or network protocols.
We can argue about what minimal privilege is, for example does the DBA also need to be local Admin on the server, or does the Network Admin or service accounts also need to be sysadmin on the database server.
However, when it comes to discussions about wether the developer, CEO, or director of business analytics needs to be SYSADMIN on the database server, then that's not even worth discussing; the answer is obviously no. We all have to move past that.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
April 11, 2014 at 9:11 am
Eric M Russell (4/11/2014)
Obviously the government, whether it be Congress or some agency dedicated to the task, can't come up with the standards; it has to be the industry putting their heads together, sort of like the various standards working groups for HTML or network protocols.We can argue about what minimal privilege is, for example does the DBA also need to be local Admin on the server, or does the Network Admin or service accounts also need to be sysadmin on the database server.
However, when it comes to discussions about whether the developer, CEO, or director of business analytics needs to be SYSADMIN on the database server, then that's not even worth discussing; the answer is obviously no. We all have to move past that.
In some sense, yes, but what about SSC? For years I was the CTO and one of the developers, as well as the DBA? It's not clear cut in terms of how you do this.
The rules should be, if you don't administer the box, you aren't an administrator. However that can vary dramatically, especially in small companies. You always need a backup. You also need to remember that the way privileges work on different platforms (*Nix, OSX, etc.) can be different, so it's not as simple as specifying roles or groups.
I'm not saying you aren't correct here in terms of what you'd like to accomplish, but that it's very complex to figure out how to apply this broadly. The one thing I'd note is that for all applications, they should be able to run at less than admin privileges. However even that's hard. What about apps that gather telemetry or metrics from the box? Often that requires some level of admin access. What about adding accounts?
It's overall, a mess.
April 11, 2014 at 9:40 am
Steve Jones - SSC Editor (4/11/2014)
Eric M Russell (4/11/2014)
Obviously the government, whether it be Congress or some agency dedicated to the task, can't come up with the standards; it has to be the industry putting their heads together, sort of like the various standards working groups for HTML or network protocols.We can argue about what minimal privilege is, for example does the DBA also need to be local Admin on the server, or does the Network Admin or service accounts also need to be sysadmin on the database server.
However, when it comes to discussions about whether the developer, CEO, or director of business analytics needs to be SYSADMIN on the database server, then that's not even worth discussing; the answer is obviously no. We all have to move past that.
In some sense, yes, but what about SSC? For years I was the CTO and one of the developers, as well as the DBA? It's not clear cut in terms of how you do this.
The rules should be, if you don't administer the box, you aren't an administrator. However that can vary dramatically, especially in small companies. You always need a backup. You also need to remember that the way privileges work on different platforms (*Nix, OSX, etc.) can be different, so it's not as simple as specifying roles or groups.
I'm not saying you aren't correct here in terms of what you'd like to accomplish, but that it's very complex to figure out how to apply this broadly. The one thing I'd note is that for all applications, they should be able to run at less than admin privileges. However even that's hard. What about apps that gather telemetry or metrics from the box? Often that requires some level of admin access. What about adding accounts?
It's overall, a mess.
Granting database access to domain groups, rather than user accounts, can prevent the need for network admins to touch the database server for routine situations like when a new employee is hired.
For situations where a 3rd party monitoring tool or individual needs access to things like system views, execution plans, or traces, the following permissions can be used. They don't need sysadmin or even db_datareader membership.
In my current role, I'm not the DBA on the production servers, but I'm often the go to guy for troubleshooting performance issues or situations where something broke in production and they need to know what's going on ASAP.
The following grants me everything I need to get the job done without handing over the keys to the city. I'm relying on profiler tracing less often and don't usually request that unless I know up front it's required. This can also be implemented by the DBA as a role with accounts added or removed only as needed.
-- server level permissions require that context be changed to [master] database.
use master
-- grant user permission to view execution plans:
grant showplan to [account|role];
-- grant user permission to view object schemas:
grant view any definition to [account|role];
-- grant user permission to view system tables and views:
grant view server state to [account|role];
-- grant user permission to start sql profiler traces:
grant alter trace to [account|role];
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply