March 5, 2014 at 2:23 pm
Hello,
I am advocating that the DBA of my organization create a read-only, least-privileged account on the SQL server so that a group of data analysts can create ODBC connections on their PCs and run queries. One of the executives of my organization is wary of this because external auditors may object. Is a read-only SQL account considered a viable option for a security-conscious organization?
I think it would be more secure to have SQL Authentication (vs. Windows) because we don't need to create a windows domain account for access. Also, it fits the model I wish to follow where data analysts can run queries from their desks (after logging into our network).
Does anyone have opinions to offer about the security of such an account?
Thanks,
March 5, 2014 at 9:34 pm
First off I would recommend determining exact which data the analysts needed data to. Define this list clearly beforehand and obtain approval from the wary executive (who should be able to get approval from an auditor). In most cases (in my experience), as long as no one is messing around in financial data, there's usually very little issues with a READ ONLY account. However, I don't work for your company and no nothing about how things run...
It's quite simple for DBA to create a sql login, grant SELECT to specific tables/databases, or even grant datareader rights to specific databases. However, with that approach anyone with the password could use it to access the same information as your group. Windows authentication is easier to manage...and probably would be the route I would go for anything "long term". Your DBA or System Administrator could easily set up a "group", add the users who need access to it, then the DBA could grant that group access to the places needed.
As a DBA, my main concern would be the actual "queries" running on the environment and what overhead that could raise against my current hardware (i.e. poorly written queries causing high reads, scans, Cartesians, etc.) In large environments, this would be a concern from your DBA's perspective.
If your organization is security conscious, then why not request a backup copy to be restored to a different server, say on a VM host? Your DBA could then lock down the data and allow access to your group?
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
March 6, 2014 at 3:14 am
Hi cafescott,
It is very difficult to answer this because there is such little info about your environment, the area(s) your business operates in, the size of your estate (databases/instances/etc).
I guess my first question, as a DBA, to you would be 'are you the data owner?' If you're not, then I would say you need to get approval from the data owners first - the data owners are those business owners and not the technical owners.
With regard to using a SQL Login, are you intending to create a SQL login for each analyst or a shared login? If you want to share the login, then who will manage the password? Who will know the password? How long before the SQL Login and password get out into the wild (in the office at least)? How will you manage restricting people who previously had access (as an Analyst), but no longer require it because they have moved to a new role? Basically, I (as a DBA) would not be very happy about giving out a 'shared' SQL Login account - in fact, I would go as far as to say I would refuse to do it unless there were significant safeguards and absolutely no other way of doing it.
A better option would be to have a Windows Group set up with the Analysts' Manager as the owner. The Manager would have to approve each analyst to be a member of this group and ensure analysts are removed as required (ie, they have left the team, or moved on). It is possible for DBAs to monitor who are members of this AD Group and be alerted to any accounts being added without the Analysts Manager's approval.
With regard to having such access, I agree with MyDoggieJessie in regard to concerns on performance impact. I recall one place I worked where analysts were given ReadOnly rights to Production and we had intermittent degraded performance issues. After a bit of investigation, it turned out that a couple of Analysts had set up jobs on their PC's to harvest off large amounts of data to their 'local' copies of the databases. This was soon changed by management to the Analysts only having ReadOnly access to Production for limited periods, upon full signoff.
Why not have a copy of the DB(s) on a separate SQL Instances for Analysts to interrogate. This is a common method I have seen on my travels. Do they need up-to-the-minute data? This will decide on how you my achieve getting a separate copy for the Analysts to use.
In my view, Production databases are for Business Production work. If Analysts start using a production environment for analysis work and start to detrimentally 'affect' the performance of the Production environment, then they shouldn't be there, except for if it is for an emergency fix (including all the controls that go with such access).
HTH
March 6, 2014 at 6:51 am
Hi MyDoggieJessie and humbleDBA, I appreciate the replies. My goal is to enable a group of about 4 people to view data via ODBC from a government database, which actually is kind of small (i.e., less than 10K records in the largest table). I would be the person who keeps the password in this office. (However, I have no capability of creating the account on the production side.)
In a previous position where I was the DBA for a government entity, my organization was fine with my creating a read-only SQL account, then connecting to the database using it to enable us to view production data in MS Access. (I am trying to use ODBC for MS Access only because the rest of the team isn't very savvy with databases.)
In my present environment, a member of the IT department is resisting allowing any access to the database that is outside of the application he wrote. Unfortunately for the rest of the team, his application is very frustrating and doesn't solve the problems that are being encountered daily. So, the purpose of this thread post is to come up with an intellectual argument that will justify allowing us to see the data outside of his application.
I understand how repeated querying can slow down a production server. In this case, the database is small enough that I don't think it will be a problem.
The idea of creating a Windows group is interesting, but unless I'm mistaken, ODBC has no idea whether the given user is a member of it or not.
Thanks for the help.
March 6, 2014 at 7:40 am
cafescott (3/6/2014)
I understand how repeated querying can slow down a production server. In this case, the database is small enough that I don't think it will be a problem.The idea of creating a Windows group is interesting, but unless I'm mistaken, ODBC has no idea whether the given user is a member of it or not.
It's not the repeated querying that I would be concerned about, it's the potential for poorly written JOINS, WHERE clauses, etc that pose more of an issue, however in your situation with 10K max rows, I wouldn't worry much about performance (unless that is already a present issue).
Sounds like your best bet is to request a copy of the database, that way it's external to the existing application.
How would your analysts connecting to the database? SSMS? Access? Excel? Some other tool? In any case, the ODBC connection shouldn't be an issue either way. If your DBA creates a windows user (that's a group from AD - Ex: YOUDOMAIN\ReadOnlyUsers), then the users that connect to it will use their regular windows account, and SQL will authenticate them based upon the security group from AD.
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
March 6, 2014 at 7:57 am
Hi MyDoggieJessie,
The data analysts in question probably will be overwhelmed by SSMS. That's why I was trying to obtain access to the database via ODBC/MS Access. Otherwise, it is a great idea.
Thanks,
March 6, 2014 at 8:27 am
While you'd have to test it to be sure, I'm pretty sure if you created an ODBC connection to SQL Server via Access using the user's regular credentials, SQL server will authenticate it via the group from AD (which is the security group account your System Administrator would create for you containing all users who need the access). It should still work.
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
March 6, 2014 at 8:33 am
MyDoggieJessie, that sounds like a very cool idea. I will recommend this. Thanks for the reply!
March 6, 2014 at 1:21 pm
Cafescott,
I think you have multiple issues here that while you need to address them simultaneously, need to be approached independently.
The first is the physical security and access, which has already been addressed. Use an AD group, set the ODBC connection to use trusted, assign that group to a read only role on the database(s) and that's done.
The next is education of your users. Do they know how to write a query? SSMS or MSAccess, direct or passthrough, you're looking at educating your users on datatypes, joins, aggregation methodology, and a number of other tasks. Have you accounted for this? Above all else, my guess is this is why your dev is pushing back on you about letting the users run free at the data layer. Training time.
Another concern is auditing. What type of data is this? The volume of it doesn't matter. The file clerk shouldn't have access to the patient records no matter if there's five or five million records. Audit is audit. They don't care about volume, they care about exposure. If there is private information in these databases that shouldn't be exposed, consider using a set of views that either obfuscate or simply don't include columns with private or identifying information. That can satisfy audit while still allowing your analysts to work.
Finally, you have to look at why the application doesn't do what they want. Is it a simple fix? Is it a matter of compiling a few reports for an SSRS server and exposing them to your business without having all of the above need to be done? Would cubes and powerpivot make more sense? Handing the neophytes keys to the data has often caused me more trouble than it solved, when the solution was 2 days away with something I could translate from business into techese.
I wish you luck, this can either go really easily, or horribly wrong, depending on circumstances and the people involved.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
March 6, 2014 at 1:47 pm
We use views as the access point for users. (outside of company application) . This way we can limit the columns seen and just give readonly access to the views.
March 6, 2014 at 1:49 pm
+1 Kraig
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
March 11, 2014 at 12:14 pm
Hey [Evil] Craig, sorry for the delay in responding. I didn't check back on this thread after making my final argument. I agree with the AD approach, but it was rejected. (I think it was rejected because they decided to let me have a nightly backup of the production database to run queries against, and they weren't going to grant me two requests.)
Also, I lost my battle to have the novice users view the data in MS Access. (If Access has any useful purpose it is to let beginners look at information.) So, I'm wondering whether my users will be able to work with SSMS, or whether it will be too challenging for them.
Thanks for the input (once again--you've helped me in the past). Take care,
March 11, 2014 at 12:15 pm
djj, that's a good idea. I will likely make views for my novice users.
Thanks,
March 11, 2014 at 12:17 pm
It seems to work for us.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply