Restricting a developer to modify table contents of other application on same server

Restricting a developer to modify table contents of other application on same server

  

Hi All,

i have a situation and hope you guys can guide a better solution.

i have two applications on same server. Both have different set of tables in them.

Now the problem is that while in development phase, a developer in Application "A" can access a table T2 which is part of Application "B" and modify the contents of this table.

User of Application A is not referencing the table T2, rather user is updating record via SQL injection method and passing the string in advance query addressing the table as "update table dbo.tablename..........." (since table T2 is not public)

this way the table is accessible and user in application A can modify table T2. 

Is there any best way to restrict the developers of a particular application to modify any record of other application ?

Hi Debasis,

are you using Lifetime? You can assign different Roles to different Applications.

For instance, you can give Change&Deploy in App "A" to user "John" and List in App "B".

But this works at Service Center / Service Studio level... if you know the table name I guess you can update it ...

Hum...

If you are using the standard naming convention for OutSystems tables (OSUSR...), in design time, the platform recognizes that you are trying to use a table directly from database, with the potential to skip security settings, and don't allows you.

This does not happen if you use the scheme where the table names are the same as the entity names.

But using SQL injection, through input parameters, I think the server are not able to recognize this in design time, and it does not verify in run time...

And if this is the case, it will be possible, in fact, to write/read to tables that the developer shouldn't have access...

I on't know if the EncodeSQL routine can prevent this, but it is a developer thing, so even if it worked, possibly he could skip its utilization.

Cheers,
Eduardo Jauch

Eduardo Jauch wrote:

Hum...

If you are using the standard naming convention for OutSystems tables (OSUSR...), in design time, the platform recognizes that you are trying to use a table directly from database, with the potential to skip security settings, and don't allows you.

This does not happen if you use the scheme where the table names are the same as the entity names.

But using SQL injection, through input parameters, I think the server are not able to recognize this in design time, and it does not verify in run time...

And if this is the case, it will be possible, in fact, to write/read to tables that the developer shouldn't have access...

I on't know if the EncodeSQL routine can prevent this, but it is a developer thing, so even if it worked, possibly he could skip its utilization.

Cheers,
Eduardo Jauch

Hi Eduardo,

could you please detail out your statement below with an example for the naming ?

"If you are using the standard naming convention for OutSystems tables (OSUSR...), in design time, the platform recognizes that you are trying to use a table directly from database, with the potential to skip security settings, and don't allows you."


Hello Debasis

I did some tests with the last platform and Server Studio.

The "normal" naming scheme for the physical tables in the database is that each application entity has a prefix OSUSR_ followed by, if I'm not mistaken, a string that represents the module where the entity was created and the name of entity, probably truncated due the fact the naming scheme uses a 18 characters fixed length for table names (again, If I am not mistaken). 

With this format, the platform can identify if you are trying to access an entity, in an SQL, that uses the reserved word OSUSR, and raise an exception in this case, effectively preventing a developer of accessing an entity he does not have access.

But, there is the possibility to change the name scheme to use physical table names the same as entity names. I'm not sure if OutSystems still support this, but in the past it supported and I know of clients that still uses this naming convention. When you choose this one, platform does not check anymore the table names and effectively the programmer can access entities outside the modules he have access through SQL.

So, if you are using the default naming convention, platform will prevent the use of the physical names, even in run time. But if the physical names are the same as the entities, so you are using this second naming scheme, there is no way to prevent a developer of use an entity he shouldn't have access, if he knows the name of the table in db.

Cheers,
Eduardo Jauch

Thanks for the explanation Eduardo !

But again, this needs to be worked upon as an enhancement in future releases. :)


Hello Debasis.

Sorry, I didn't understood. What must be worked on?

From my tests, there is no possibility of accessing a table using its physical name in SQL.

What do you think needs change?

Cheers

Eduardo Jauch

Hello Eduardo,

as per my tests, there is possibility to access table via sql with prefix OSUSR_ 


exec [sp_executesql] @Query

and for @Query parameter, pass on the SQL statement addressing the table as [<DATABASE_NAME>].DBO.[OSUSR_0UG_EMPLOYEE].[ID] . This can be accessed by both table name and view name OSUSR_0UG_EMPLOYEE_T18.

Hi Debasis,

Which version of OutSystems are you working on?

Cheers,
Eduardo Jauch

Hi Eduardo,

its ver 10.0.302 

In version higher this os fixed. In my personal that is the latest version, it identify the use of reserved words like OSUSR_ and block both in design and run time.

did you follow the same method via which i am executing the query ?

if yes, then i will test it out with my personal environment which has got the latest one.

