Solved

sp_OACreate - EXECUTE denied on object  [ It is a critical object ? ]

Posted on 2004-08-04
10
1,819 Views
Last Modified: 2007-12-19
Hi all,

A client in our shared SQL enviroment creates a function and ask me to permit it acess to sp_OACreate object, but, I don't know what really do this object and if it is a critcal object for security issues.

Below all needed information:

~~~Error when try to run function~~~~

Server: Msg 229, Level 14, State 5, Procedure SymDecrypt, Line 12
EXECUTE permission denied on object 'sp_OACreate', database 'master', owner 'dbo'.

~~~Select that generate this error~~~~~

SELECT
dbo.SymDecrypt ('zip9cem', CONVERT(TEXT,PED_DADO_CC)) AS CARTAO
FROM PEDIDO WHERE PED_CODIGO=20000

~~~~Store procedure Code~~~~~

CREATE  FUNCTION SymDecrypt (@Password varchar(255), @CipherData varchar(255))
RETURNS varchar(255) AS
BEGIN
DECLARE @object int
DECLARE @hr int
DECLARE @ClearData varchar(255)
DECLARE @source varchar(255)
DECLARE @description varchar(255)
DECLARE @Tmp varchar(255)

EXEC @hr = sp_OACreate 'DynCrypto.Crypto', @object OUT
IF @hr <> 0
BEGIN
--- Report error
EXEC @hr = sp_OAGetErrorInfo @object, @source OUT, @description OUT
IF @hr = 0
BEGIN
SET @Tmp = '(' + @source + ') ' + @description
END
RETURN @Tmp
END

--- Call SymDecrypt
EXEC @hr = sp_OAGetProperty @object, 'SymDecrypt', @ClearData OUT, @Password, @CipherData
IF @hr <> 0
BEGIN
--- Report error
EXEC @hr = sp_OAGetErrorInfo @object, @source OUT, @description OUT
IF @hr = 0
BEGIN
SET @Tmp = '(' + @source + ') ' + @description + '2'
END
RETURN @Tmp
END

--- Destroy the DynCrypto object
EXEC @hr = sp_OADestroy @object
IF @hr <> 0
BEGIN
--- Report error
EXEC @hr = sp_OAGetErrorInfo @object, @source OUT, @description OUT
IF @hr = 0
BEGIN
SET @Tmp = '(' + @source + ') ' + @description
END
RETURN @Tmp
END

RETURN @ClearData
END

0
Comment
Question by:ipsystems
  • 5
  • 4
10 Comments
 
LVL 34

Assisted Solution

by:arbert
arbert earned 50 total points
Comment Utility
Looks like they're calling an external encryption function.

SP_OA allows you to call external dlls/objects that gives added functionality not in SQL Server.  There is the possibility it could be considered a security hole,  but the user of the function needs to know the DLLs and functions being called in order to use it.
0
 
LVL 12

Expert Comment

by:patrikt
Comment Utility
Security proplem is that when you grant access to sp_OA.. procedures to someone, he can run any COM/ActiveX object. You have no control what will be called and what it can do to your system.

If your user needs this functionality i suggest that you create that SymDecript procedure under your (dbo) context and then only give permission to that procedure.
In this scenario you have control ower what is called (sp_OA is called in your context) and user will have needed function.

Consider that when procedures are called in chain, permission is only checked when owner is differenc. When user1.Proc1 calls user1.Proc2 no permission check will be done on second call. But when user1.Proc2 calls user2.Proc ther will be permission check.

Patrik
0
 
LVL 12

Accepted Solution

by:
patrikt earned 450 total points
Comment Utility
Just same code:

In master DB as you (dbo,sa)

CREATE  FUNCTION SymDecrypt (@Password varchar(255), @CipherData varchar(255))
RETURNS varchar(255) AS
BEGIN
...

And give EXEC permission to user.

User call:
SELECT
master.dbo.SymDecrypt ('zip9cem', CONVERT(TEXT,PED_DADO_CC)) AS CARTAO
FROM PEDIDO WHERE PED_CODIGO=20000

User have no direct access to sp_OA... procs.


0
 
LVL 34

Expert Comment

by:arbert
Comment Utility
Have you actually tried this solution???  I just tested--results are interesting....
0
 

Author Comment

by:ipsystems
Comment Utility

Yes,

I will create a Function on Master and give execute permission only to this specific user.

Tks vry much ;)


0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 34

Expert Comment

by:arbert
Comment Utility
No, what I'm saying is, how are you going to create a function without the use of sp_oa?  And, when a user has rights to that function or proc, that doesn't give them the rights to execute sp_oa (which is needed from your above example because it's an external routine).
0
 
LVL 12

Expert Comment

by:patrikt
Comment Utility
When function is created under dbo it func in same contect as sp_OA which is also dbo. In this case SQL will not check permissions in second call.

Both SymDecrypt function and sp_OA are in same context so SQL will check only first call usesr->dbo.SymDecrypt.

In general when there is sequence of calls (procs, functions, tables atc.) SQL will check permissions only on owner boundaries.

This option is probably configurable and can be switched off, but I have no experience with this.
0
 
LVL 34

Expert Comment

by:arbert
Comment Utility
"In general when there is sequence of calls (procs, functions, tables atc.) SQL will check permissions only on owner boundaries."

But did you test it???  It doesn't work this way with sp_OA...
0
 
LVL 12

Expert Comment

by:patrikt
Comment Utility
I have exact same solution on production server. Proc which calls sp_OA to send mail.
I works well on 3 of my servers.

If you are in doubt I have to double check what should be diferent.

IPSYSTEMS? Are you still with us? Is that solution running in your environment?
Albert put me in great feer. I have to find what should be set to put it in work.

Thanks for comments albert. I'm going to test it carefully on other server.
0
 
LVL 12

Expert Comment

by:patrikt
Comment Utility
My test environment:
- standard SQL setup
- User patrik with access to master (no rights set)
- sa user which creates and grants permissions

There is test code:
--For SA
USE master
CREATE Function dbo.TestOA ()
RETURNS int
AS
BEGIN
      declare @handle int
      EXEC sp_OACreate 'JMail.Speedmailer',@jmail OUT
      RETURN @handle
END

GRANT EXEC ON dbo.TestOA TO patrik

CREATE PROCEDURE dbo.spTestOA
AS
      declare @handle int
      EXEC sp_OACreate 'JMail.Speedmailer',@handle OUT
      SELECT @handle

GRANT EXEC ON dbo.spTestOA TO patrik


--For uasr (patrik)
USE master

exec sp_OAcreate

EXEC dbo.spTestOA
SELECT dbo.TestOA()


select * FROM syspermissions JOIN sysobjects ON syspermissions.id=sysobjects.id
where name like 'spTestOA'

select * FROM syspermissions JOIN sysobjects ON syspermissions.id=sysobjects.id
where name like 'TestOA'

select * FROM syspermissions JOIN sysobjects ON syspermissions.id=sysobjects.id
where name like 'sp_OACreate'


You have to change COM ProgID to something meaningfull on your server.

Please test it on your server. Thanks.
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

Suggested Solutions

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how the fundamental information of how to create a table.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now