Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
PlasticLizard
Starting Member
7 Posts |
 Posted - 2003-02-28 : 18:17:05
|
| I'm a bit stumped on a particular performance issue.My application has a relatively complex method for assigning role based permissions that take into account several fields in the my Users table, including Department, Company, Base and others. The query I use to determine if a user has permissions for a particular role is, while not terribly slow, not lighting fast either, but I use it absolutely everywhere, so even a moderately slow query won't be acceptable in the long run.Therefore my desire is to cache a mapping of users to permissions based on various user defined patterns and rebuild that cache every time the Users table or the PermissionGroupEnrollment table changes.The cache table would get quite large, so I don't really want to attach a trigger to Users and PermissionGroupEnrollment to rebuild the entire thing for a change on each row of either table, mostly because when the permissions table is updated from my .aspx page I'll update between two and a dozen rows, each update being a separate call to a stored procedure. To rebuild the cache table on each call would be, I think, extremely resource intensive.Is there ANY way to trigger the rebuilding of my cache from a function? My desire is to rebuild the cache on the first access to dbo.HasPermission(User,Group) after a change has been made. I would have the Users and PermissionGroupEnrollment triggers set a dirty flag in my variables table and rebuild the cache only if that flag was set when my dbo.HasPermission function is called. But functions can't alter the database to rebuild the cache. Is there any sort of workaround to this?Edited by - PlasticLizard on 02/28/2003 18:27:19 |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-02-28 : 18:29:58
|
| Awww, why did you get rid of the extra info? It was very helpful.My personal opinion, feel free to reject it: based on my experience with granting and denying permissions to stuff in a database, it's really not worth developing a system to do it when SQL Server already provides users, roles, and permissions on objects. It's really not that hard to utilize, and you can GUARANTEE that someone doesn't accidentally access something they're not supposed to. The only instance where you really need extra features would be row-level security, and even that can take advantage of existing users and roles in SQL Server. And you get all of this functionality for free, without needing to cache anything in the application.If you are already incorporating your permissions groups into actual roles, then I have no argument. But you'll have to provide more details, like actual table structures, queries, and situations where performance is not to your liking. As of now I have no idea what the function actually does, and I'd think that there are other means to accomplish the same thing without needing functions to do them. |
 |
|
|
|
|
|
|
|
Alter DB from function?