Restricting execute of a user defined CLR function to sysadmin and not dbowner

Question

I'm probably missing something obvious or going about this in the wrong way, but here it goes:

I've written a scalar CLR function in SQL Server 2005. It has one parameter (int) and returns a varchar(2000). I'm having trouble figuring out how to specifically grant permissions to sysadmin role and restrict any other role, including db_owner, from executing it.

I'm guessing that granting and revoking permissions is the same for CLR functions as any other UDF. This is a sensitive function because it decrypts a password that's stored in a SecurityUser table for the application. I don't want anyone to be able to run it except members of the sysadmin role.

In the database, I first tried granting to sysadmin from the database:

grant execute ON dbo.fCrossTabDx1 TO sysadmin

and of course received the error:

Msg 15151, Level 16, State 1, Line 1
Cannot find the user 'sysadmin', because it does not exist or you do not have permission.

I then tried to grant permission to db_securityadmin, which is a role in the database and got error:

Msg 4617, Level 16, State 1, Line 1
Cannot grant, deny or revoke permissions to or from special roles.

So, would appreciate someone steering me in the right direction, if this is possible.

Can I restrict a user with db_owner permissions from executing a function, and
Can I allow only sysadmin role execution permission on a function?

Thanks.

Aside: As a rule, if you can decrypt a password you're doing something wrong. A one-way encryption or hashing is generally used to prevent recovering passwords. — HABO
– HABO, Commented Apr 16, 2012 at 17:24

Remus Rusanu · Accepted Answer · 2012-04-16 17:11:06Z

2

You cannot restrict permissions on the securable owner. By definition members of db_owner own every securable in a database. A function is a database securable. Ergo, you cannot restrict permissions on a function to dbo or members of db_owner role.

You are doing it wrong:

rolling your own encryption/decryption functions. Use the built in encryption functions.
rely on permissions for cryptographic security. You should rely on knowledge of decryption password, ie. the data can be decrypted by those who know the password.
I'm pretty sure your CLR function has the decryption password built in it, ie. you embed decryption keys in code, a big big big no-no.

Do read and internalize the Encryption Hierarchy. Do follow standard practices. Do not invent your own.

edited Apr 16, 2012 at 17:11

answered Apr 16, 2012 at 17:03

Remus Rusanu

296k42 gold badges459 silver badges583 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Dan G. Over a year ago

Yeah, I kind of figured. I took the lazy approach and I guess will need to do it right and grant permissions explicitly. Thanks.

Dan G. Over a year ago

I know I've exposed my dirty laundry here. At the time, the decision to not use sql security and rely on an application security layer seemed appropriate for the level of security they needed and the total lack of onsite dba support in a very remote location. But now I'm not so sure it has saved us work and we probably should return to a sql security model and just rely on managing logins and database users. This would avoid the entire need for application encryption/decryption. Points well taken, Remus.

Collectives™ on Stack Overflow

Restricting execute of a user defined CLR function to sysadmin and not dbowner

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related