| Author |
Topic |
|
tfountain
Constraint Violating Yak Guru
491 Posts |
 Posted - 2003-06-10 : 11:10:50
|
| First, let me explain the need and my proposed solution:I have a need where I think the best approach would be to use a multi table valued function. This function will be used in several places in our system. The purpose of this function is to implement a custom security type concept to only allow individuals to view employees they have access to. For example, people that have access to our system will have a security group associated with thier user record. This security group is defined based on fixed columns on the employee record (although, any combination of these columns can be used in any given security group). Basically, evaluating a request for an employee search is somewhat dynamic in this respect.What I intend on doing is writing a multi table valued function that takes two parameters, the key to the security group record and the key to an employee record. This function will then compare the criteria in the security group to the columns on the employee record and return a record if the criteria matches on the employee record as defined in the security group.With all this in mind, I am looking for advice on the pros and cons of using functions in this manner. I use functions all the time but only scalar functions for simplistic purposes. Or, if anyone has any other suggestions I am open to those as well. |
|
|
ajthepoolman
Constraint Violating Yak Guru
384 Posts |
Posted - 2003-06-10 : 15:02:02
|
| I wish we had done something like that. Instead we have placed the same snippet of code at the top of damn-near all the 5000+ stored procedures! Sometimes you can't see the big picture immediately.I don't know if it is the same thing you are using, but we check the permissions (it is really more a location setting) to determine what values get loaded into our combo boxes and what menus show on our software. So it is somewhat part of our overall security, but a very small portion of it.I honestly don't know if it is more efficient to have your code clumped into stored procedures or not. Since they are precompiled it might save some time in the long run. Does anyone else know what type of performance hit you take using functions?Overall, we use functions fairly extensively. Everything from calculating the number of days in a month to returning temp tables of viewable records tied to the user.Doubt this helps, but it burns some time!Aj |
 |
