> rjuju123@xxxxxxxxx wrote: > > bryn@xxxxxxxxxxxx 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.
