| Author |
Topic |
|
TSQLMan
Posting Yak Master
160 Posts |
 Posted - 2003-09-19 : 10:14:42
|
| I am working on putting a web interface to an enterprise reporting system developed by my predecessor. When I looked at the report security, I noticed every report in the application had a SQL Role, and he used a stored procedure the verify that the user_id existed in the role. Is it just me or is this a bad way to do it. Furthermore what other suggestions would you have as to how to approach this issue. Thanks,TSQLMan |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-09-19 : 12:12:57
|
| You can create SQL roles to manage a collection of users. If you have different permissions required for different groups, then you need to create a role for each of these, such as an admin role or a public role. It probably is a good idea that he checks if the user exists in the role that way the user doesn't get an error message about permission denied. But then again, the system could check for that error and then notify the user about not having the correct permissions to run the report.Tara |
 |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-09-19 : 12:16:51
|
quote:
I noticed every report in the application had a SQL Role
I don't think I understand that. Could you explain further.Also, What type of security model do you have set up?SQL Login? Connection Pooling? NT Authentication (what's it called in 2000?)Do you have application level security as well?Brett8-)SELECT @@POST=NewId()That's correct! It's an AlphaNumeric! |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-09-19 : 12:19:00
|
| I think he means exactly what the statement says. For every report, a SQL role was created, most likely with different permissions for each. I'm sure that there are duplicates though or ones that could be combined.Tara |
|
|
|
TSQLMan
Posting Yak Master
160 Posts |
Posted - 2003-09-19 : 12:35:47
|
Bingoquote:
Originally posted by tduggan
I think he means exactly what the statement says. For every report, a SQL role was created, most likely with different permissions for each. I'm sure that there are duplicates though or ones that could be combined.Tara
|
|
|
|
TSQLMan
Posting Yak Master
160 Posts |
Posted - 2003-09-19 : 13:02:03
|
That is what he is doing, but I have always tried to keep as few roles as possible on a database. How much of a performance issue is having all those rolls. Somewhat in my predecessors defense, our reporting is kept very secure. We do have groups, and matching rolls, but there are a lot of reports that are maybe one or two users who are permitted access to them. Our corporate umbrela has somewhere around 30 companies. quote:
Originally posted by tduggan
You can create SQL roles to manage a collection of users. If you have different permissions required for different groups, then you need to create a role for each of these, such as an admin role or a public role. It probably is a good idea that he checks if the user exists in the role that way the user doesn't get an error message about permission denied. But then again, the system could check for that error and then notify the user about not having the correct permissions to run the report.Tara
|
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-09-19 : 13:04:36
|
| Having a lot of roles is not a performance issue at all. At my last job, we had to create a role for every division in a government agency that was going to use this application. I do not remember how many roles that we had, but there were a lot of them.Tara |
|
|
|
TSQLMan
Posting Yak Master
160 Posts |
Posted - 2003-09-19 : 13:07:11
|
| Tara, Brett,Thanks, |
|
|
|
|
Report Security Verification