If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

Originally posted here by catch I agree, as a rule (high assurance and very low assurance applications aside) this method of role based security is the most beneficial with the lowest cost of upkeep.

One user per application function is really just an attempt at obfuscation, a hole in your webapp isn't fixed if it ends up only being able to be done in specific areas through such restrictions. If your app needs this information, and should EVER have to update database data with your input from the web, you are just attempting to obfuscate where it can be done, and are not actually properly writing your code. Input sanitization is far and away the superior method of dealing with SQL injection problems. Multiple DB users per app is essentially a way to make your code longer while not providing any decent returns.

The issue here is if user authentication information is used to encrypt their provate data, these keys will be lost if the authentication information is reset. (Just like with EFS) resolutions for this are numerous (including my favorite, a completely seperate authenication recovery system that stores user passwords in a recoverable way and uses them to generate new passwords).

Again, doubling up on things for minimal gain. This also does not address indexing and so forth. Secure applications are worthless in the business environment if they do not perform well.

Sorry catch, considering you only really demonstrate one actual change to programming that could be beneficial, and then toss in a few concepts that may or may not be even able to be properly implemented, I'd have to give you a B for effort and about a D for implementation. There are many other things you could have covered about input sanitization (for instance, SQL-side stored procedures, PEAR:B's Prepared Statements, etc) that would help people. Perhaps you could continue this with an eye towards expanding it?

Chris Shepherd
The Nelson-Shepherd cutoff: The point at which you realise someone is an idiot while trying to help them.
\"Well as far as the spelling, I speak fluently both your native languages. Do you even can try spell mine ?\" -- Failed Insult
Is your whole family retarded, or did they just catch it from you?

One user per application function is really just an attempt at obfuscation

RBAC is obfuscation now?

This also does not address indexing and so forth. Secure applications are worthless in the business environment if they do not perform well.

Again indexing is useless when a given user will never access more than a single row.

Sorry catch, considering you only really demonstrate one actual change to programming that could be beneficial, and then toss in a few concepts that may or may not be even able to be properly implemented

The idea was not to give many practical examples as that would have really extended this beyond a useful length for this medium. I was merely attempting to introduce readers to this concept that is quite effective and when all three are used, they provide a very effective solution to upkeep-free security (to not just SQL Injections) for existing (especially hosted, free, and open development) installations.

