SQLServerCentral / Editorials / SQLServerCentral.com / Why Use the Principle of Least Privilege? / Latest Posts

RE: Why Use the Principle of Least Privilege?

Revenant — Mon, 18 Apr 2011 12:44:44 GMT

MS contact info sent via private mail.

RE: Why Use the Principle of Least Privilege?

Revenant — Fri, 15 Apr 2011 11:21:12 GMT

[quote][b]Ninja's_RGR'us (4/15/2011)[/b][hr]Thanks a million. Any way I can provide more feedback if / when I find bugs? . . .[/quote]I pinged them and asked them for permission to give you their e-mail. As they are in Hyderabad, I would expect their reply by late Sunday.

RE: Why Use the Principle of Least Privilege?

Ninja's_RGR'us — Fri, 15 Apr 2011 10:57:09 GMT

[quote][b]Revenant (4/15/2011)[/b][hr][quote][b]Ninja's_RGR'us (4/12/2011)[/b][hr]I love MS & all since I'm earning my living because of them. BUT I work with ms Dynamics Nav and EVERY SINGLE FREAKING USER needs to be DBO.Granted this is the only db they have access to but this is a little moronic. And we're using the 2nd latest product version. So it looks like they still have some work to do there!& BTW, there's a open / design table right there in the application... which anyone has access to. Our contact told us there's no real way to change that.[/quote]I reported this as a P0 to their test lead.Thank you for bringing this up.[/quote]Thanks a million. Any way I can provide more feedback if / when I find bugs?The other issue with this is that we have no real way to do implementations.IE : I put db is single user mode to kick every one out. Backup, restore, checkdb. Put db in restricted user mode so our team can kick in (at which point I didn't know that every user now had access). Tell our consultant to start the upgrade process. In the middle of it we realize we have incorrect data. We trace it to users having logged back it and done transactions. We had to use restricted users because there was 3-4 of us in there to run the tests as fast as possible. We now had to constantly monitor the connections and keep killing them for 2 hours until we were done.Now the only safe way we have is to pay ultra-overtime for the consultants which they don't want to do anyways or run in single user and shut down the application for 3-6 hours... which means 300+ <wo>man hours lost.The correct way would be to have users in data reader_writer group and have a way to kick 'em of of the system when we need to.I don't mind giving dbo for the consultants since they actually need it most days. But even that could be improved.TIA.

RE: Why Use the Principle of Least Privilege?

Revenant — Fri, 15 Apr 2011 10:45:07 GMT

[quote][b]Ninja's_RGR'us (4/12/2011)[/b][hr]I love MS & all since I'm earning my living beause of them. BUT I work with ms Dynamics Nav and EVERY SINGLE FREAKING USER needs to be DBO.Granted this is the only db they have acess to but this is a little moronic. And we're using the 2nd latest product version. So it looks like they still have some work to do there!& BTW, there's a open / design table right there in the application... which anyone has access to. Our contact told us there's no real way to change that.[/quote]I reported this as a P0 to their test lead.Thank you for bringing this up.

RE: Why Use the Principle of Least Privilege?

SQLRNNR — Fri, 15 Apr 2011 10:34:43 GMT

I think this is a great reminder that the effort to prevent injection is a continual process.

RE: Why Use the Principle of Least Privilege?

Jim Murphy — Wed, 13 Apr 2011 09:30:04 GMT

[quote][b]K. Brian Kelley (4/12/2011)[/b][hr]In the BBS days...[/quote]I remember those days! I ran a very small BBB back in the 80's. Those were the days. Anyway, what you describe is correct. The concept of whitelist vs. Blacklist. Whitelist being more secure and ensuring the characters or patters match an exact allowable list only, vs. a blacklist which is less secure and looks for characters not allowed. Blacklist being less secure because hackers are always adapting and changing and even if you blacklisted all of the bad chars/patterns today, it may be vulnerable tomorrow via a new yet-to-be-invented construct.On the coding side, I advocate whitelist, and on an exception, attempt to blacklist sanitize the input (replace), then run it through the whitelist check one last time. This tends to prevent the really bad stuff, even if not invented yet (usually), while not doing a smack down on the users ETL process, etc.Anyway, you made some nice points.Jim

RE: Why Use the Principle of Least Privilege?

Michael Valentine Jones — Tue, 12 Apr 2011 11:41:02 GMT

There are fairly simple steps that you can use to eliminate the vast majority of SQL Injection attacks:Always Use Parameters. Even if you don't use Stored Procedures. [url]http://weblogs.sqlteam.com/jeffs/archive/2006/07/21/10728.aspx[/url]

RE: Why Use the Principle of Least Privilege?

Ninja's_RGR'us — Tue, 12 Apr 2011 10:50:25 GMT

I love MS & all since I'm earning my living beause of them. BUT I work with ms Dynamics Nav and EVERY SINGLE FREAKING USER needs to be DBO.Granted this is the only db they have acess to but this is a little moronic. And we're using the 2nd latest product version. So it looks like they still have some work to do there!& BTW, there's a open / design table right there in the application... which anyone has access to. Our contact told us there's no real way to change that.

RE: Why Use the Principle of Least Privilege?

Steve Jones - SSC Editor — Tue, 12 Apr 2011 10:46:09 GMT

[quote][b]Eric M Russell (4/12/2011)[/b][hr]Just for clarification, I didn't intend to compare fellow developers who occasionally code ad-hoc SQL to those other guys (idiots) who make u-turns on the interstate highway. It's not the same thing, and I myself have been in situations where I've had to resort to coding ad-hoc SQL to solve some problem like pivoting or complex filtering that would still require dynamic SQL even if it were done in a stored procedure. Even in that case, I'll grant the application account select permission on only the specific tables it needs. It was just that I think interstate dividers are a good non-IT example of implementing the Principle of Least Privilege.:-)[/quote]I didn't take it as a "developers suck" as much as "people will take short cuts if they can".

