Post #119,105
9/26/03 5:08:22 PM
|
Am I overly paranoid?
When I set up a database, particularly if web access is involved, I typically allow no access to base tables. For Selects I only use Views (w/only Select access, natch). For insert/update/delete, only stored procedures.
I've seen dozens of databases set up with public Select/Insert/Update/Delete permissions on base tables (for user accounts permitted in the database, that is). I've always thought that was atrocious design, reasoning that if the application is worth the expense of [insert your favorite commercial database engine's name] then it had to be worth protecting the data to extent possible.
(Aside: I'd do this w/any GPL'd database engines as well because I consider it good form).
Is that view, in general, overkill?
Just looking for reasoned opinions.
TIA.
bcnu,
Mikem
The soul and substance of what customarily ranks as patriotism is moral cowardice and always has been...We have thrown away the most valuable asset we had-- the individual's right to oppose both flag and country when he (just he, by himself) believed them to be in the wrong. We have thrown it away; and with it all that was really respectable about that grotesque and laughable word, Patriotism.
- Mark Twain, "Monarchical and Republican Patriotism"
|
Post #119,110
9/26/03 5:21:14 PM
|
Not overkill.
Recommended procedure, IMO. You can control access to underlying data more easily. One might call it encapsulation, in fact... [/me invokes the wrath of the Tablenator ;-)]
Having a database that has effective stored procedures helps, though.
Regards,
-scott anderson
"Welcome to Rivendell, Mr. Anderson..."
|
Post #119,116
9/26/03 5:54:38 PM
|
Slight overkill
I would consider making views for all selects a bit of an overkill. There are good reasons to make views for some tables, but trying to do every select that way isn't worth the effort.
Updates and inserts are a different story. Doing all updates and inserts through stored procedures can make sense if you have strong security requirements.
Of course it also depends on what the system is for. If your building an public web application then overkill is called for. If it is an internal application then you may not need to go the overkill route on stopping intentional hacking. And if your working on an application where the users are not required to change their passwords from the default 'password' then you might as well screw the security.
Jay
|
Post #119,117
9/26/03 6:00:13 PM
|
More than security. Maintenance.
Using DML for all your table access eases maintenance. If you have to change something only the dml package changes, and not all the calls.
Same with views. I like to treat the tables as private data. When it changes, nothing else breaks.
Regards,
-scott anderson
"Welcome to Rivendell, Mr. Anderson..."
|
Post #119,123
9/26/03 6:43:11 PM
|
In theory
In theory but my experience has been that isolating everything creates as much work as it saves. There are cases where having a view can keep you from having to change blocks of code, and in those cases go ahead and make one. But in many cases the view just adds another step to the update of the table since you end up having to care the change through the view. Thus creating views of everything as a policy doesn't really help.
The underlying problem (and this applies to all encapsulation) is that any substantial change to the data type or organization problems means there are flaws with the buisness or presentation logic that need corrected also.* If I discover that that table needs to be broken into two because part of it is actually a many to one relation to the other part, creating a view to hide the table split doesn't get me anything most of the time. That is because the fundamental change probably broke the buisness and screen logic tied to that table.
Using views for SQL tables will depend a bit on what languages you are using though. My experience is mostly with VB, along with a smattering of C and other languages. In a weakly types, objected oriented system you can probably gain more benefit from encapsulation because you are playing into the languages strengths.
Jay
* This should not be taken as saying it's a bad idea. Only that it isn't the panacea that some people make it out to be.
|
Post #119,128
9/26/03 6:55:56 PM
|
I'm talking about in PL/SQL, and this isn't theory.
We have about 1M lines of PL/SQL here. I've seen this in action again and again: people don't use DML or views, individual references to tables get scattered all over the code base, and eventually we have to go in and do a clean-up that could have been avoided by concentrating table access in DML or a view. I've done three large ones this year alone. This is very practical advice borne of experience in a large code base.
Table changes don't happen that often. Those that do can easily be taken care of in views with SELECT foo.* and the like. The problem is the business logic changes. Unless you hide your table access behind dml procedures, you end up with SELECT and UPDATE statements all over the place. Change one little thing and all of a sudden you have to go touch 30 modules to change one thing.
Regards,
-scott anderson
"Welcome to Rivendell, Mr. Anderson..."
|
Post #119,149
9/26/03 9:54:35 PM
|
Sometimes you need a view
at the law firm I worked at, we had an extensive database. I created some Views for various information that was stored in many different tables and required a lot of joined tables and tricky indexes. It was easier to create a view on say like Employee Info or Docket Info which referenced 12+ different tables but returned one huge recordset. Then the view can be accessed by stored procedures and filters put on them. It made maintaining the various stored procedures much easier.
But I agree, views on everything may be overkill.
"Lady I only speak two languages, English and Bad English!" - Corbin Dallas "The Fifth Element"
|
Post #119,134
9/26/03 7:42:43 PM
|
What's the application?
And what sort of controls do you need on the transactions?
If it is a heavy duty OLTP application then you better funnel all the inserts and updates through stored procedures or you gonna have locking problems. If it's a data warehouse, most of the data should be locked down anyhow since it's of a historical nature.
About the only exception to this rule is where there's a middle tier betwixt the client and the database. Even in that scenario, allowing the middle tier direct access to the tables basically means that the client is still required to go through the necessary processes.
From the read perspective (SELECT), you could possibly allow a little more freedom. But you're still better off creating filtered views - even if that view is at the current time little more than a straight output of the table. It allows you to clamp down on access if you ever need that facility (i.e. access rights can be enforced down to the row level - not just at the table level). If you want to have updateable views, the INSTEAD OF triggers can provide a facility to treat the update any way you'd like.
And yes, I do think you are being paranoid. But the second you look at things from the perspective of being a DBA, a heavy dose of paranoia is part of the job description.
|
Post #119,136
9/26/03 7:52:30 PM
|
While I'm on the subject
Register me as one that believes the distinction between functions and views is arbitrary. A view, as it stands in most historical implementations, should be viewed as a function that has zero parameters. It should also be a allowed that a view can be parameterized as well, allowing you to treat it as a function that returns a resultset. Either that or allow Stored Procedures that return resultsets to be allowed to participate as a source in the FROM of the sql construction.
|
Post #119,143
9/26/03 9:42:58 PM
|
Small intRAnet, overkill. Big public site, no.
________________
oop.ismad.com
|
Post #119,220
9/28/03 2:24:48 AM
|
not at all
Sounds like good data management hygiene to me. In my experience the less access you provide to developers and end users the fewer "mistakes" you have to fix. Inserts, updates and deletes have rules about when and how they're to be done called business logic. Access to the base tables should always be done via code that enforces those rules. Likely, there are rules about who can view what data too. Again, access should be provided by code that enforces the business rules.
Providing too many privileges to developers or end users will result in applications being written that depend on unnecessary privileges that will be a bear to maintain.
One of the prime directives of a DBA is to protect the data. The cost of the Database engine is irrelevant. What is the risk to the business if data goes missing or is altered inappropriately or seen by someone who shouldn't?
Have fun,
Carl Forde
|
