> rjuju...@gmail.com wrote: > > b...@yugabyte.com wrote: >> >> Now back to my new thread. I interpreted what Tom wrote to mean that he >> flatly rejected the idea that a database design was possible that prevented >> a client session that authorized as a role, that's designed for that >> purpose, from dropping tables and otherwise arbitrarily corrupting stuff. I >> expect that I completely misunderstood his point. But, anyway, that's what I >> responded to. >> >> Now it seems that you, Julien, are not convinced that the code that I showed >> prevents a session that authorizes as "client" from dropping the table, >> owned by "u1", where the data is. Nor are you convinced that a "client" >> session is prevented from inserting mixed or upper case data, updating >> existing data, or deleting existing data. Rather (as your Bobby Tables >> reference indicates) you think that a cunning SQL injection attack can >> manage to do these bad things. >> >> Well... the challenge is yours now: prove your point with some working code. > > I'm convinced that that authorization system works as expected, what I'm not > convinced of is that the authorization system can prevent an untrusted user > with a direct SQL access from actually hurting you. So yes in your case maybe > the "client" role cannot drop the showed table, but it can still insert > nonsensical data, from a client point of view, or lead to outage or other > problems without any difficulty, and there's nothing in the authorization > system that can prevent that. > > I'm also not convinced that your demo is proving anything, as "inserting any > only value made of non-uppercase characters in a single table" isn't really > representative of any basic application, especially without knowing what that > data will be used for. > > The only case this example could make sense would be a log application, and > then a direct SQL access you can insert nonsensical or malicious data, > depending on what the application will do with those data (which could lead > to crash in the client application, or make it do thing it shouldn't do).
My example wasn't meant in any way to be realistic. I'm sorry if I didn't make that clear from the outset. It was meant only to illustrate the principles. For example, the "lower case only" rule was meant to be an example of *any* data rule. Just like the write-once-read-many auto-generated surrogate primary key rule. Can you show me how those data rules, unrealistic as you might think them to be, can be violated? > I'm not convinced... that the authorization system can prevent an untrusted > user with a direct SQL access from actually hurting you. What do you mean by "untrusted"? Any person who is given the credentials to start a database session is trusted—even a person who can connect as a superuser and do untold harm. So focus on a person who has the credentials to connect as "client" in my example. But imagine a design that exposes functionality to "client" sessions exclusively through a carefully designed and implemented API that's expressed exclusively with user-defined functions and procedures. And choose something to model that meets your criteria for realism. Then show me, using a self-contained code example, how a session that authorized as "client" can cause the hurt that concerns you. Notice that "hurt" must be taken to mean having the persistently stored data no longer satisfying as specified business rule. And not anything to do with denial of service based on unconstrained resource consumption. If, when I review it, I can see how to change the code to remove the vulnerability, then you'll have learned something. On the other hand, if you can show me a vulnerability that cannot be fixed, then I'll have learned something! I'm selfishly more interested in that second outcome because my overall mental model will have been improved.