RE: Why Use the Principle of Least Privilege?

Steve Jones - SSC Editor — Tue, 12 Apr 2011 10:44:30 GMT

[quote][b]SanDroid (4/12/2011)[/b][hr]Steve,Great article and wonderful graphic. One question I have always had about this new Policy of least privilege that M$ started pushing for in XP is this.Are internal or supported products (like Install Shield, Remedy, Office, IE) following all these rules, or are they given exceptions that allow thier products to still work?There are some products that we have developed in house where we work that allow us to seamlessly update everyone to the latest version of our current software. With these changes, our only answer from M$ has been to purchase a partners product that does a limited what we want to do.Don't get me wrong, I understand the need for this change in security and code execution levels and all the issues it resolves. I would like to see better support from M$ when it breaks things that are working using previous design patterns they had provided. [/quote]I doubt that many of the MS products adhere to this, though a lot of that is different departments coding to meet their deadlines, without necessarily following some standard. I think a lot of this changed in the 2003-2004 time frame when MS stepped back, stopped development of some products like SQL Server, and re-engineered their processes to be more rigid, standard, and secure.I definitely agree that MS has not necessarily been a great advocate here if best practices for security and incorporating that into their own development cycles.

RE: Why Use the Principle of Least Privilege?

Eric M Russell — Tue, 12 Apr 2011 09:19:54 GMT

[quote][b]Steve Jones - SSC Editor (4/12/2011)[/b][hr][quote][b]Eric M Russell (4/12/2011)[/b][hr]It's just like those big concrete dividers that the department of transportation puts between the east and west bound lanes of an interstate highway. If the barriers were not there, idiots would of course be routinely doing U-turns in front of oncoming traffic.[/quote]That's a great quote, and a nice analogy. :-D:-)[/quote]Just for clarification, I didn't intend to compare fellow developers who occasionally code ad-hoc SQL to those other guys (idiots) who make u-turns on the interstate highway. It's not the same thing, and I myself have been in situations where I've had to resort to coding ad-hoc SQL to solve some problem like pivoting or complex filtering that would still require dynamic SQL even if it were done in a stored procedure. Even in that case, I'll grant the application account select permission on only the specific tables it needs. It was just that I think interstate dividers are a good non-IT example of implementing the Principle of Least Privilege.:-)

RE: Why Use the Principle of Least Privilege?

SanDroid — Tue, 12 Apr 2011 08:40:21 GMT

Steve,Great article and wonderfull graphic. One question I have always had about this new Policy of least privilidge that M$ started pushing for in XP is this.Are internall or supported products (like Install Shield, Remedy, Office, IE) following all these rules, or are they given exceptions that allow thier products to still work?There are some products that we have developed in house where we work that allow us to seemlessly update everyone to the latest version of our current software. With these changes, our only answer from M$ has been to purchase a partners product that does a limited what we want to do.Don't get me wrong, I understand the need for this change in security and code execution levels and all the issues it resolves. I would like to see better support from M$ when it breaks things that are working using previous design patterns they had provided.

RE: Why Use the Principle of Least Privilege?

K. Brian Kelley — Tue, 12 Apr 2011 08:28:24 GMT

[quote][b][Jim].[dba].[Murphy] (4/11/2011)[/b][hr]RegEx patterns can go a long way to help, but are usually implemented in the application/business layer, causing DBAs to depend on these faithful developers to protect our own systems. Uh. That's not good. [img]http://www.sqlservercentral.com/Forums/Skins%5CClassic%5CImages/MessageIcons/W00t.gif[/img] Triggers? Policies? Functions/Stored Procs? What is YOUR preferred method of sanitizing user input (as brokered via a developer)? I tend to lean toward driving the developers through a database 'interface' via stored procs using parameters (and not the @whereClause or @orderBy kind of parameters!).Jim[/quote]Unfortunately, I'm going to have to go back to leaning on the developer. Here's why:1 - Attackers started using SQL injection to insert links to malicious sites to appear on legitimate web pages. Most of these were automatically opened using javascript. We built filters that looked for those sites via triggers.2 - Attackers started using tinyurl and bit.ly and other link shortening sites as a counter to our efforts. We responded by looking for javascript and a href in our triggers.3 - Attackers started doing weird things like running convert statements to change the appearance of the code. We deployed (modified) our triggers to stop that but there are many, many techniques and we couldn't get them all.4 - Attackers started using legitimate mechanisms within javascript to obfuscate what was being done and, combined with #3, we couldn't trap for that, even using a RegEx.In the BBS days, we had a philosophy when we built door applications. We would define the valid inputs that were permitted (the domain, if you will, from mathematics). If it didn't fit that domain, the input was rejected. This is harder when you're trying to do text fields and the like, but not undoable. And once you've properly defined one input check for this, it's easy to re-use. That goes back to Steve's comment about Andy always speaking on patterns and practices. Once you write the check routines you need, you can re-use them, saving you time and work. And you deploy it at the app level so we never see it at the database level, because by the time it gets to us, we might not be able to do anything about it.

