Custom aggregate function security problem

Question

Custom aggregate function security problem

davidthegray

SSC Eights!

Points: 908
More actions
May 9, 2008 at 8:17 am

#182226

Hi. I'm getting a headache trying to solve a mysterious problem.
I have developed some custom CLR aggregate functions that return all the strings contained in a column, separated by a '<br>'
or a ' - ' or other strings.
I did this in Visual Studio 2005 one year ago and it has worked perfectly until today.
Now I needed to add a custom CLR function and I have migrated the project to Visual Studio 2008.
I have added the new function and deployed it to my development SQL server in DEBUG mode. Everything works fine.
Then I deployed everything on the server (always from within VS2008), and the toy is broken. Note that the functions are used by ASP.NET, accessing SQL through integrated authentication.
If I open a page that uses the aggregate with my personal account (I am the DBA), everything works fine. If users open the page, they get an EXECUTE authorization denied on the object 'MyCustomAggregate' error message.
I tried everything. I tried changing the authorisation level from SAFE to EXTERNAL to UNSAFE. I tried changing the assembly owner from 'dbo' to my account. Of course the db is 'trustworthy' (otherwise the CLR assembly wouldn't have worked until today).
The funny thing is that the new function (a simple scalar function) works perfectly. All I have to to is to right click on its name in SQL Management Studio, and grant the EXECUTE privilege to the 'public' role. This means that the problem is not a matter of assembly security (which was also clear from the fact that the aggregates work if I query them from my account).
It must be a matter of giving the EXECUTE authorisation to the other users. How can I do that??? Seems like there are no individual permissions on the aggregates. Running a GRANT EXECUTE ON [dbo].[MergeStringSepBR] TO [TheRoleIWant] gives an error.
For the sake of completeness, I give you the code of one of my failing aggregates:
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
[Serializable]
[Microsoft.SqlServer.Server.SqlUserDefinedAggregate(Format.UserDefined, MaxByteSize = 8000,
IsInvariantToDuplicates = false, IsInvariantToOrder = false, IsInvariantToNulls = true, IsNullIfEmpty = false)]
public struct MergeStringheSepBR : IBinarySerialize
{
public void Init()
{
accu = "";
}
public void Accumulate(SqlString Value)
{
if (!Value.IsNull)
{
if (accu == "")
accu = Value.Value;
else
accu += "<br>" + Value.Value;
}
}
public void Merge(MergeStringheSepBR Group)
{
accu += "<br>" + Group.accu;
}
public SqlString Terminate()
{
// Inserire qui il codice
return new SqlString(accu);
}
// Campo di membro segnaposto
private string accu;
#region IBinarySerialize Membri di
void IBinarySerialize.Read(System.IO.BinaryReader r)
{
accu = r.ReadString();
}
void IBinarySerialize.Write(System.IO.BinaryWriter w)
{
w.Write(accu);
}
#endregion
}

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

davidthegray SSC Eights! Points: 908 More actions · Answer 1

Today I have opened my VS2005 old project in my old machine, which still has VS2005 installed. I deployed the old project to the server but nothing has changed. Still, no EXECUTE permission on my CLR custom aggregates that worked perfectly until last Friday. :crying: I can't remember how I deployed them one year ago. There must be a way...

davidthegray SSC Eights! Points: 908 More actions · Answer 2

[h3]Horror story[/h3]

I have finally found a way to have my aggregates back to work. I had some broken web pages using them also on the public web site, so I desperately needed a solution.

The solution is this:

use [MyDatabase]

GO

GRANT EXECUTE TO [public]

GO

Now the custom aggregates work again, but I'm also learning how it feels to live in fear, like replicant Roy Batty.

Does that privilege mean that anybody can run any of the stored procedures in my db?

RBarryYoung SSC Guru Points: 143329 More actions · Answer 3

davidthegray (5/9/2008)
Running a GRANT EXECUTE ON [dbo].[MergeStringSepBR] TO [TheRoleIWant] gives an error.

OK, what was the Error?

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

davidthegray SSC Eights! Points: 908 More actions · Answer 4

rbarryyoung (5/15/2008)
davidthegray (5/9/2008)
Running a GRANT EXECUTE ON [dbo].[MergeStringSepBR] TO [TheRoleIWant] gives an error.
OK, what was the Error?

rbarryyoung,

The error I got was:

Messaggio 15151, livello 16, stato 1, riga 1

Impossibile trovare l'oggetto 'MergeStringSepBR' di tipo oggetto, perché inesistente o perché non si dispone dell'autorizzazione.

This translates into "Cannot find the object 'MergeStringSepBR' of type object, because it does not exist or you do not have permission.

What made me think there must be something wrong in SQL server is that the Management Studio does not show "properties" in the context menu when you right-click on the programmability->aggregate functions tree item. Properties are shown for all other types of items.

But right now I found out something new! I re-run the query and, wow, it works! I probably did something REALLY stupid :blush: like forgetting to run the USE [MyDB] statement, that's the only thing I can think of.

Thank you for your hint, I hope the problem is definitly solved. Still, there is something to fix in the Management Studio GUI, as the "property" menu item item is missing on the aggregates context menu.