Thursday, December 15, 2005

Thoughts from other blogs...

I track quite a few blogs these days (last count, 39 feeds).  Some work related, some not so much work related.  I was skimming them this morning and hit this piece.  I like the way Alf thinks…  It is the age old discussion – where does the logic go.  Me, I am a firm believer that the closer to the data we keep security and data logic – the better.

If you put security for example out in the middle tier (you know, the CICS transaction layer is what we used to call this piece – I know, nothing useful or cool was invented before application servers but hey – that is another story)  - what happens when the middle tier technology of choice today becomes passé? That’ll never happen right? (it is spelled with all capital letters – CICS, some people pronounce it as “kicks” transactions).  Well, I’m sure we can just reinvent screen scrapers for your middle tier technology like we did for 3278 terminal green screen applications.

And, if you put your security in the middle tier and someone, well, gets around your middle tier into your database – what then?  Oh, no security… Got it.

And, if you put your data logic (like NOT NULL, this field must be a string of 30 characters or less, this is a number, this is a date that is less than this other date over there, the value in this field must exist in this table over there, this is unique, when the status code = ‘ACTIVE’ – then this field must be unique, and so on) in your middle tier… What happens when the next cool application comes along (and doesn’t quite remember all of the rules?).

I could go on – and do in my seminar.  The optimizer wants those integrity constraints.  The data wants the protection of security (data hates to be exposed like that).  I love to get “algorithms” from middle tier programmers that purport to enforce data integrity – especially integrity that crosses rows in a table or crosses tables in the database.  It takes about 30 seconds to come up with a multi-user scenario involving 2 or 3 users that causes bad data to be created.  (Programmers still think very “linear” I find – not much thought to multi-user conditions.  Understandable since they treat the database like a black box and don’t really get how concurrency controls are implemented anyway).

The next blog I was reading was “We Do Not Use Blogs”.  Mogens made a good point this morning – one I make when discussing statspack.  It is very unlikely you can find the root cause of system wide slowdowns with a statspack report.  It is somewhere to start, but it is unlikely you’ll discover the root cause with it.  It can give you places to start looking, but it is unlikely in my experience to find the “answer”.  Oh sure, you can get lucky sometimes – but most times, no.  You need to dig a little deeper.

I do disagree with his pronouncement of this stuff as not being useful.  I have found places to go looking from a baselined statspack (from when the ‘system’ was ‘good’) compared to a report from when the ‘system’ was bad.  Looking for major changes (to help answer the question “what has changed” when the answer from humans is invariably “nothing has changed of course”).  While I might not be able to answer the final question “what is wrong” from them – they do give me clues where to start looking.  Else you have to start looking at everything.
POST A COMMENT

20 Comments:

Blogger Colin Sheppard said....

Add mine to your list and make it 40!

http://colinsheppard.blogspot.com/

:)

Thu Dec 15, 09:18:00 AM EST  

Anonymous Anonymous said....

Why, everything on there is boring

Thu Dec 15, 10:20:00 AM EST  

Anonymous Anonymous said....

"You need to dig a little deeper."

Agreed. That's where I tend to lose direction though. Can you provide some pointers on how best to start the digging?

Thu Dec 15, 10:35:00 AM EST  

Blogger APC said....

>> (last count, 39 feeds)

Then you need to track another three...

Thu Dec 15, 10:39:00 AM EST  

Blogger Thomas Kyte said....

Why, everything on there...

