4 ms·

Your stored procedures are "M" (Model) in MVC. It is a good abstraction, however I see no advantage (security-wise) in keeping this layer in DB stored procedure

by amenod 13y ago

Your stored procedures are "M" (Model) in MVC. It is a good abstraction, however I see no advantage (security-wise) in keeping this layer in DB stored procedures. It could just as well reside in some application layer.

Sssnake 13y ago
You should try it some time, the advantages become obvious. You can verify your SQL just by re-running a single sql script. You can use a simple script to auto-generate the functions in your app to access the stored procedures, including ensuring everything is type safe. You can update your logic without having to update your app. And there's literally no downside. I can't understand why anyone would want to keep their SQL in their app, it is horrible compared to keeping it in the DB.
einhverfr 13y ago
In general, with LedgerSMB, we don't usually use stored procedures for security reasons. We use them for abstraction reasons. There are cases, however, where we do add security barriers to them so there are reasons why having this as a layer in the db can be a good thing. Let's take an example: separation of duties in accounting. You want someone to enter transactions and you want someone else to approve them, and you want to enforce this through db permissions. You might give insert permissions to user 1 but update permissions to user 2. Except that you don't want to allow updates. You only want to allow some updates, to a few columns. So now you grant update permissions only on some columns. Except that you don't want to allow all updates on those columns. You only want to allow updating from, say, a null value to a definite value (say, indicating who and when). You can't do this with standard permissions, so you now create another role with permission to update these columns, and require that mere mortals go through a stored procedure to update them, which will only allow those rows to be updated which had nulls in these values. Now, I hear you say, you could do this with a join table called 'approval' with these columns so you don't have to worry about NULLs. Great, except that NOT EXISTS() queries tend to suck and you can't use partial indexes to ensure you can always quickly look up those transactions pending approval. So in the end NULLs, stored procedures, and an intermediate role end up winning out in terms of security.

by amenod 13y ago

Sssnake 13y ago

You should try it some time, the advantages become obvious. You can verify your SQL just by re-running a single sql script. You can use a simple script to auto-generate the functions in your app to access the stored procedures, including ensuring everything is type safe. You can update your logic without having to update your app. And there's literally no downside. I can't understand why anyone would want to keep their SQL in their app, it is horrible compared to keeping it in the DB.

einhverfr 13y ago

In general, with LedgerSMB, we don't usually use stored procedures for security reasons. We use them for abstraction reasons. There are cases, however, where we do add security barriers to them so there are reasons why having this as a layer in the db can be a good thing. Let's take an example: separation of duties in accounting. You want someone to enter transactions and you want someone else to approve them, and you want to enforce this through db permissions. You might give insert permissions to user 1 but update permissions to user 2. Except that you don't want to allow updates. You only want to allow some updates, to a few columns. So now you grant update permissions only on some columns. Except that you don't want to allow all updates on those columns. You only want to allow updating from, say, a null value to a definite value (say, indicating who and when). You can't do this with standard permissions, so you now create another role with permission to update these columns, and require that mere mortals go through a stored procedure to update them, which will only allow those rows to be updated which had nulls in these values. Now, I hear you say, you could do this with a join table called 'approval' with these columns so you don't have to worry about NULLs. Great, except that NOT EXISTS() queries tend to suck and you can't use partial indexes to ensure you can always quickly look up those transactions pending approval. So in the end NULLs, stored procedures, and an intermediate role end up winning out in terms of security.