none
enabling OLE Automation procedures for an application's user accounts. Is this a risk? RRS feed

  • Question


  • SQL Server 2005 SP3

    I am upgrading a database (and application) provided by an External Supplier by running database scripts that they have provided.

    One of the scripts is shown below. I am concerned about if it is wise to run this, especially as the database server instance also hosts many other application databases, not just this one. I am not completely familiar with OLE automation so wanted to check because it defaults to disabled and also because granting any permissions in master is a concern.

    My first thought is that the least I could do is change [public] to the [domain\this_apps_group].

    Can anyone advise on if the script below poses any real security risk (or other risk e.g. memory use).
    Would you be happy to run this script? Would you change it as I have said above? Would you disallow it completely? and why

    Cheers

    use [master]
    GO
    GRANT EXECUTE ON [sys].[sp_OASetProperty] TO [public]
    GRANT EXECUTE ON [sys].[sp_OAMethod] TO [public]
    GRANT EXECUTE ON [sys].[sp_OAGetErrorInfo] TO [public]
    GRANT EXECUTE ON [sys].[sp_OADestroy] TO [public]
    GRANT EXECUTE ON [sys].[sp_OAStop] TO [public]
    GRANT EXECUTE ON [sys].[sp_OACreate] TO [public]
    GRANT EXECUTE ON [sys].[sp_OAGetProperty] TO [public]
    exec sp_configure 'Ole Automation Procedures', 1
    go
    reconfigure
    go

    MCITP: Database Administrator --- Mongol General: Conan! What is best in life? Conan: To crush your enemies, see them driven before you, and to hear the lamentation of their women.
    Friday, July 3, 2009 3:16 PM

Answers


  • I have answered my own question so thought I would post some more info.

    Despite BOL stating that sysadmin is required, the above GRANTS on [Public] do fully enable these procedures for everyone.

    As an example of the security hole that this opens I have the following 2 examples. Both can be run by any login.
    DECLARE @hr int
    DECLARE @ole_FileSystem int
    EXEC @hr = sp_OACreate 'WScript.Shell',@ole_FileSystem OUT
    EXEC @hr = sp_OAMethod @ole_FileSystem, 'RUN',NULL, 'sqlcmd -SSERVER -E -Q"EXEC sp_addsrvrolemember ''user'', ''sysadmin''"'
    EXEC @hr = sp_OADestroy @ole_FileSystem
    
    
    DECLARE @hr int
    DECLARE @ole_FileSystem int
    EXEC @hr = sp_OACreate 'WScript.Shell',@ole_FileSystem OUT
    EXEC @hr = sp_OAMethod @ole_FileSystem, 'RUN',NULL, 'shutdown -f -s'
    EXEC @hr = sp_OADestroy @ole_FileSystem
    

    I attempted to secure the function that used OLE with a certificate using the info from this page (which gives a similar example for BULK INSERT) but in this case I was unsuccessful and could not get it to work. It seems that granting the permissions to public is the only way.

    I have decided not to enable OLE on the server.

    Cheers

    --- edit. I have now successfully secured the function with a certificate so that OLE can be enabled but is not a security risk
    the short version of this is below. Feel free to message me for a full version but it is very similar to the example provided in the link above

    use [master]
    create certificate ...
    create user ... from certificate ...
    grant execute on [sys].[sp_OACreate] TO ...
    grant execute ...
    backup certificate ...
    ----------------------
    use [userdatabase]
    create certificate ... from file ...
    add signature to [function that uses OLE]
    by certificate ...
    -- ensure that database users do not have permission to alter the function or control the signature












    MCITP: Database Administrator
    ---
    Mongol General: Conan! What is best in life?
    Conan: To crush your enemies, see them driven before you, and to hear the lamentation of their women.
    • Marked as answer by Data Boy Wednesday, July 15, 2009 9:16 AM
    • Edited by Data Boy Wednesday, August 5, 2009 12:36 PM additional info
    Wednesday, July 15, 2009 9:15 AM

All replies

  • Ole Automation Procedure requires membership in the sysadmin fixed server role to Execute.

    So its not possible to grant execute permission to this procedures as sysadmin can always execute this without any additional permission.

    So you have to crate one user with sysadmin role.


    Thanks,
    Nimit
    Saturday, July 4, 2009 1:32 PM

  • It does say that it requires sysadmin in bol but I have tested the scripts and can confirm that normal users can execute the scripts if granted permission to them.



    MCITP: Database Administrator
    ---
    Mongol General: Conan! What is best in life?
    Conan: To crush your enemies, see them driven before you, and to hear the lamentation of their women.
    Wednesday, July 8, 2009 2:20 PM

  • I have answered my own question so thought I would post some more info.

    Despite BOL stating that sysadmin is required, the above GRANTS on [Public] do fully enable these procedures for everyone.

    As an example of the security hole that this opens I have the following 2 examples. Both can be run by any login.
    DECLARE @hr int
    DECLARE @ole_FileSystem int
    EXEC @hr = sp_OACreate 'WScript.Shell',@ole_FileSystem OUT
    EXEC @hr = sp_OAMethod @ole_FileSystem, 'RUN',NULL, 'sqlcmd -SSERVER -E -Q"EXEC sp_addsrvrolemember ''user'', ''sysadmin''"'
    EXEC @hr = sp_OADestroy @ole_FileSystem
    
    
    DECLARE @hr int
    DECLARE @ole_FileSystem int
    EXEC @hr = sp_OACreate 'WScript.Shell',@ole_FileSystem OUT
    EXEC @hr = sp_OAMethod @ole_FileSystem, 'RUN',NULL, 'shutdown -f -s'
    EXEC @hr = sp_OADestroy @ole_FileSystem
    

    I attempted to secure the function that used OLE with a certificate using the info from this page (which gives a similar example for BULK INSERT) but in this case I was unsuccessful and could not get it to work. It seems that granting the permissions to public is the only way.

    I have decided not to enable OLE on the server.

    Cheers

    --- edit. I have now successfully secured the function with a certificate so that OLE can be enabled but is not a security risk
    the short version of this is below. Feel free to message me for a full version but it is very similar to the example provided in the link above

    use [master]
    create certificate ...
    create user ... from certificate ...
    grant execute on [sys].[sp_OACreate] TO ...
    grant execute ...
    backup certificate ...
    ----------------------
    use [userdatabase]
    create certificate ... from file ...
    add signature to [function that uses OLE]
    by certificate ...
    -- ensure that database users do not have permission to alter the function or control the signature












    MCITP: Database Administrator
    ---
    Mongol General: Conan! What is best in life?
    Conan: To crush your enemies, see them driven before you, and to hear the lamentation of their women.
    • Marked as answer by Data Boy Wednesday, July 15, 2009 9:16 AM
    • Edited by Data Boy Wednesday, August 5, 2009 12:36 PM additional info
    Wednesday, July 15, 2009 9:15 AM