August 25, 2015 at 2:14 pm
Should general office users be allowed to perform SQL queries on a ERP database (or any database for that matter) when the ERP has a frontend application with reporting features
August 25, 2015 at 2:15 pm
No.
In general it raises some potentially bad issues.
1. General users will likely not know how to write proper queries, this can lead to 2 things, first performance hits and second now you end up having to support them when their queries don't work as expected.
2. Security issues, what exactly will they end up with access to and what potentially sensitive information should they not be seeing.
3. If they already have an interface to pull the information they need why do they need direct access to the DB? This leads to more user maintenance, and were you planning on installing SSMS on all their computers which now has to be maintained?
August 25, 2015 at 2:21 pm
That's generally a discussion between the DBA and management, but some key questions that come to mind...
1. Can they perform their job duties without said access or is this "just the way's it's been done before".
2. Do you have security/permissions set up appropriately. i.e. db_denywriter !!!
3. Are these "super-users" who have sql knowledge or just people who know how to write a SELECT statement.
As I said it's going to come down to a management decision, but the DBA can and should come prepared with reasons why it's not a good idea. Specificlly a poorly written query can (and usually does) have a significant impact on production.
** just my 2 cents, but I'd say "Hell No" and give reasons to support that.
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgAugust 26, 2015 at 3:51 am
Another vote for No.
It's a fight you're likely to lose unless you can provide alternatives. I'd suggest looking at log shipping to another server. If you can, get upgraded to 2014 and then use Availability Groups to provide a readable secondary. Nightly restores. Something to let them have their access, but not on your production server.
It's also worth noting, that you could be violating a ton of legal processes depending on what kind of data we're talking about. I would certainly bring up compliance and audits in this discussion. There are places where even the DBA isn't allowed to see the data in the database (good luck with that in SQL Server).
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 26, 2015 at 6:12 am
Thanks to ZZ, Jason and Grant
first and foremost queries were limited to previous IT manager. everything else was accessed by custom reports through SSRS, SharePoint, and the ERP which were either built by the previous IT manager or outsourced. So although I have had the courses in Admin SQL there was no reason to perform queries. New personnel mgr has some experience in querying so believes that this is good for the company. I don't agree. Personnel involved have had 1 day of query instructions. Loaded gun in my opinion. None of the users would fall in the class of superuser.
So I am gathering data for reasons against. Option to move to 2014 is not likely as the ERP is limited to stable operations under 2008.
Any others with comments. Since this is a one man operation, tech support and all, worse comes to worse they will need to contract a DBA to perform the ill advised magic.
August 27, 2015 at 7:23 am
Grant,
What would be the purpose of moving the query capability off of the production server?
Wouldn't that just still require granular permissions that would still apply elsewhere?
Looks like it would only prevent it from happening on the primary server.
Wouldn't they still be able to login to the primary with the same permissions by just changing the server names?
What configuration would be required to accomplish your suggestion?
As an aside, already been told it will not matter what my objections are.
August 27, 2015 at 7:44 am
ltibbs (8/27/2015)
Grant,What would be the purpose of moving the query capability off of the production server?
Wouldn't that just still require granular permissions that would still apply elsewhere?
Looks like it would only prevent it from happening on the primary server.
Wouldn't they still be able to login to the primary with the same permissions by just changing the server names?
What configuration would be required to accomplish your suggestion?
As an aside, already been told it will not matter what my objections are.
If you have a secondary server you can add additional security there that lets them access the server. It can be different than the production server. Also, depending on how you set up the secondary server, you can mask data. For example, we had to provide our developers with a database on which they could test out production problems. It was insisted that they have production data. However, legally, we couldn't let them see certain information. So, nightly, we ran a restore from our production database, cleaned the data in the database through UPDATE statements, then added the developers logins to that second database.
So, what did we get there? First, we got a test of our production backups, bonus. Second, we offloaded all the stupid stuff they might do to a server other than our production server. So if they accidently selected every row in the database in a Cartesian product to every other row, while that testing server might be offline, production chugged along perfectly. Third, they did have access to the data that they had a legitimate need for, but we were still able to protect our production environment.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 27, 2015 at 9:48 pm
Heh... you guys are all missing it. You should encourage all manner of ad hoc queries from the masses so that you can better justify a massive hardware and training budget not to mention all the cool toys to make SQL Server "run faster" even though it won't really help much. 🙂
--Jeff Moden
Change is inevitable... Change for the better is not.
August 28, 2015 at 7:25 am
Jeff Moden (8/27/2015)
Heh... you guys are all missing it. You should encourage all manner of ad hoc queries from the masses so that you can better justify a massive hardware and training budget not to mention all the cool toys to make SQL Server "run faster" even though it won't really help much. 🙂
LMAO
Don't give me any ideas, I could use some new tools. 😉
August 28, 2015 at 7:50 am
Jeff Moden (8/27/2015)
Heh... you guys are all missing it. You should encourage all manner of ad hoc queries from the masses so that you can better justify a massive hardware and training budget not to mention all the cool toys to make SQL Server "run faster" even though it won't really help much. 🙂
Can't we just give everyone the SA password too? Then we'll never even have to worry about permission issues!!!
August 28, 2015 at 8:05 am
Users with a little bit of knowledge are liken to a stick of dynamite in a match factory.:-D
August 28, 2015 at 8:50 am
ZZartin (8/28/2015)
Jeff Moden (8/27/2015)
Heh... you guys are all missing it. You should encourage all manner of ad hoc queries from the masses so that you can better justify a massive hardware and training budget not to mention all the cool toys to make SQL Server "run faster" even though it won't really help much. 🙂Can't we just give everyone the SA password too? Then we'll never even have to worry about permission issues!!!
Wait, you mean you guys don't use a blank SA password? Hmm.
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgAugust 28, 2015 at 8:57 am
The point is to run a business, not have a perfect environment for the db itself.
Yes, much care needs to be taken before allowing anyone to run ad-hoc queries. But blocking all access could be a very severe mistake as well. Don't lose sight of the real issue: what's best overall for the business.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
August 28, 2015 at 9:14 am
Scott, I agree with you that the point is to run a business. You raise a good point about perspective. It may be that cutting off all access could be bad for business, but so could having everyone with a computer having the ability to run whatever queries they want against the production database. It might impact performance...just a bit anyway.
Going back to a previous post, the DBA and management needs to have a serious conversation, focused on the needs and wants of the employees.
As for me, I'd vote no.
August 28, 2015 at 9:44 am
Based upon what was posted, the answer is no.
But you need to be prepared to provide the users the ability to perform their jobs.
If this is a separate database, such as Grant suggested, or something else, you will need to have this information.
The solution cannot be more difficult to the end users than the existing process.
You may be able to simplify their lives, and save your own sanity, by providing a series of views and grant these users select permissions to these views, and denying them everything else!
The views can filter or obfuscate sensitive data.
The users can type their queries and you can sleep easy.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
Viewing 15 posts - 1 through 15 (of 25 total)
You must be logged in to reply to this topic. Login to reply