There are many other things you could have covered about input sanitization (for instance, SQL-side stored procedures, PEAR:DB's Prepared Statements, etc) that would help people. Perhaps you could continue this with an eye towards expanding it?

I wanted to avoid these topics for several reasons:
They tend to be not possible on hosted solutions or for existing applications, and this is a "shoestring" tutorial after all.
Sanitation techniques can widely vary and as I said to slarty are especially difficult to implement in a centralized manner within an existing application which may already use spotty and varying sanitation.
These concepts extend to all RDBMS and application development tools, not just PHP. (which was selected for the reasons I stated in the original entry and nothing more.)

Least privilege and role based security are good concepts and can minimalist the impact of an otherwise successful attack. SQL injection was merely a simple example of those.

I'd just like to mention... Perhaps the title of the tut isn't the best. I feel that the whole concept isn't specifically to defend against SQL injection. Giving your application roles for it's functions to work under can't be a bad idea and isn't hard to implement at all. I think we agree that every function() or user isn't meant to have another account, just more general accounts to work under (I.E. this should never have to insert, that should never have to grant).

I would never have a web app without a filtering layer, but I wouldn't have it without some sort of way to protect itself from the unknown either. He's addressing a more general layer of security.

What catch wrote about is not a direct answer to SQL injection... it's how a good design can mitigate risk. Catch, I think a title change would make the tut more agreeable, do you?

A title change might be ok, but if I titled it something about least privilege or role based security it would have been more fitting to use OS security rather than SQL+web app security (which I felt was a far simpler example).

Sometimes difficult to find that balance between general enough to be useful in the widest variety of situations yet focused enough to demostrate a concrete context. I can tell that I've failed in this as two people now have replied thinking this is a tutorial for writing new web applications using PHP that are immune to SQL injection attacks. (Sorta like all those people who think "The Thin Red Line" is about WWII.) If this were such a tutorial, it would be very flawed and incomplete.

When implemented application-side the way you suggest, yes, and I explained why above. It limits where exploitation can occur, but it doesn't prevent it from occurring. It's just an attempt to hide a problem, not fix one.

Again indexing is useless when a given user will never access more than a single row.

Indexing is designed for finding one or more records in a huge list of records, and doing so quickly. When you ask a database server to find record 345678 of roughly a couple million, it has to be fast. Perhaps on a small scale application this might be true, but if you are talking about actual business applications for medium to large businesses, you will hit these marks easily, even with simple marketing databases.
Are you suggesting that to accomodate these 'security measures', programmers should toss out scalability?

I wanted to avoid these topics for several reasons:
They tend to be not possible on hosted solutions or for existing applications, and this is a &quot;shoestring&quot; tutorial after all.

Many hosting providers give you SQL access and all rights on your own database, so I don't see how either of my suggestions are "not possible on hosted solutions". As for existing applications, your suggestions themselves don't help at all, so if you are rewriting everything from scratch or re-developing parts of an application already, why not invest the time and do it properly?

Sanitation techniques can widely vary and as I said to slarty are especially difficult to implement in a centralized manner within an existing application which may already use spotty and varying sanitation.
These concepts extend to all RDBMS and application development tools, not just PHP. (which was selected for the reasons I stated in the original entry and nothing more.)

If an existing application needs redevelopment, it should be done properly, not in a convoluted half-cocked concept hack that might help in specific circumstances.

Originally posted here by Soda_Popinsky I'd just like to mention... Perhaps the title of the tut isn't the best. I feel that the whole concept isn't specifically to defend against SQL injection. Giving your application roles for it's functions to work under can't be a bad idea and isn't hard to implement at all. I think we agree that every function() or user isn't meant to have another account, just more general accounts to work under (I.E. this should never have to insert, that should never have to grant).

Yeah, I understand what he wrote, I made that very clear. It still doesn't give you an increase in security, for the reasons I explained above. All you are doing is limiting where attacks can hit. You aren't stopping them from coming in. Apparently this concept is somewhat difficult to put across.

I would never have a web app without a filtering layer, but I wouldn't have it without some sort of way to protect itself from the unknown either. He's addressing a more general layer of security.

What catch wrote about is not a direct answer to SQL injection... it's how a good design can mitigate risk. Catch, I think a title change would make the tut more agreeable, do you?

A title change doesn't fix a flawed premise. The premise here is that by limiting what each group or set of functions in a webapp can do on the database, you are increasing your security. The fact of the matter is, if cracker A can break in via your user account management system, but not via the authentication mechanisms, then they will focus their efforts there. You are not actually getting yourself any additional security, nor are you treating the actual problem. You are merely treating one of the symptoms of the greater problem -- you have a faulty piece of code that doesn't properly sanitize its input.

Chris Shepherd
The Nelson-Shepherd cutoff: The point at which you realise someone is an idiot while trying to help them.
\"Well as far as the spelling, I speak fluently both your native languages. Do you even can try spell mine ?\" -- Failed Insult
Is your whole family retarded, or did they just catch it from you?

It limits where exploitation can occur, but it doesn't prevent it from occurring.

That is all RBAC/Least privilege ever does. If application X cannot write to directory Z, but application Y can... does this mean there is no point in making application X not be able to?

Are you suggesting that to accomodate these 'security measures', programmers should toss out scalability?

One more time, repeat after me... this is a "shoestring tutorial." It doesn't deal with huge databases or issues of dramatic scalability. It was just intended to present a concept. Why is that so difficult for you? Beside, the table can/most likely would use a plain text row for simple administrative identification and indexing.

As for existing applications, your suggestions themselves don't help at all, so if you are rewriting everything from scratch or re-developing parts of an application already, why not invest the time and do it properly?

First off, this technique can be applied to something like PHP-nuke in less than half an hour, and perhaps even faster if a module was developed. As far as I can tell, there is no faster/cheaper way to more or less universally reduce the risk of SQL Injection attacks.

If an existing application needs redevelopment, it should be done properly, not in a convoluted half-cocked concept hack that might help in specific circumstances.

Hmm, well pretty much all web applications and all applications in general are in need of redevelopment, least they are likely to suffer a security problem in the future.
So I guess any patch could be considered "a convoluted half-cocked concept hack that might help in specific circumstances." Is this to mean that you are against patching, even patching like this that resolve known and unknown attacks preemptively? Everything should just be rewritten at the first sign of trouble?

It still doesn't give you an increase in security, for the reasons I explained above. All you are doing is limiting where attacks can hit. You aren't stopping them from coming in. Apparently this concept is somewhat difficult to put across.

How is this different than ANY security concept? Discretionary Access Controls: Do not prevent exploits from occurring, DAC merely limits the scope of the exploit to the given UID.Mandatory Access Controls: Do not prevent exploits from occurring, MAC merely limits the scope of the exploit to the given label.Role Based Access Controls: Do not prevent exploits from occurring, RBAC merely limits the scope of the exploit to the given role.Domain Based Access Controls: Do not prevent exploits from occurring, DBAC merely limits the scope of the exploit to the given domain.Least Privilege: Does not prevent exploits, it just minimalizes the power the exploited application can pass on to the attacker.Type Enforcement: Does not prevent exploits from occurring, TE merely limits the scope of the exploit to a given type.Network Flags: Do not prevent exploits from occurring, they merely limit the scope of the exploit to access granted to a given port.Loss of privilege on Fork: Does not prevent exploits from occurring, it merely removes the rights from the new process.A Reference Monitor: Does not prevent exploits from occurring, it merely ensures the exploited application stays within it's exiting security constructs.

Not a lot of point in going in to audit security, covert channel security, role rotation, or even encryption (which doesn't prevent exploits, it merely makes and disclosed data more difficult to use).
So what kind of security does prevent exploits from occurring... I see nothing in ISO15408 or DOD5200.28-STD on the subject...
Filtering tools and data sanitation tools are useful, but they have one serious drawback: If you have the time and money to map every possible pattern that might be submitted to an application during its lifetime, you are better off implementing other high assurance techniques. Frequently filtering tools are just slapped in as cheap, short term fixes, which explains their absence from the two primary security standards as well as every high assurance (military, financial, R&D) system I've ever come across.
That said, sure a filtering tool is useful for simple web application development, but that seemed a far less interesting and less universal of a solution, so I chose to not write about it.

A title change doesn't fix a flawed premise. The premise here is that by limiting what each group or set of functions in a webapp can do on the database, you are increasing your security.

Read above, or are you suggesting that every other security type is moot? because you know, if every application was perfect, there would be no need for security. How many perfect applications have you created?

The fact of the matter is, if cracker A can break in via your user account management system, but not via the authentication mechanisms, then they will focus their efforts there

How is this different than on a system with data sanitation? In my system, unless the attacker has access to each users password, the user account management system is useless to them. And thanks to least privilege only one, very small script would have access of any kind other than SELECT to the user database. Unlike your approach where all the scripts retain this access.

The problem here is that you suffer from the flawed assumption that the best way to security is to prevent all exploits. This is, at best impractical (especially when using third party software), the goal of security should then be to minimalize the effects of a given exploit to reduce the damage it can inflict.
I do not wish to argue this point with you further, if you have more to say on the subject please direct your responses to TPEP@gibraltar.ncsc.mil, I am sure they would love to hear how you feel that 40 years of their efforts on computer security are flawed because they don't focus on preventing exploits from occurring.

While this is outside my area of interest, (no publicly accessible SQL DB's), and therefore most of this is outside my level of "expertise", as usual, Catch is pretty much "on the ball".

1. The title doesn't need changing. The word "shoestring" in the title absolutely implies that this is a patch, quickfix or whatever you want to call it.... It sure as hell doesn't imply a corporate-wide solution to the issue.

2. Unless your app only requires the use of a limited subset of the possible SQL queries/keywords that can be pre-filtered at a low cost in management then there will be exploit attempts that _might_ compromise the security of the app at some point.

3. Since the app _might_ be compromised in an, as yet, unknown way your most logical and sensible option is to minimize the effect of the compromise.

4. Limiting the potential damage _is_ an absolutely viable and acceptable course of action in any kind of security, period.

While implementation might be at issue the principle is simply _responsible_ if your goal is security....

My $2....

Don\'t SYN us.... We\'ll SYN you..... \"A nation that draws too broad a difference between its scholars and its warriors will have its thinking done by cowards, and its fighting done by fools.\" - Thucydides

Originally posted here by catch chsh, again for the point of arguing with me you've missed the point.

I disagree, you seem to have missed MY point, which is that if you have issues as wide ranging in an application as improper input sanitizing, you aren't going to fix it by simply limiting where the attacks occur, you are just changing what is going to be attacked. I don't disagree that a least privilege system is a good idea, but it is impractical at best for most Web Applications. Perhaps not the dinky scripts some people write to track their contacts or what have you. Horde is a good example of the large type of Web Application I am discussing.

That is all RBAC/Least privilege ever does. If application X cannot write to directory Z, but application Y can... does this mean there is no point in making application X not be able to?

The flaw in that statement is we are discussing an environment with ONE application. There is no Application Y here, and application X NEEDS to be able to write to directory Z. What you are discussing is Functionality X of Application A doesn't need to update a database, while Functionality Y of Application A does need to update a database. The differences are minute but rather critical.

One more time, repeat after me... this is a "shoestring tutorial." It doesn't deal with huge databases or issues of dramatic scalability. It was just intended to present a concept. Why is that so difficult for you? Beside, the table can/most likely would use a plain text row for simple administrative identification and indexing.

It isn't difficult for me to understand, I was making it clear for everyone else (since you failed to do so) that it would fall apart in large scale applications. That's all.

First off, this technique can be applied to something like PHP-nuke in less than half an hour, and perhaps even faster if a module was developed. As far as I can tell, there is no faster/cheaper way to more or less universally reduce the risk of SQL Injection attacks.

I'd like to see proof in the form of patches, number of lines changed before I believe you can just drop in such changes on an already built app in less than a half an hour. If you've done it, that shouldn't be a problem. Otherwise, you're just talking out your ass.

Hmm, well pretty much all web applications and all applications in general are in need of redevelopment, least they are likely to suffer a security problem in the future.
So I guess any patch could be considered "a convoluted half-cocked concept hack that might help in specific circumstances." Is this to mean that you are against patching, even patching like this that resolve known and unknown attacks preemptively? Everything should just be rewritten at the first sign of trouble?

Interesting twist of my words, but sorry, not going to respond to crap I didn't say.

[...]

Read above, or are you suggesting that every other security type is moot? because you know, if every application was perfect, there would be no need for security. How many perfect applications have you created?

Perfect in regards to security? A couple. It is possible you know, contrary to the ultra paranoid concept everyone wants to believe. Within the scope of your application, you can make things perfectly secure. If this weren't the case, how come QMail has never been broken (drawing on an industry example)?

How is this different than on a system with data sanitation? In my system, unless the attacker has access to each users password, the user account management system is useless to them. And thanks to least privilege only one, very small script would have access of any kind other than SELECT to the user database. Unlike your approach where all the scripts retain this access.

Funny thing here, you're worried about people stealing your data, what about people doing damage to the data? Dropping a production database is a bad thing, small or large. Basically what you have stated is that your system is superior from the standpoint of security, but if people can break into your user management system from Script A, and on mine they can't do it because there's PROPER INPUT VALIDATION, how does that make yours more secure than mine?

The problem here is that you suffer from the flawed assumption that the best way to security is to prevent all exploits. This is, at best impractical (especially when using third party software), the goal of security should then be to minimalize the effects of a given exploit to reduce the damage it can inflict.

Actually, if you read what I wrote instead of making your own assumptions about what I'm going to write, you might realise that's pretty far from the case.

I do not wish to argue this point with you further, if you have more to say on the subject please direct your responses to TPEP@gibraltar.ncsc.mil, I am sure they would love to hear how you feel that 40 years of their efforts on computer security are flawed because they don't focus on preventing exploits from occurring.

All I gotta say is, you started this thread with:

People have criticized me for not making real world arguments... they say every system I discuss is for high security, high assurance, high budget, with intelligent users. I must be honest, that is the majority of my experience and this has always been a stumbling block when posting here or searching for work.

And I think it's rather clear that that is STILL the case. I have brought real world, business arguments against your concepts and you have failed to defend them, and are now refusing to do so.

I have nothing against granular access controls, but as they apply to many web applications, it is highly impractical, and you are FAR better served by doing things properly in the first place. Proper input filtering, stored procedures, and prepared statements are all examples of things that can help you achieve what you are trying to do with multiple users in the proper manner.

Chris Shepherd
The Nelson-Shepherd cutoff: The point at which you realise someone is an idiot while trying to help them.
\"Well as far as the spelling, I speak fluently both your native languages. Do you even can try spell mine ?\" -- Failed Insult
Is your whole family retarded, or did they just catch it from you?

1) Database indexing is helpful for people who query for a single row or many, it helps them get their data faster...improving performance. This can help with the cia of security by improving the availability of the system. Less time spent with locking is good and it also leads to less application errors.

2) Proper input validation is the first line of defense when trying to thwart sql injection or xss. It's stupidly simple and largely ignored.

3) If you have a proper database system you probably should give a different user account to different applications or different users of those applications based on their authentication credentials and business needs.

