|
|
|
|
|
Database Scanner 3.0.1 1 2 3 4 5 6 7 8 9 10 The rights tab contains options that not only are security related, but also good practices in general. I don't allow anyone other than sa to own an object in a production environment. If a user needs to create a table, they do it in tempdb. Users having the ability to own objects means that they can create objects and drop objects which is a very bad idea. xp_cmdshell is a very well known security risk. This risk is mitigated by forcing it to be run under the SQLExecCommandExec account when executed by someone other than an administrator. You should then revoke all access from this account with the exception of anything that needs to be accessed by that account. Allowing remote access might not be an option. If you have to replicate databases, you are making a security compromise for the sake of functionality. At no time should you be allowing direct updates to system tables.
As you would expect for maximum security, the only allowed access is via a stored procedure from a group. In database scanner, group and role mean the same thing depending upon which version of SQL Server you are using. You will most likely need to make compromises on this screen depending upon your environment. It is really too bad that everything is an all or nothing approach. Many of the environments I've been in are set up such that a particular group(s) can directly hit tables while most of the other groups can't. You have no such granular capability and instead have to rely on ignoring some of the violations that are thrown. The one option that doesn't work against a 7.0 server is the detection of whether permissions can be delegated. This feature relies on someone using the grant with grant option. One of the main failings of security in 7.0 is that any member of a role can add any other user to that role. Most 7.0 sites have gotten lucky simply because most people don't know this barn door exists in security. Database Scanner does not detect this problem. I haven't encountered a problem it in any environments yet, because I manage permissions the hard way due to this problem. I do not use roles to manage security with the exception of fixed server or database roles. I individually grant access to users, because they will not have authority to grant access to someone else. The role concept was well done in 7.0. The implementation of roles was poorly done.
|
All content on this site, except where noted, represents an original work of Michael R. Hotek and is protected by applicable copyright laws. The SQL Server FAQ is the sole work of Neil Pike. No page, portion of a page, or download may be used for commercial purposes in whole or in part without the express, written permission of the applicable author.