Short of being sort of "rude", I'm finding myself waiting for the update for spellbound, a plugin I wasn't aware of - so for me - "score", that sounds like a nice one (maybe it'll help my answers on asktom be more "correct" spelling wise in the future)...


You need to dig a little deeper.

application traces, you need details, back to the sql_trace file for me for more details to dig deeper. I might know what I'm trying to look for (hinted from the "aggregate") but the devil is in the details.

Thu Dec 15, 10:42:00 AM EST  

Anonymous David Weigel said....

Looking for major changes (to help answer the question “what has changed” when the answer from humans is invariably “nothing has changed of course”).

That's really the key. Nothing changes unless something changes. When it comes to computers, running the same program against the same data on the same software and hardware will do the same thing every time. Whether customers believe it or not.

Thu Dec 15, 05:40:00 PM EST  

Anonymous Partha said....

(like NOT NULL, this field must be a string of 30 characters or less, this is a number, this is a date that is less than this other date over there, the value in this field must exist in this table over there, this is unique, when the status code = ‘ACTIVE’ – then this field must be unique, and so on) in your middle tier… What happens when the next cool application comes along (and doesn’t quite remember all of the rules?).

I take your point on the data logic closer to the data. Would like some more clarity on how this needs to be implemented if it is 'also' not in the front end. For example, let's say we have unique constraints, NOT NULL constraints, Foriegn Key constraints and check constraints to validate our data.

The user fills in the screen (and leaves some data which needs to be NOT NULL), and then commits it. Data does not commit and returns a Error Message - For the user, this is a meaningless error message and the user complains that there is some Oracle Error, and hence the program (or worse still Oracle) has an error and is unable to process his request.

Correct me if Iam wrong, but did you mean that you need to have such constraints, or that all this should be in packages and checked in the server with meaningful error messages?

Thu Dec 15, 08:34:00 PM EST  

Blogger Thomas Kyte said....

Partha said...

Error handling and interfacing with the end user - that, that is the job of the client application (something they should spend some time on doing - but alas, they spend their time in futile attempts to reinvent integrity constraints outside the database). The client application is responsible for making sure the end user isn't confused - that is their job.

Constraints may be given meaningful names. You could even build a table of constraint name to "very useful error message for end user".

also, from Effective Oracle by Design, chapter on Efficient Schema's

[quote]
Does That Mean That the Client Should Not Do Integrity Checking Itself?


It can be very useful for the client to do integrity checking for a variety of reasons. The important thing to remember, however, is that it must ultimately still be performed in the database. Client-side integrity is not a substitute for server-side integrity; it is a complementary addition. Here are some of the salient reasons for client-side integrity:

· Better end-user experience Users can discover as they are typing that the data they entered doesn’t stand a chance of being entered into the database. They do not need to wait until they select Save.

· Reduced resource usage on the server  By preempting bad data on the client, you do not make the server perform work that will ultimately need to be undone.
But, just as there are pros to most everything, there are cons as well. The major disadvantage with client-side integrity is that you now have two places where the rules are, and if they change over time, you must ensure they are changed in both locations. It would be frustrating for a client to not be able to input information the database should accept because the application is working from old rules. It would be equally as frustrating to work on a set of data and feel confident that it is valid, only to discover as you choose Save that the database will reject it. Good configuration management and software engineering principles will reduce the chances of having this happen.
[/quote]

Fri Dec 16, 07:12:00 AM EST  

Anonymous jrock said....

i agree that the database is the the ultimate protector of the data, not client side business logic. there's always the risk of errors in client side code which can introduce bad data.

that being said, i'm a proponent of client side logic for some of the reasons you mentioned (immediate response, less taxing on the server, etc). as a developer (formerly, a linear, "isn't the database just a respository" developer - thanks tom), it's my job to make the end user's experience with my product as simple as possible. i will negate any external resources (such as the network) when it is logical to do so.

Fri Dec 16, 10:51:00 AM EST  

Anonymous Anonymous said....

I find statspack to be consistent with what is described in 'The Goal' by Goldratt (the book referred by Millsap in his book. You get the top 5 consumers of resources and you optimize thoes queries. Fits in nicely with - identify your constraints, do something about them and so on.

I always found them useful - but then that maybe because I know the apps and their data quite well - or maybe I was plain lucky.

Sat Dec 17, 03:05:00 PM EST  

Anonymous Anonymous said....

I know that at Oracle there has of late been a focus on Rules Engines. Oracle are including one in a forthcoming version of their application server. Wouldn't it make sense for the database to use this rules engine and also for app server based applications to use the same rules engine.

I guess the point here is that Oracle have always had a rules engine that consisted of declarative constraints stored in the database. That particular rules engine has not been that easy to tap into. Perhaps we really need a better rules engine. A single point of truth that is more accessible.

Mon Dec 19, 08:51:00 PM EST  

Anonymous Anonymous said....

Tom, if I follow the same logic you use to preach "keep application logic in the data base & security too" can you answer this: Why Oracle does not implement constraints at the database level in the eBusiness suite? No foreign keys, no constraints. The answer should apply to the people who preach separate the logic from the data too.

Tue Dec 20, 01:39:00 PM EST  

Blogger Thomas Kyte said....

Tom, if I follow the same logic you use to preach

First - I did not write the EBusiness Suite.

And if I had, it would probably be different than it is.

You can read advice and choose

a) to listen to it, take it into consideration
b) ignore it because others have

At the end of the day - it is up to you. You choose.

Does the EBusiness suite use database features? Probably more so than any *generic* application, yes. The EBusiness stuff is close to a "development environment" in many respects than a "buy this, install this, run this".

So, in the application space I work in (people building applications that are installed and run), not using the database is "not smart".

Let's look at an implementation I did have some influence on (not 100% influence, there are things I would do differently here as well)

HTML DB

flows_020100@XE> select constraint_type, count(*) from user_constraints group by constraint_type;

C COUNT(*)
- ----------
R 132
U 9
P 145
C 604

there are a few foreign keys, primary keys, lots of check constraints (many of which are *not* NOT NULL)...

Tue Dec 20, 01:54:00 PM EST  

Anonymous Anonymous said....

I personally like putting validation in *both* the database and the front end.

Validation on the front end usually gives you a very responsive user interface (when no query is needed), while validation on the DB makes sure that nobody is bypassing the client. I do put more man hours into the DB validation because it's the more important of the two.

Wed Dec 21, 11:41:00 AM EST  

Blogger Satya said....

I often come across databases designed for one specific application and end-up serving as data stores for many applications.
Of those, I often find applications and their data (stores) in good state that have their data integrity and security built into database. I have seen business (applications) fall apart over time that have no integrity and security (in that order).

Client side - integrity check a must for good interface.

Adding Rules engine in database - good idea. Its very useful. It can be used to enforce certain level of integrity check on heterougenous databases.

Thu Dec 22, 10:13:00 AM EST  

Anonymous Anonymous said....

10g OEM Diagnositcs/Tuning Packs attempts to detect root cause now, not just symptons for a large majority of problem types where Oracle understands the connection between sympton and root cause based on historical/statistical likelihood. They are a big improvement over STATSPACK, because they also tell you what's changed and can compare prior period snaps (good times) with other periods (bad times). This compare capability and historical analysis/trending makes much of what use to be time consuming much simpler. Oracle essentially says who needs tkprof, we have ASH, who needs statspack we have AWR, who needs tuning, we have proactive tuning (ADDM)... Nothing will replace the knowledgable DBA, but Oracle is making it much easier.

I'd love to comment on the business logic discussion, but Oracle is in many ways working counter to your viewpoints Tom. I am sure there are a variety of reasons for it, but with Apps and workflow leaving the PL/SQL world, there won't be much left, other than the legions of your disciples, who will have to carry the message until that next paradigm shift happens.

Thu Dec 22, 02:34:00 PM EST  

Anonymous Urs Meier said....

Sorry, business logic is more than just a few constraints.
Whatever you can declare (no programming needed) should be defined in the database. These declarations are generated by design tools anyway and therefore for "free".

In most scenarios you have more than just one database (e.g. user accounts on a LDAP server), so checking security and business logic is not tied to the database.

Same for security. Just use a privileged, secure account for your connections and nobody gets around your middle-tier.
DBAs are used to make privileged accounts secure or they even use single sign on technics.

Tue Dec 27, 12:47:00 PM EST  

Blogger Thomas Kyte said....

about business logic - that is precisely why I said:

... . Me, I am a firm believer that the closer to the data we keep security and data logic – the better. ....

right up front.

However, the LDAP thingy doesn't fly with me - the database is certainly more than capable of using that as well - that is a DATABASE thing, not an application thing. Keep security by the data, not far away.


Laughed so hard when I read "Just use a privileged, secure account for your connections and nobody gets around your middle-tier.". Thanks for the smile. Middle tiers have so many holes in them (they are written by whom? oh, the guys right out of school - got it). Yeah, use a super user for your middle tier, perfect. That is why sql injection is sooo popular and so easy to exploit.

Tue Dec 27, 01:04:00 PM EST  

Anonymous Anonymous said....

Hi Tom,

First I agree, I love constraints; absolutely love them. If the RDBMS were robust enough I would put every business rule and constraint in it.

The second item is: I agree that security should be in the Database tight and integrated. So why then does Oracle not overhaul it security? It is really showing its age. I know advanced security is out there but as I look at it evens seem weak (I must admit I have not yet used it but got the OK to buy it after the recent METALINK note that stated Oracle is insecure and said to use Advanced security).

I would like a security system that knows a schema is different from users. A user own 0-N schemas. A user can have privileges on a schema or objects within the schema. AND the most important: The super user should be able to delegate Authorization and Authentication privileges to a user for a schema. Who can then delegate privileges to objects within the schema. Should schemas be able to contain schemas? I do not know. Should Schemas contain users? I do not think so. Sometimes I think SQL Server did a better job at the security architecture.

Tue Dec 27, 03:21:00 PM EST  

Anonymous Anonymous said....

Hi Tom,

First I agree, I love constraints; absolutely love them. If the RDBMS were robust enough I would put every business rule and constraint in it.

The second item is: I agree that security should be in the Database tight and integrated. So why then does Oracle not overhaul it security? It is really showing its age. I know advanced security is out there but as I look at it evens seem weak (I must admit I have not yet used it but got the OK to buy it after the recent METALINK note that stated Oracle is insecure and said to use Advanced security).

I would like a security system that knows a schema is different from users. A user own 0-N schemas. A user can have privileges on a schema or objects within the schema. AND the most important: The super user should be able to delegate Authorization and Authentication privileges to a user for a schema. Who can then delegate privileges to objects within the schema. Should schemas be able to contain schemas? I do not know. Should Schemas contain users? I do not think so. Sometimes I think SQL Server did a better job at the security architecture.

Tue Dec 27, 03:22:00 PM EST  

POST A COMMENT

<< Home