You should probably also be looking into the proper use of stored procedures, table/row/column level permissions and the proper use of database views.

4) If you are creating dynamic sql (one of the most common cases when sql injection is available) in your web application and not using stored procedures....you should probably get kicked square in the nuts for not knowing what you are doing.

5) Encryption of some fields in a database can be good or even necessary, but this has the potential of damaging performace. A better question may be do you really need to store this data in a database that is at a higher leve of risk? Is there a better way to architect your system?

Proper analysis and design is your friend. I'd also recommend putting up a link to some of the better web security sites out there, maybe something sans related and definitely something to http://www.owasp.org .

"When I get a little money I buy books; and if any is left I buy food and clothes." - Erasmus
"There is no programming language, no matter how structured, that will prevent programmers from writing bad programs." - L. Flon
"Mischief my ass, you are an unethical moron." - chshBlog of X

All you are doing is limiting where attacks can hit. You aren't stopping them from coming in. Apparently this concept is somewhat difficult to put across.

Limiting where the attacks can hit is the point! Isn't it? Do you run Apache as root? Of course not, you give it least privledge and that's a form of security. Why not apply to the database app?

Back in the day, when we didn't know what input to filter, wouldn't we want a way to at least mitigate the unknown? Let's pretend that right now, a zero day is running around. That zero day POSTS the string "RZYSQL" into our application, and the result is a forced "DROP DATABASE" in SQL (remember... pretend!). The limited role of the application would block it given it didn't have drop privledges. Until we at least hear about the exploit on the lists, we've at least mitigated the unknown. Then you can filter for that RZYSQL string until the db releases a patch.

When I know exactly what to filter it'll be done, but if it's feasible within my app I'll give roles to certain functions.

Concept of least priviledge isn't new, where EXACTLY it is appropriate and should be implemented (your mom's blog or purchasing dpt... shudder...) is something I won't argue!

As far as I can tell, there is no faster/cheaper way to more or less universally reduce the risk of SQL Injection attacks.

I won't agree with that though. If I had to edit an existing application to defend against SQL injection, first place I'd look is to just filter single quotes where necessary. I agree with your tut in terms of a general secure design, but not as a answer for SQL injection... and I think that was your point anyway.