Hi,

I have been looking at LLBLGen Pro and at this stage, I am extremely impressed. I note, however, that all of the magic is done with generated SQL and precludes the use of stored procedures.

At my current place of work, the DBAs will not allow access to the SQL servers by anything other than stored procedures. How does this figure with the need for LLBLGen Pro to have non-sproc CRUD rights to a SQL server?

How do users approach this type of situation?

Thanks!

Gazley wrote:

At my current place of work, the DBAs will not allow access to the SQL servers by anything other than stored procedures.

How do users approach this type of situation?

1. Convice the DBA's that there is no "technical" advantage to SProcs.
2. Use a tool other than LLBLGen.

BOb

1. Convice the DBA's that there is no "technical" advantage to SProcs.

Other than its massively easier to tie down security <shrug>. I would say this is a technical advantage but hey, what do I know?

1. Use a tool other than LLBLGen.

So, the real answer is, there isn't a way or an easy way to do this, other than possibly use SQL Server 2005 and restrict access via the application rather than the user.

Thanks.

If a DBA has the power to decide for you how you write your software, then there's not much we can do, I'm afraid. Convincing DBA's that procs aren't an advantage is an uphill battle. For example, the 2 most heard myths from DBAs that procs are better are: 1) procs are more secure 2) procs are faster.

1) Take an application X. X is used by Joe from accounting. Say, X uses stored procedures and as Joe works on his laptop, it's apparent that X uses some sort of user to login to the db system. Say Joe is allowed to remove customers, within the context of the app. Is Joe then also able to do that if he connects with a query tool to the db using the exact same credentials as the application uses? Yes: the proc pr_DeleteCustomer(@customerID) can be called by Joe with the query: EXEC pr_DeleteCustomer(10) and customer 10 is gone. Security set on procs can also be done with roles on tables. It's also very little work to do and gives you the same security level.

2) the myth that procs are faster comes from the fact that people think that procs are stored in pre-compiled form. That's not true, at least not in modern database systems: they're compiled when they're first ran, the execution plan is cached, and if the cache decides the execution plan has to go, it's tossed away and the proc will be re-compiled the next time it's run. This is exactly the same as with dyn. parameterized sql.

Gazley wrote:

1. Convice the DBA's that there is no "technical" advantage to SProcs. Other than its massively easier to tie down security <shrug>. I would say this is a technical advantage but hey, what do I know?

no it's not 'easier'. You can also define roles on your schema, and place users in those rules, where they get the credentials of the role.

The problem with the 'it's more secure'-myth is that people forget that the application isn't written by the DBA. So the application developer has several DML actions to execute, however it's undecided whether the developer is able to do so, because of the procs available. This thus leads to proc-addition to fulfil the request of the developer. However, the security that should be applied to the proc depends on the context the proc is used in. That's not determinable at a static level. What IS determinable is that you can determine the rights on the data at the set /relational algebra level.

1. Use a tool other than LLBLGen. So, the real answer is, there isn't a way or an easy way to do this, other than possibly use SQL Server 2005 and restrict access via the application rather than the user.

Correct, together with role based security on the server.

Gazley wrote:

Other than its massively easier to tie down security <shrug>. I would say this is a technical advantage but hey, what do I know?

Frans pretty much responded to this, but since you are reponding to me I will throw my 5 cents in.

Most enterprise apps (I assume this is enterprise if you have a DBA) are done with a physical n-tier architecture so the "end-user" is not connecting to the database directly. It is the middle tier that connects to the app. Since this is the case there is much less of a need to control access at the user level.

BOb

Thank you Frans for this explanation. This is the kind of response I was hoping for when I posted my question. I understand what you are saying and this means a lot more now than it did before.

I am very impressed with LLBLGen Pro and was hoping that there was something I could wave at the DBAs to convince them that sprocs aren't the only show in town.

Thanks again.

-=Gary

Otis wrote:

If a DBA has the power to decide for you how you write your software, then there's not much we can do, I'm afraid. Convincing DBA's that procs aren't an advantage is an uphill battle. For example, the 2 most heard myths from DBAs that procs are better are: 1) procs are more secure 2) procs are faster.

1) Take an application X. X is used by Joe from accounting. Say, X uses stored procedures and as Joe works on his laptop, it's apparent that X uses some sort of user to login to the db system. Say Joe is allowed to remove customers, within the context of the app. Is Joe then also able to do that if he connects with a query tool to the db using the exact same credentials as the application uses? Yes: the proc pr_DeleteCustomer(@customerID) can be called by Joe with the query: EXEC pr_DeleteCustomer(10) and customer 10 is gone. Security set on procs can also be done with roles on tables. It's also very little work to do and gives you the same security level.

2) the myth that procs are faster comes from the fact that people think that procs are stored in pre-compiled form. That's not true, at least not in modern database systems: they're compiled when they're first ran, the execution plan is cached, and if the cache decides the execution plan has to go, it's tossed away and the proc will be re-compiled the next time it's run. This is exactly the same as with dyn. parameterized sql.

Gazley wrote:

1. Convice the DBA's that there is no "technical" advantage to SProcs. Other than its massively easier to tie down security <shrug>. I would say this is a technical advantage but hey, what do I know?

no it's not 'easier'. You can also define roles on your schema, and place users in those rules, where they get the credentials of the role.

The problem with the 'it's more secure'-myth is that people forget that the application isn't written by the DBA. So the application developer has several DML actions to execute, however it's undecided whether the developer is able to do so, because of the procs available. This thus leads to proc-addition to fulfil the request of the developer. However, the security that should be applied to the proc depends on the context the proc is used in. That's not determinable at a static level. What IS determinable is that you can determine the rights on the data at the set /relational algebra level.

1. Use a tool other than LLBLGen. So, the real answer is, there isn't a way or an easy way to do this, other than possibly use SQL Server 2005 and restrict access via the application rather than the user.

Correct, together with role based security on the server.

Hi Bob

Thanks for this further clarification. It is appreciated.

Best

-=Gary

pilotboba wrote:

Gazley wrote:

Other than its massively easier to tie down security <shrug>. I would say this is a technical advantage but hey, what do I know?

Frans pretty much responded to this, but since you are reponding to me I will throw my 5 cents in.

Most enterprise apps (I assume this is enterprise if you have a DBA) are done with a physical n-tier architecture so the "end-user" is not connecting to the database directly. It is the middle tier that connects to the app. Since this is the case there is much less of a need to control access at the user level.

BOb