Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
ravilobo
Master Smack Fu Yak Hacker
1184 Posts |
 Posted - 2008-04-30 : 15:43:49
|
| One of my developers needs on enable Ole Automation on production? What security loop holes we are creating, by enabling this feature in SQL2K5?------------------------I think, therefore I am - Rene Descartes |
|
|
tfountain
Constraint Violating Yak Guru
491 Posts |
Posted - 2008-04-30 : 15:50:37
|
| We have been using this here for quite a few years (since SQL 2000). To be honest, I'm not so much concerned with any security issues being exposed as I am with exposing potential resource issues. If the OLE procs are used but the object handles are not cleaned up then you can end up having your memory gobbled up on your database box. What we did here was created a custom role to execute the sp_OA procs and control what logins have access to that role through a nightly security job that runs daily and resets all the permissions on the database. Our custom role simply grants EXECUTE permissions on all the sp_OA procedures and resides in the master database.Another viable option IMO is to look at CLR instead. Same concerns but just the newer, preferred way of doing this type of stuff. |
 |
|
|
Haywood
Posting Yak Master
221 Posts |
Posted - 2008-04-30 : 15:52:13
|
| You are granting the ability for someone [depending on permissions] to create COM and/or ActiveX objects in the SQL Servers process space. IIRC, only an sa level account can use the sp_OA* procedures by default, when enabled. This can be bad in the case that your object is run in the servers process space. If the object abends, it may take down or lockup the sql server as well. There are also security concerns about being able to use/manipulate COM & ActiveX objects... |
|
|
|
ravilobo
Master Smack Fu Yak Hacker
1184 Posts |
Posted - 2008-04-30 : 15:56:18
|
| Thank you, tfountain!How does nightly re-access of ole procs address the resource hog issue?------------------------I think, therefore I am - Rene Descartes |
|
|
|
tfountain
Constraint Violating Yak Guru
491 Posts |
Posted - 2008-05-01 : 13:40:13
|
quote:
Originally posted by ravilobo
Thank you, tfountain!How does nightly re-access of ole procs address the resource hog issue?
It doesn't really address any resource issues, this is just part of a larger job. We have metadata stored that mimics what our security settings should be across the database. Every night a job removes all permissions and regrants them. This is really in case we make temporary changes or some clown somehow manages to make a change that we do not want to persist. To an extent it indirectly prevents abuse of our OLE procs (really, any of our permissions). |
|
|
|
ravilobo
Master Smack Fu Yak Hacker
1184 Posts |
Posted - 2008-05-01 : 13:45:05
|
quote:
Originally posted by tfountain.. some clown somehow manages to make a change ..
I understand your feelings. I have worked with similar clowns in the past. Thanks!------------------------I think, therefore I am - Rene Descartes |
|
|
|
|
|
|
|
|
Ole automation.