Solved

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

Posted on 2004-08-04
10
1,821 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
 
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

861 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

25 Experts available now in Live!

Get 1:1 Help Now