i will post the updates after verification.

Hello Debasis,

Yes. Using exec and everything else, with the query itself in the input parameter.
The query is checked during execution after all input parameters being expanded and if it finds the reserved words, it raises an exception.

Cheers,
Eduardo Jauch


The parameter from the query provided from Sahoo doesn't need to be set as Expand Inline, and will indeed trigger the effect described by him on 10.0.702. And even if using Expand Inline parameters, there are ways of achieving the same result.

If you have sensitive data in your application and want to restrict developer's access to it, I'm afraid the best option is to obfuscate the data, or don't have it in DEV in the first place. LifeTime lets you easily restrict access to other environments, on which you may have the original data.

Hello Debasis,

Unfortunately, Paulo is correct.
On my tests I never thought of NOT setting the expanding inline in the parameter.
Without it, the query is executed using the physical name without a problem.

I wasn't able to find a way of doing the same using inline parameters though. Like Paulo said, probably there are ways to do it.

And of course, he has a valid point when he says that sensitive data shouldn't be in DEV environment.
Regarding the possibility of code injection that will "open doors" in an application when used in production, it's important to implement the code review process, both because of security issues as well as to improve quality of applications delivered.

Paulo,
Thanks for the correction.

Cheers,
Eduardo Jauch

Hi, Paulo and Eduardo - you both are correct not to have sensitive data at Dev Environment, rather we can have several methods calling them from a more secured server. "And Yes, Code review is a must."

However, there should be some level of restriction to what we can achieve right now. May be it will be a part of future releases (if Outsystems Champs are reading this thread).

:)

Eduardo,

Even with expand inline, there are ways to bypass the runtime checks, e.g. by using CHAR() or other functions.

As for the original question, there is just no way to restrict access to the database tables if you give a developer direct access to the database, which is what happens with the SQL statement. If your developers are misbehaving in such a way, you should just fire them.


Kilian Hekhuis wrote:

Eduardo,

Even with expand inline, there are ways to bypass the runtime checks, e.g. by using CHAR() or other functions.

As for the original question, there is just no way to restrict access to the database tables if you give a developer direct access to the database, which is what happens with the SQL statement. If your developers are misbehaving in such a way, you should just fire them.


Thanks Killian,

I'm not used to do heavy use of T-SQL/PL-SQL, so, shame on me for assuming that was not possible.
I did try some approaches, but most make use of variables (starting with @) and I was not able to make them work.

I'll keep trying, for the sake of learning :)

But I was thinking...
No matter how much security we put over our systems, internally is always possible to overcome them. So, the persons that do have access can always misuse available information. And it is not possible to prevent this (only make it difficulty)

Cheers,
Eduardo Jauch

tbh if you need to force that you need to create multiple catalogs, force each application on a different connectionstring and simply do a police-job of watching those advanced-sql's.

it's more a matter of discipline of those developers.


if they actually refuse to work nicely and by the book i really wonder...


J. wrote:

if they actually refuse to work nicely and by the book i really wonder...

Yeah, like I said, fire them :)

Debasis Sahoo wrote:

Hello Eduardo,

as per my tests, there is possibility to access table via sql with prefix OSUSR_ 


exec [sp_executesql] @Query

and for @Query parameter, pass on the SQL statement addressing the table as [<DATABASE_NAME>].DBO.[OSUSR_0UG_EMPLOYEE].[ID] . This can be accessed by both table name and view name OSUSR_0UG_EMPLOYEE_T18.

I find it hard to understand why you even have to use those kind of queries.

sure there are circumstance to use those in specific situations, but really to bypass the normal way of aggregates or even advanced-queries and access the generated tables (so not taken into account of versioning) is really really bad programming.


J.

I think Debasis is trying to find a way to avoid access to an entity if the user does not have the right to do so, but those queries allows it. 

So, as was pointed out, the only way is to use other methods to protect data, like using different catalogs, etc. :)

Guys,

i understand to avoid such situations and have better monitoring on the developers work. This is all about what we can do as of now.

But my point is that if from outsystems, we do not have such security measures then instead of making the habit on avoiding the methods, let outsystems experts focus on to include this feature in future release. :)

Again, i a completely agree on your valuable thoughts to just avoid these practices. However,since till now we don't have a restriction from development side, i am more focused (with a request note) on the experts to have R&D on it. :)

Hi Debasis,

I understand your point of view, but I don't think it's technically possible to restrict access to certain tables if you give direct access to the database at all (at least without severe performance/maintenance penalties). So either OS can abolish the SQL statement (very bad idea), or allow it and also allow the fact that a bad programmer can do bad things with it...