Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2004-08-04
10
Medium Priority
?
1,834 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
10 Comments
 
LVL 34

Assisted Solution

by:arbert
arbert earned 200 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 1800 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure 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

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

721 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