Solved

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

Posted on 2004-08-04
10
1,825 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
ID: 11723311
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
ID: 11723769
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
ID: 11723908
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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
LVL 34

Expert Comment

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

Author Comment

by:ipsystems
ID: 11726865

Yes,

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

Tks vry much ;)


0
 
LVL 34

Expert Comment

by:arbert
ID: 11727172
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
ID: 11727420
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
ID: 11727484
"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
ID: 11733535
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
ID: 11733696
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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

831 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