Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1835
  • Last Modified:

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

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
ipsystems
Asked:
ipsystems
  • 5
  • 4
2 Solutions
 
arbertCommented:
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
 
patriktCommented:
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
 
patriktCommented:
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
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
arbertCommented:
Have you actually tried this solution???  I just tested--results are interesting....
0
 
ipsystemsAuthor Commented:

Yes,

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

Tks vry much ;)


0
 
arbertCommented:
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
 
patriktCommented:
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
 
arbertCommented:
"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
 
patriktCommented:
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
 
patriktCommented:
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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now