RE: Why Use the Principle of Least Privilege?

Steve Jones - SSC Editor — Tue, 12 Apr 2011 08:21:15 GMT

[quote][b]Eric M Russell (4/12/2011)[/b][hr]It's just like those big concrete dividers that the department of transportation puts between the east and west bound lanes of an interstate highway. If the barriers were not there, idiots would of course be routinely doing U-turns in front of oncoming traffic.[/quote]That's a great quote, and a nice analogy. :-D:-)

RE: Why Use the Principle of Least Privilege?

Eric M Russell — Tue, 12 Apr 2011 07:50:02 GMT

In any database that I have ownersip of, I typically grant application accounts authorization by adding them as members of a single role, and that role grants them only EXEC permission on the stored procedures that the application needs. If an account can't select from any table and can't view the schema information, then that severely limits how it could be leveraged by a SQL injection attack or stolen login credentials. I do have a separate "power user" account in the development and QA environment that allows developers or testers to browse object schemas or select from any table. In the Development environment, it even grants them permission to insert / update / delete any table, so they can perform and cleanup after unit tests. However, that account doesn't get carried over to production.This should be done, not just a means of providing security from external attacks, but also as a means of constraining how the database is being used by application developers. If they want to query the database in a certain way, rather than coding an ad-hoc SQL statement, they should instead run it past the database developer or DBA, who then creates the proper stored procedure. It's just like those big concrete dividers that the department of transportation puts between the east and west bound lanes of an interstate highway. If the barriers were not there, idiots would of course be routinely doing U-turns in front of oncoming traffic.

RE: Why Use the Principle of Least Privilege?

Evil Kraig F — Tue, 12 Apr 2011 01:10:25 GMT

I'm currently on the dev end of a bad little duel between the dba and dev sides of the house. Acquisitions and the like have forced some of it, pride and bad methodology others. Where I'm at now used to be a cowboy shop. QA was barely sneezed on as you passed the salad bar by for the meat of production. They'd do four rolls in a day if they had to, to get it right. Problem was our users/UAT is now used to only working off production to check production changes. Argh. Needless to say along the way they royally shot themselves in the foot, to CEO level of notice.Mind you, I came in after the fact. Now they've got full change controls in place and I'm trying, from the underbelly, to convince the rest of this dog to wag its tail. They're incredibly frustrated at not being able to run amok. Now, I'll admit, not being able to pull up sysprocesses when something I'm optimizing isn't making sense (alright, Mr. Proc, just what ARE you doing?) or doing a trace can get frustrating, but it's proper build methodology.I *know* this from both sides, and am constantly defending the people who have chained me to the table... and that's what it feels like. Imagine someone who's never worked the other side of the data house. It's miserable for them. All a DBA can do is commiserate, explain, teach... and remember not to leave the keys out.

RE: Why Use the Principle of Least Privilege?

Jim Murphy — Mon, 11 Apr 2011 22:50:25 GMT

HA! That graphic is priceless.It is all too easy to begin the development process by configuring the application to use "sa" (or another sysadmin account), and even demo using "sa" while stating the excuse that "you shouldn't use sa in production" (the proverbial "Do as I say, not as I do"). Frankly, we shouldn't use "sa" in development either. In fact, for a user to gain access to a system, they need both a username and a password, therefore disabling sa is best since it removes both halves of the equation. Following the more-secure best practice of using only Windows authentication doesn't help much if we set up using MYCOMPUTER\ADMINISTRATOR (which is a SQL sysadmin) or equivalent. Therefore, this applies to any other login, SQL or Windows, that has the sysadmin server role. This is a nice reminder to begin the development process with a lower privileged user account so we never end up rolling out an application into production which is a sysadmin.RegEx patterns can go a long way to help, but are usually implemented in the application/business layer, causing DBAs to depend on these faithful developers to protect our own systems. Uh. That's not good. [img]http://www.sqlservercentral.com/Forums/Skins%5CClassic%5CImages/MessageIcons/W00t.gif[/img] Triggers? Policies? Functions/Stored Procs? What is YOUR preferred method of sanitizing user input (as brokered via a developer)? I tend to lean toward driving the developers through a database 'interface' via stored procs using parameters (and not the @whereClause or @orderBy kind of parameters!).Jim

Why Use the Principle of Least Privilege?

Steve Jones - SSC Editor — Mon, 11 Apr 2011 21:29:53 GMT

Comments posted to this topic are about the item [B]Why Use the Principle of Least Privilege?[/B]