|
|
tfountain
Constraint Violating Yak Guru
491 Posts |
Posted - 2003-06-10 : 15:56:23
|
quote:
returning temp tables of viewable records tied to the user.
This sounds exactly like what we're going to do. |
|
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2003-06-10 : 18:30:50
|
Another option which I feel is very solid is to use SQL's built in Security and Roles. Coupled with VIEW this system is fairly easy to maintain and extremely flexible...1) Create Roles for the database2) Create SQL User accounts for each user or Role3) Add a column named SecurityGroup with a datatype of SYSNAME to each secured table (Employees). Enter the Role in this field that has access to each employee.4) Create a view.Example:create table Employees (EmployeeID INT NOT NULL PRIMARY KEY, SecurityGroup SYSNAME NOT NULL DEFAULT('Administrator'))GOInsert Employees values (1,'Administrator')Insert Employees values (2,'Administrator')Insert Employees values (3,'Restrict')Insert Employees values (4,'Restrict')goCreate view EmployeeSecureASSelect EmployeeIDfrom EmployeesWHERE IS_MEMBER(SecurityGroup) = 1Now every user just simply calls this view....Although it is a bit of inital work, it is extremely flexible... DavidM"SQL-3 is an abomination.." |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-06-10 : 19:38:13
|
| I agree 100% with David's suggestions about the views, even more so because you would need to make the table SELECTable in order to use the function method anyway, so the table is just as wide open as if you were not using any security at all. By using the view and locking the table down, no one can circumvent your security. |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-06-10 : 19:49:45
|
| I've done a similar sort of thing with temp tables and stored procedures.If you just want to check if the user is allowed to use a function then you can just exec an SP with a user_id and function_id and get back an output parameter.For your situation I've had it populate a temp table - would use a table variable now for small result sets which you don't want to index. If you only use it once in the SP may as use a function in the from clause of a query. If you use it many times still may want to populate a table variable.The nice thing about populating a temp table from an SP is that it gives more flexibility as some things aren't allowed inside a function - e.g. you can't log the call.Function will give more flexibilty over the view but will be less efficient - the view would be compiled with the query whereas the function will be called to generate a resultset (I think).I still have a few doubts about functions as they can cause some incorrect results if you try to include complex processing. And can cause row by row processing. Good for simple things though.Takes me a long time to be convinced by anything new.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2003-06-10 : 20:22:25
|
quote:
I still have a few doubts about functions as they can cause some incorrect results if you try to include complex processing. And can cause row by row processing. Good for simple things though.Takes me a long time to be convinced by anything new.
You ARE still pining for 6.5 aren't you ?  Damian |
|
|
|
tfountain
Constraint Violating Yak Guru
491 Posts |
Posted - 2003-06-12 : 11:46:08
|
quote:
Another option which I feel is very solid is to use SQL's built in Security and Roles. Coupled with VIEW this system is fairly easy to maintain and extremely flexible...
There are only a few problems with this with our implementation. First, an employee can fall into more than one group. Although this could be fixed by making a child table off employee we have millions of employee records. Everytime someone adds/changes/deletes a security group we'd have to evaluate the 22 columns from the employee table for every employee and add/delete records into this child table.Second, using the roles won't work for us, simply because our web pages access the database through our Visual Basic components (COM+). So the COM+ applications are configured to use specific creditials, thus every request looks the same to the database.Although I do like that idea! I just don't think we can use it with our implementation. |
|
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2003-06-12 : 18:59:53
|
quote:
First, an employee can fall into more than one group.
A Role can be "user" of another Role. ie Security Chain.In my example, Administrator would be a member of the Restrict group and thus have access to all those rows.quote:
Second, using the roles won't work for us, simply because our web pages access the database through our Visual Basic components (COM+). So the COM+ applications are configured to use specific creditials, thus every request looks the same to the database.
This can be achieved using Windows Security. The COM+ componenets can be set to use the Users contect. Every User is part of a Windows Group and then you would map the Windows Group to the appropriate SQL Role. As I said, this requires a fair bit initial planning...DavidM"SQL-3 is an abomination.." |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2003-06-12 : 19:56:15
|
| We ran into a performance problem with a particular UDF and I found this article that offered an explanation. http://www.sswug.org/see/14762 (You may need a login to read though)In a nutshell, the article points out that the optimizer assumes a UDF will return a single row. Because of this, it makes some bad decisions regarding the execution plan.I hope this is resolved in the next release of SQL Server because the table valued function is incredibly useful. I still use them quite frequently, but I have found that using a UDF can sometimes create performance headaches.-ecEdited by - eyechart on 06/12/2003 19:57:17 |
|
|
|
tfountain
Constraint Violating Yak Guru
491 Posts |
Posted - 2003-06-16 : 11:08:52
|
quote:
This can be achieved using Windows Security. The COM+ componenets can be set to use the Users contect. Every User is part of a Windows Group and then you would map the Windows Group to the appropriate SQL Role. As I said, this requires a fair bit initial planning...
This would work fine for the system users internal to our organization but our application is actually an ASP (application service provider - not active server pages ;)). Most of the users are actually clients external to our site (our clients), etc. Our application is accessed via the web using anonymous connections. Unless there is something I'm overlooking, this is not an option for us. |
|
|
|
tfountain
Constraint Violating Yak Guru
491 Posts |
Posted - 2003-06-16 : 11:14:50
|
quote:
...In a nutshell, the article points out that the optimizer assumes a UDF will return a single row. Because of this, it makes some bad decisions regarding the execution plan....
I went ahead with the function implementation and the performance difference is barely noticable. There are two functions and they both can return multiple rows. The first function can return anywhere from zero to a few thousand and the second will return a handful. But like I said, the performance difference is barely noticable even when doing large employee searches (like typing in the letter 'S' for a last name search).Edited by - tfountain on 06/16/2003 11:29:23 |
|
|
|
|
Seeking guru advice on pros/cons of functions...