December 2, 2009 at 11:38 am
Seth,
Hate to break the news to you, but if they haven't already figured that trick out, they're either new to being T-SQL writer or they're not a very good one.
That's not to say that they SHOULD be doing that sort of stuff. But usually, that's one of the first tricks someone figures out. And any DBA who doesn't watch out for that sort of shenanigans isn't doing their own job correctly.
December 2, 2009 at 12:27 pm
I think it depends on how much you have to deal with permissions. In most of my environments, permissions have been either 'all' or 'nothing' on the database itself, with only 1 or 2 people having the 'all'. I went years without knowing how all of that worked, but now that I think about it, I did learn it pretty quickly after I started actually caring about locking down user accounts.
Any experienced DBA working in an environment that required a lot of user account control would know it for sure, but I don't know how aware the general ever-growing mass of people that work with databases are of it.
December 3, 2009 at 8:22 am
Elliott W (12/2/2009)
I am a little leary of putting the sprocs in different schema from the tables they use. My main reason is tied to not wanting to grant any more permissions than are absolutely required..CEWII
There's actually no reason from a security perspective to not have procs in a different schema than the tables. There are several ways to work it so you don't have to grant extra permissions:
1. If the 2 schemas have the same owner than ownership-chaining will still work. See this blog post.
2. You can use EXECUTE AS in the procedure. See this blog post.
3. You can use Module Signing. See this blog post.
I would actually put my reporting procedures in a different schema so I could just grant exec rights on the schema.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
December 3, 2009 at 9:10 am
Jack Corbett (12/3/2009)
Elliott W (12/2/2009)
I am a little leary of putting the sprocs in different schema from the tables they use. My main reason is tied to not wanting to grant any more permissions than are absolutely required..CEWII
There's actually no reason from a security perspective to not have procs in a different schema than the tables. There are several ways to work it so you don't have to grant extra permissions:
1. If the 2 schemas have the same owner than ownership-chaining will still work. See this blog post.
2. You can use EXECUTE AS in the procedure. See this blog post.
3. You can use Module Signing. See this blog post.
I would actually put my reporting procedures in a different schema so I could just grant exec rights on the schema.
Threads like this is the reason for the security section in SSRS Web.Config which looks like Microsoft is asking did we leave out any security context?
Execute as will not run the types of reports I have developed and most security issues comes up because companies runs reports in production databases which is a very high risk for performance and users access.
In banks developers code go to QA, UAT and then the data team which either deploys or rejects the code which means the code will be checked for tasks performed.
If a DBA stops intermediate inserts which are needed in most complex reports the DBA will write the code to create that report because there are reasons why SSRS is free.
Kind regards,
Gift Peddie
December 3, 2009 at 9:25 am
Gift,
I'd be interested in knowing what you are doing in the stored procedures that make it so you can't use EXECUTE AS. I'm not saying you should, I'm just asking what doesn't work.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
December 3, 2009 at 9:35 am
Jack Corbett (12/3/2009)
Gift,I'd be interested in knowing what you are doing in the stored procedures that make it so you can't use EXECUTE AS. I'm not saying you should, I'm just asking what doesn't work.
Reports that calculate cycle time in a factory, track a part through the factory to the time of completion of that part. Government required reports that tracks product life cycle and Government benefit tracking, most of these are completed with very long stored procedures or many stored procedures.
Simpler solution shared datasource through Enterprise Library and users permissions are defined either in an application or ReportManger.
Kind regards,
Gift Peddie
December 3, 2009 at 10:23 am
So it's not that Execute perms don't work, but that by the rules you can't use them?
December 3, 2009 at 10:27 am
Gift Peddie (12/3/2009)[hrIf a DBA stops intermediate inserts which are needed in most complex reports the DBA will write the code to create that report because there are reasons why SSRS is free.
I'm not quite sure I understand this, but I'm going to give it a shot.
We're not trying to prevent the use of Temp tables in report code. We've even set up TempDB permissions so that the report writers can utilize them as necessary for more complex reports.
That being said, reports should NEVER (to my mind) alter or create data in the database. Reports should only be telling the users, in a pretty way, what data already exists in the database. ETL and transactions and all that good stuff, even month end processing, does not count as reports in my mind. A report is a screen or piece of paper that gives you a point in time snapshot of what the data looks like. Whether that PIT is the previous month end regardless of what day you run it or the PIT is Right Now, is beside the point. It's still just a looksee into the data, not an actual change to the data.
December 3, 2009 at 10:38 am
That being said, reports should NEVER (to my mind) alter or create data in the database. Reports should only be telling the users, in a pretty way, what data already exists in the database. ETL and transactions and all that good stuff, even month end processing, does not count as reports in my mind. A report is a screen or piece of paper that gives you a point in time snapshot of what the data looks like. Whether that PIT is the previous month end regardless of what day you run it or the PIT is Right Now, is beside the point. It's still just a looksee into the data, not an actual change to the data.
Reports help small banks increase loan volume, when FDA rejects a drug reports provides the data, reports provides the cost of a product manufacture. Your concept of report is what end user tools do not what developers write. I have seen reports more complex than applications that created the data to create the report.
Report database and production database are generally two separate views of the company data.
Kind regards,
Gift Peddie
December 4, 2009 at 4:10 am
Gift Peddie (12/3/2009)
Your concept of report is what end user tools do not what developers write.
Gift, I'm sorry. I don't understand that sentence at all. Could you rephrase? Because the rest of what you're saying seems to agree with my statement and yet, somehow I get the impression you're disagreeing.
I just don't understand what you're disagreeing about.
December 4, 2009 at 10:45 pm
I just don't understand what you're disagreeing about.
I am not agreeing with you because I have never developed reports that presents existing data in a pretty way. Developed reports in most cases generate new data through parameters, aggregation or some required base math depending on the industry. Developed reports transform business process, creates new marketing and sales tools. It is aslo reports that show fraud in government benefits which includes the providers.
Kind regards,
Gift Peddie
December 8, 2009 at 5:56 am
WHOOHOO!
Found the workaround.
Create a new database called ReportDesign. Give the report writers whatever perms they need on that database.
Give db_datareader and db_denydatawriter on the "source" databases (from which the reports will come).
All procs are written in ReportDesign. Doesn't matter if they put inserts & updates into the proc or not. Because db_denydatawriter exists on the source DB and no Alter Schema and Create Procedure perms are on that database, the proc won't execute correctly when they try to run it.
HappyHappy!
December 8, 2009 at 6:01 am
Gift,
I have an end user who does what you're describing. But in the end, no matter what numbers he plugs in to come up with his scenarios, he's still basing everything off existing data and massaging numbers to present his graphs, charts, predictions, etc. Even when he comes up with "new data," it's based off the old.
The process in which people do these sorts of things is not, IMHO, a report. It is an ETL. A report is only the final piece of the process.
But, I guess we'll just have to agree to disagree on this one.
Viewing 13 posts - 16 through 28 (of 28 total)
You must be logged in to reply to this topic. Login to reply