store procedure vs hard coding sql statements
-
planning to change out the sql statements i had in one of my application i developed but then my boss was wonderinf if there would be any security issue since persons would now have access to our sql statments by simply jus viewing the store procedures. and i was wondering if there was any way to prevent this which leads me right back to wondering if i should jus make it be hard coded in the application so basically my two questions are: 1. is there a way to protect my storeprocedures i thought of leting the server ask for the server account and this works in that the db's password protected but when i click to use windows authentication it allowed me to connect which makes me think that this can be used as a way of getting around me protecting the store procedure say any idea or i am way of on my approach 2. whats the difference in performance between hardcodding sql statems or using store procdures Kenny Edmond
-
planning to change out the sql statements i had in one of my application i developed but then my boss was wonderinf if there would be any security issue since persons would now have access to our sql statments by simply jus viewing the store procedures. and i was wondering if there was any way to prevent this which leads me right back to wondering if i should jus make it be hard coded in the application so basically my two questions are: 1. is there a way to protect my storeprocedures i thought of leting the server ask for the server account and this works in that the db's password protected but when i click to use windows authentication it allowed me to connect which makes me think that this can be used as a way of getting around me protecting the store procedure say any idea or i am way of on my approach 2. whats the difference in performance between hardcodding sql statems or using store procdures Kenny Edmond
The difference in performance is typically large. Use the sql stored procedures instead. If they can see your stored procs then they can see your data too. So which is more valuable? Probably your data. This is a seperate problem that can be addressed with a good amount of security. There are typically three layers of security... IIS --> SQL --> Windows (File System) Sometimes the SQL auth is integrated so SQL and file system are the same. Your choice. But, I choose seperated for higher security, and integrated for ease of use.
-
planning to change out the sql statements i had in one of my application i developed but then my boss was wonderinf if there would be any security issue since persons would now have access to our sql statments by simply jus viewing the store procedures. and i was wondering if there was any way to prevent this which leads me right back to wondering if i should jus make it be hard coded in the application so basically my two questions are: 1. is there a way to protect my storeprocedures i thought of leting the server ask for the server account and this works in that the db's password protected but when i click to use windows authentication it allowed me to connect which makes me think that this can be used as a way of getting around me protecting the store procedure say any idea or i am way of on my approach 2. whats the difference in performance between hardcodding sql statems or using store procdures Kenny Edmond
1. From Books on line: "you are creating a stored procedure and you want to make sure that the procedure definition cannot be viewed by other users, you can use the WITH ENCRYPTION clause. The procedure definition is then stored in an unreadable form. After a stored procedure is encrypted, its definition cannot be decrypted and cannot be viewed by anyone, including the owner of the stored procedure or the system administrator." This is a rather extreme step to take, however. most hardcoded SQL statements can easily be viewed with a utility that dumps the strings tables in and executable, so IMO stored procs, even unencrypted, are no less secure than hard-coded sql statements. To view your Stored procs, the user would satill need to be able to log in to the server, to view the strings in your exe, all that is needed is read access to the file... 2. As a general rule, stored procedures are more efficient since the excecution plan is precompiled.
-
planning to change out the sql statements i had in one of my application i developed but then my boss was wonderinf if there would be any security issue since persons would now have access to our sql statments by simply jus viewing the store procedures. and i was wondering if there was any way to prevent this which leads me right back to wondering if i should jus make it be hard coded in the application so basically my two questions are: 1. is there a way to protect my storeprocedures i thought of leting the server ask for the server account and this works in that the db's password protected but when i click to use windows authentication it allowed me to connect which makes me think that this can be used as a way of getting around me protecting the store procedure say any idea or i am way of on my approach 2. whats the difference in performance between hardcodding sql statems or using store procdures Kenny Edmond
Stored Procs are faster and you can secure them just like anything else in SQL. But another point is valid. It is much harder to do SQL injection attacks on a stored procedure.
-
planning to change out the sql statements i had in one of my application i developed but then my boss was wonderinf if there would be any security issue since persons would now have access to our sql statments by simply jus viewing the store procedures. and i was wondering if there was any way to prevent this which leads me right back to wondering if i should jus make it be hard coded in the application so basically my two questions are: 1. is there a way to protect my storeprocedures i thought of leting the server ask for the server account and this works in that the db's password protected but when i click to use windows authentication it allowed me to connect which makes me think that this can be used as a way of getting around me protecting the store procedure say any idea or i am way of on my approach 2. whats the difference in performance between hardcodding sql statems or using store procdures Kenny Edmond
OK - a lot of people here have stated that stored procs are faster than inline SQL, and indeed that was the case for quite a while. This is not always the case now. One of the common misconceptions with Sql Server (version 7 onwards) is that it compiles stored procedures. Well - WRONG!!! It doesn't. What it does do, is cache execution plans for all queries that run through it (even ad-hoc ones). The algorithms that are behind this are very sophisticated and have gone through a lot of revisions/improvements by the Sql Server team. Some SPs will be quicker than inline SQL, and some inline SQL will be quicker than SPs. Evaluate what works for you. Hint - dynamic SQL is generally faster inline than SP because you only send the result of the dynamic query generation to Sql Server, rather than having Sql Server try to dynamically populate parameters, etc. Security. Well, it is true that people can get at your stored procedures. But, this only applies to the level of security that you have applied to your DB. Look at using roles and authentication to lock down the database. You can also lock down who can see your source code by implementing a robust security model for your organisation. Use a decent version control system to control who has access to the code. Limit the access on the directories where people check out/in the code to, so that only authorised users can gain access.
Arthur Dent - "That would explain it. All my life I've had this strange feeling that there's something big and sinister going on in the world." Slartibartfast - "No. That's perfectly normal paranoia. Everybody in the universe gets that." Deja View - the feeling that you've seen this post before.