June 6, 2003 at 6:23 am
Hi People,
Well, I'm hoping this is a simple one. Anyway, I'm in the process of locking down our SQL server - following the (excellent) articles found on this site (10 steps to securing your SQL by Brian Knight - SQL Server Security Part's 1 & 2 by Chris Kempster).
However, I'm struggling on one particular part and that is locking down Extended Stored Procedures (which is recommended in both articles). Now I'm not that keen on simply 'dropping' the XSP's as I might want to use them in the future AND I don't know whether they have any particular dependencies or not. In the article, it is suggested that the appropriate XSP's are merely disabled however, how do you do that?
I know I can remove 'Execute' permission for users of the database - but what about users who have 'implied' access (i.e. the DB Owner - I think!). Should I 'revoke' access to the appropriate users, i.e. just the 'sa' account? If so, how? And will I be able to reinstate the permissions later on?
Thanx for any help or pointers + (I'm actually a developer with limited DBA experience, so please be nice)
Drydo
Microsoft MVP (VB)
June 6, 2003 at 6:27 am
Extended Stored Procedures are only found in the master database. If you revoke access to an extended stored proc, only a role that can bypass security would have the ability to execute them. The only role that fits that bill is sysadmin. So if you revoke execute rights against the extended stored procedures, only members of the sysadmin role can execute them.
Keep in mind that even if you revoke permissions to a particular extended stored procedure, someone with that level of access can come back around and re-enable access. Also, if you drop an extended stored procedure, someone who knows what they are doing and has sysadmin rights can re-add it unless you also move the .DLL that is for the extended stored procedure. Of course, most .DLLs support multiple extended stored procedures, and you can't move it if you need access to others that it also drives.
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley
June 6, 2003 at 6:56 am
Hi Brian,
Thanx for your input. OK - I know that the XPSs are stored in the Master DB - so what you are saying - if someone gets hold of the SA password, it doesn't matter what I do they can still mess with the system?
For your info - no one but the SA account has any kind of access to the Master (or any other databases). My database has two separate usernames with only datareader access with all interaction done through Stored Procedures - is this OK?
+ Apart from securing the SA account - there isn't too much I can do?
Cheers Dude - Drydo
Microsoft MVP (VB)
June 6, 2003 at 8:38 am
Yes, always secure the sa account with a strong password. Now, let's start with roles.
The sa account is built into SQL Server. If you specify for SQL Server to run in Windows Authentication mode (not mixed), it won't be active.
However, there is a fixed server role, sysadmin, and users can be added to that. By default, the BUILTIN\Administrators group is a login that is a member of this server role. The sysadmin group can do everything sa can do (literally everything on the SQL Server). The BUILTIN\Administrators group is the local Windows administrators group on the system.
Now, before removing BUILTIN\Administrators, you have to be aware of the issues. I'll cover that in an article I'm working on now for my security column. However, the effects have been answered in the forums from time to time. Allen_Cui has answered this question a lot as have I.
These may be the only users outside of the ones you've defined. It doesn't sound like an issue based on what you've set up, and if that's the case, you just probably need to look at what XPs the public role has access to. Keep in mind that in SQL Server a database can have a guest account. In a user database, you have to add this user before it's active. What the guest account does is if you don't have a valid user account in the database and the guest account is present, you take on the permissions of the guest account. Well, all user accounts are in the public role, to include guest. So if guest exists, anyone who can access the server can access what's in the database.
Okay, now here's the catch. The system databases, msdb and master both, require the guest account. This is mandatory and the guest account is not to be removed. That means if a user can log on to SQL Server, the user has access to everything the public role has access to within the master database. See the discussion in the following thread:
http://www.sqlservercentral.com/forum/link.asp?TOPIC_ID=12885
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley
June 6, 2003 at 9:14 am
I think bkelly has covered most of it, but I just want to suggest that I don't
think locking down extended proc. should be taken as a security measure.
The reason for that is, as bkelly mentioned only the sysadimn role can exec. these
procs. If you restirct your databases access using user defined roles or groups, monitor your local server adminstrator group and make your sa password extremely difficult to crack you should not worry about removing these procs.
I've used extended procs. numerous times and they're very handy, especially if
you're write adminstrative and maintenance scripts.
MW
MW
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply