Friday, December 30, 2005

Who needs modular code?

Wouldn’t it be nice if when people wrote some useful code, they tried to make it suitably modular and reusable? This is what I have to contend with at the moment. We have a large, complex system written in Oracle Forms that we are now partially re-writing in HTMLDB (hurrah!) One function I want to replicate is the ability to change your own Oracle password; the current Forms application has a form to do this that looks like this:
Old Password: [ ] New Password: [ ] Confirm New Password: [ ]
So that should be a moment’s work to redo, right? Wrong. There is a package of procedures for user maintenance that contains the following 2 procedures that are relevant:
PROCEDURE change_password_validate
(p_username         IN VARCHAR2,
 p_old_password     IN VARCHAR2,
 p_new_password     IN VARCHAR2,
 p_confirm_password IN VARCHAR2,
 p_profile_name     IN VARCHAR2,
 p_mode             IN VARCHAR2 DEFAULT 'N',
 p_mask             IN  VARCHAR2) ;

PROCEDURE change_password_process
(p_username IN VARCHAR2,
 p_new_password IN VARCHAR2,
 p_confirm_password IN VARCHAR2,
 p_mask           IN VARCHAR2,
 p_profile_name IN VARCHAR2,
 p_encrypt_pw IN VARCHAR2,
 p_admin_mode IN VARCHAR2 DEFAULT NULL) ;
Some design flaws are immediately evident:
  • Validation is totally separate from processing. If I choose to, I can skip the validate routine altogether and call the process routine to change the password to anything I like, regardless of whether I get the old password right or confirm it correctly. (Actually, this foolish separation of validation from processing is a company standard!)
  • I get to choose whether the password is to be stored (in our own application’s USERS table) in encrypted form or not. HTF do I know whether it should be encrypted or not?
  • I need to supply something called p_mask, which I think may be something to do with the encryption process, or maybe the validation process – none of this is documented of course, or at least nobody knows where any such documentation may be found. I have tried passing the word ‘mask’ and it seems to work, except that all subsequent attempt to change the password then fail on the validation of the “old” password – perhaps because it has been encrypted in an unexpected manner.
  • I also need to supply something called p_profile_name, which I do happen to know is a user attribute something like a role, stored in the USERS table. Well excuse me, but if I’m passing in the username as a parameter, why should I have to go look up the USERS record and obtain the profile_name value just to pass it into this lazy procedure?
  • I don’t fully understand p_mode and p_admin_mode either, but at least they have defaults which I assume (for now) I can live with.
So instead of being a 5 minute job, this is probably going to occupy about a day of my time: locating source code (the packages are wrapped in the database), studying source code to see what it is doing. And if all that fails, trying to find someone from the team that wrote the code and ask them to tell me what I should be doing.

35 comments:

Adrian said...

I've never understood why anyone would want to reimplement user security in the first place.

IMHO the best modular implementation of this, is the one that comes shrink wrapped when you install the database.

With proxy authentication there is no excuse for having data security any where other than the database.

AskTom has a great rant on this exact subject under "database security vs application security". ( Although he uses metalink as an example, which if you've used it lately could be thin ice for a design template admittedly ).

Rab Boyce said...

HtmlDB? is it the 90's again?

William Robertson said...

No, it's 2005. Try to keep up.

Rab Boyce said...

actually, it's 2006.. ( sorry, couldn't resist !!! )

William Robertson said...

Yes, now it's 2006. 2005 was last year. The 90s were a while ago. Hope it's all starting to make sense now.

Tony Andrews said...

I wonder what Covenant thinks HTMLDB is, since it didn't exist in the 1990s - it came out with Oracle 9.2.

Adrian said...

I also can't stand redundant names.

*_process? What else would you find in a procedure?

Of course, not as bad as the data_table I had to work with.

Rab Boyce said...

I was, of course, thinking of WebDB, which was HTML DBs predecessor.

but seriously, HTML generation in the database? why for god's sake, man! why?

Adrian said...

I'm impressed by convenant.

Given only a questionable PL/SQL API for changing a password, this person can recommend the appropriate implementation technology for an entire project.

Tony Andrews said...

> but seriously, HTML generation in the database? why for god's sake, man! why?

Because it's about an order of magnitude easier than alternatives such as J2EE or .NET. And what is the downside?

Noons said...

"an order of magnitude easier"?
You feeling generous or what?

Rab Boyce said...

ok, ill take the bait. why the devil not?

J2EE and .Net are not difficult technologies, they are only difficult to those who can't be bothered to learn them. I'll grant you that if all you know is Oracle and PL/SQL then HTLMDB is probably easier to grasp than J2EE, but that's really no excuse. That's the sort of lazy thinking that leads to this sort of thing which was written by someone who knew shell, sql and didn't realise that it would actually be easier and more elegant to do this in PL/SQL. ( If all you have is a hammer every problem starts looking like a nail! )

Also J2EE and .Net are developers tools to be used by software professionals, whereas HTMLDB is aimed squarely at power users, the sort of people who run businesses on excel spreadsheets and access databases. Are these really the sort of people you want designing your systems?

I wasn't suggesting that HTMLDB was wrong for your environment. Perhaps you haven't got any experienced programmers where you are, or are only interested in limited web functionality. I was, however, expressing a general preference for a seperation of UI, application logic and data. I also think that choice of programming language should be based on fitness for purpose for the task at hand and not on religous preference. For example PL/SQL for data processing in the RDBMS environment, something more general purpose such as Java for the application logic and UI, perhaps C or C++ if there are any near real time requirements ( although java is making some in roads into this area ).

Please don't misunderstand, I am a big Oracle fan and firmly believe that data should be stored and manipulated using a powerful RDBMS, however I think it is a mistake to keep throwing extra functionality into the database environment just because you can.

Apologies to those who think this is way off topic!

Tony Andrews said...

> J2EE and .Net are not difficult technologies, they are only difficult to those who can't be bothered to learn them.

I have learned .Net, and it makes building an Oracle database application a LOT harder than HTMLDB does. I don't like HTMLDB because I am a power user, I like it because it delivers on productivity and functionality. Writing Java or VB code to loop through rows of data to be displayed seems like a backward step to me from tools like Oracle Forms; HTMLDB seems like a step forward from Oracle Forms. It is unfortunate that Oracle has decided to promote it as a power user toy; I know that Tom Kyte for one sees it as beng a LOT more than that.

By the way, have you actually used HTMLDB, or are you just prejudiced against it?

Rab Boyce said...

You do realise that you can write classes ONCE to do the looping and rendering for you...its a concept that has its roots in structured programming, quite clever really.

In fact, you may find that Oracle Forms or HTMLDB do that anyway under the covers, its just hidden from you.

In fact if use something like JSF, the databinding is quite sophisticated, and you don't need to write much code unless you need to change the way something works.

and, no , I haven't used HTMLB...I have only read about it, but then I haven't used the palm of my hand to bash in nails as I would rather use a hammer.

and as an ex Forms programmer NOTHING is a backward step from oracle forms!

Tony Andrews said...

Strange comparison: your hand was not designed for bashing in nails, whereas HTMLDB was designed specifically for building database applications - despite Oracle's bizarre marketing strategy.

Of course HTMLDB does it for me under the covers - that's my point! Why should I have to roll my own pagination control logic? HTMLDB does the humdrum stuff and leaves me to concentrate on the business logic - which will all be held in the database where it really belongs ;-)

Ah well, I'll never convert you - and why should I? Let the masses toil away designing classes to manage data in Java, while the few enlightened ones work with a tool that is so much more productive and integated with the database. One day we will rule the world!

Adrian said...

Covenant, humour we lowly "unskilled programmers"...

Maybe you'd like to post an example of the super efficient, scalable, concurrent, ( obviously "generic" ) class you wrote ONCE to "do the looping and rendering"?

Tony Andrews said...

Here is one the the best pro-HTMLDB arguments I have seen, from someone who is a pretty bright button:

http://forums.oracle.com/forums/thread.jsp?forum=137&thread=285796&message=883538#883538

Rab Boyce said...

Ok, I over simplified the solution to make a point. You clearly do not write a single class to do the looping and rendering ( it would be a nasty class if you did ) , you need to build ( or use ) a framework consisting of many specialist classes, my point was that you don't need to keep re writing the same stuff again and again to use J2EE or .Net ,which is exactly the point of using J2EE or .Net.

What writing in Java ( or simliar ) offers however is flexibility, scalability and reuse. You can write code ( application, presentation , whatever ) that can be used in different applications more easily, and in my opinion are more maintainable.

If The presentation code is kept distinct from the database then it can be reused without a database. ( or with a different database, or multiple databases or databases from different vendors ).

If the application logic is maintained in an middle tier then it too can be reused in different situations ( even one where no database is required, or multiple databases for distributed transactions etc ).

Separating the presentation code from the application code and the data also allows us weak minded programmers ( myself included ) to concentrate on the problem at hand, which makes debugging easier.

How often have we seen code where the display logic is interwined with the business logic and data access making the code overly complex to read, having a more structured architecture helps prevent this messy thinking. My original point ( for those with long memories ) was that I didn't think it was a good idea to have your UI generation done by the database. it just feels wrong....its like the java programmer who writes his/her own pseudo sql or never uses a database unless they really have to, its just icky.

Adrian said...

"If The presentation code is kept distinct from the database then it can be reused without a database"

Useful.

Next time I find some clients that want an application that processes no data I'll give you a ring.

"...or with a different database"

Again, Please humour me to the extent of showing me this class you've written that works with different databases.

Tony Andrews said...

I can't deny that HTMLDB locks you into the Oracle DBMS. If that's a problem, you won't want to use HTMLDB. It's not a problem for me: I'm not a great fan of database independence.

Of course display logic should be kept distinct from the database and business logic, and it is in my HTMLDB applications. Just because HTMLDB uses its own database under the covers doesn't mean that presentation and business logic are all munged together in one lump (as they are in many .Net applications I have seen).

I don't really care if my HTML is generated by the DBMS, an application server or a little man inside the PC as long as I don't have to do it myself!

Rab Boyce said...

Ok, I think we have reached the fundamental nature of our disagreement.

you believe that the RDBMS environment should be used as a general purpose application environment, and I don't.

There are arguments on both sides, and we are never going to agree, as this is largely a preferential thing. ( you can continue doing it the wrong way if you prefer - Only Kidding! )

----

As for Thai's request for my super duper class,er..I think you may have misunderstood what I was saying. There are generic database independant frameworks out there for doing this stuff and I haven't written one, I was mereley pointing out that just because your using java doesn't mean you need to write the processing and rendering code yourself every time as suggested by Tony.

I did once write a generic query processing thing that rendered the results of an arbitary query on a web page with pagination etc, but Im not sure that's what you meant and I wouldn't hold it up as a great example of software engineering.

Tony Andrews said...

Never mind, it's been fun arguing - and it has inspired me to update my blog, which doesn't happen very often!

Adrian said...

Covenant,

You have not found "the fundamental nature of disagreement".

What actually happened was that you waded into an Oracle blog that you clearly do not understand, and whilst completely missing the point that of the post you were reading, completely dismissed HTMLDB as an implementation technology without any terms of reference.

You then began a long, tedious and uneducated rant about the scalability of java technologies and software engineering in the most patronising terms you could muster.

After being shot down a couple of times you then attempted to change the subject. Twice. At which point you completely inverted your argument: "HtmlDB? is it the 90's again?" to "I wasn't suggesting that HTMLDB was wrong for your environment". Hmmm, interesting.

You have, in terms best described by Umberto Eco in "Foucault's Pendulum", "Talked outside your glass".

Rab Boyce said...

Thai,

I am afraid I beg to differ. I do actually understand the nature of the blog and In fact I understand a reasonable amount about oracle and development in general, although im not an Oracle specialist.

I was simply making a joke about HTMLDB and using PL/SQL to generate HTML , which I was thought was in keeping with the tone of the wtf blog, clearly it wasn't.

And to be fair, I did ramble a little to keep the argument alive and keep my tiny, uneducated mind amused.

As for the patronising tone, that's a little pot and kettle don't you think? , There was no offence intended ( as from the nature of the last post it was clearly taken! )

anyway, thanks for the wonderful blog. I look forward to reading ( although, obviosuly not understanding your posts )

and if you thinking quoting Eco makes you look clever you are as jacopo belbo would say 'Ma gavte la nata'

Rab Boyce said...

that should have read ...

and if you thinking quoting Eco makes you look clever you need to as jacopo belbo would say 'Ma gavte la nata'

William Robertson said...

I think he's just called your mother a hamster.

Adrian said...

What he doesn't know is that my mother is a hamster.

Frankly I'm giving him a few more attempts to get the insult right before I respond.

Rab Boyce said...

yeah.... must learn to preview posts. kind of took the wind out of that one!

:S

Adrian said...

I'm deeply suspicious of claims such as: " build ... a framework consisting of many specialist classes, ... [so] you don't need to keep re writing the same stuff again and again to use J2EE or .Net ,which is exactly the point of using J2EE or .Net."

Every J2EE project I've been on ( and some that I haven't ) seem to involve the entire "framework" being rewritten several times a year to keep up with the latest J2EE-buzzette, in response to user complaints that the UI is unjustifiably slow.

Rab Boyce said...

I think ( I hope! ) we can all agree that there are bad J2EE projects, there are also bad .Net projects, and believe it or not bad Oracle Projects ( hence oracle-wtf ).

There is progress in software, and sometimes it is desirable to rewrite some code from time to time to take advantage of advances in technology or adopt new techniques. This isn't a Java issue, just a software one.

I for one am glad that these advances happen as it keeps me employed and above all keeps me interested.

Tony Andrews said...

I don't mind having to learn new techniques or languages when I feel an advance is actually being made. But for database applications, I can't help feeling that some of the "advance" has been backwards. To build database applications with many screens consistently requires a framework tailored to the job. For Oracle, the nearest Java-based framework is Jdeveloper with ADF. I imagine that in 3 or 4 years time that may be a really good toolkit, but right now it seems rather cumbersome and flaky.

Surely a sign of progress should be that it becomes easier to build applications rather than harder - after all, this still do the same job they have always done. So why do I keep hearing that you need to allow 2-3 times more development effort for J2EE compared to that ancient warhorse called Oracle Forms?

Adrian said...

"I for one am glad that these advances happen as it keeps me employed and above all keeps me interested. "

Gulp.

Hopefully the purpose of software engineering isn't to keep the engineers employed and happy.

Rab Boyce said...

it isn't?

Phantom Nitpicker said...

> humour we
humour us

Anonymous said...
This comment has been removed by a blog administrator.