Is read-only access to production systems safe?

There is an interesting thread on the Dizwell Forum about access to production systems using tools like TOAD. Of course, this begs the question, is there such a thing as “safe” read-only access to production data?

If you are allowing read-only access to tables on a production system, you are also allowing record and table locking. If you don’t believe me, take a look at this:

tim_hall@dev1> CONN sys/????@db10g AS SYSDBA
Connected.
sys@db10g> CREATE USER test_user1 IDENTIFIED BY test_user1 QUOTA UNLIMITED ON USERS;

User created.

sys@db10g> GRANT CONNECT, CREATE TABLE TO test_user1;

Grant succeeded.

sys@db10g> CREATE USER test_user2 IDENTIFIED BY test_user2 QUOTA UNLIMITED ON USERS;

User created.

sys@db10g> GRANT CONNECT TO test_user2;

Grant succeeded.

sys@db10g> CONN test_user1/test_user1@db10g
Connected.
test_user1@db10g> CREATE TABLE test_tab1 (
2    id  NUMBER
3  );

Table created.

test_user1@db10g> INSERT INTO test_tab1 (id) VALUES (1);

1 row created.

test_user1@db10g> GRANT SELECT ON test_tab1 TO test_user2;

Grant succeeded.

test_user1@db10g> CONN test_user2/test_user2@db10g
Connected.
test_user2@db10g> DECLARE
2    l_id  test_user1.test_tab1.id%TYPE;
3  BEGIN
4    SELECT id
5    INTO   l_id
6    FROM   test_user1.test_tab1
7    WHERE  id = 1
8    FOR UPDATE;
9
10    ROLLBACK;
11  END;
12  /

PL/SQL procedure successfully completed.

test_user2@db10g> LOCK TABLE test_user1.test_tab1 IN EXCLUSIVE MODE;

Table(s) Locked.

test_user2@db10g> ROLLBACK;

Rollback complete.

test_user2@db10g>

Looks like the SELECT privilege is not so safe after all 🙁

Of course, you don’t need direct access to the tables. You could always provide access to the data via APIs, but that’s not what your average TOAD user wants to hear!

Cheers

Tim…

West Mid Show…

I went to the West Mid Show yesterday. It’s an agricultural show held every year, near the town where I grew up. Although we did look at most of the exhibits, the main reason for visiting was the impressive display of tractors, diggers, combine harvester, ploughs, bailers, mowers, quad bikes etc. Most of the day was spent watching my 4 year old nephew play on all this equipment, punctuated by me nervously saying phrases like, “Please don’t do that!”, and, “Don’t break it, I don’t have a spare £26,000 to buy a broken tractor”. It’s amazing how a four year old can find novel ways to break agricultural equipment. 🙂

Cheers

Tim…

The Fast and the Furious: Tokyo Drift…

I went to see The Fast and the Furious: Tokyo Drift last night. Like the previous films, it’s very low on story and characters, but heavy on cars and racing. I’m not really into cars, but it is quite nice to turn your brain off just watch kids race around like nutters. If you liked the previous films, this is more of the same. If not, go and see XMen-3… 🙂

Cheers

Tim…

Link Spam and Great Kick…

I’ve been getting loads of link spam recently. Unfortunately, I get it from many angles:

  • Blog Comments – I installed the “WP-Hashcash” plugin and so far it has prevented all link spam, but I’m a bit fed up of deleting the moderated spam. It is a great plugin though!
  • Article Comments – I don’t have any anti-spam protection on these, so I guess I need to do a bit of programming.
  • Forum Posts – I use phpBB, so I guess I need to hunt for some plugins to help me out.
  • Forum Users – I get loads of users signing up who don’t post, but have dodgy homepage URLs. I guess they are all trying to use my forum as a link farm. I have a visual confirmation check on signup, so either they are signing up manually, or they have a way round this security feature.

I usually get rid of these spam posts pretty quickly, but it does get a bit depressing having to clean up on a daily basis. Drastic measure may be taken soon 🙂

On a lighter note, I got kicked in the solar plexus last night at Karate. It was a perfectly timed heel bang on target and it doubled me over straight away. After a few seconds of not breathing I dropped to my hands and knees and waited for a few more seconds before my lungs started to work again. It was a rather odd because I remember feeling like I wanted to breath, puke and laugh all at the same time. Not the most normal combinaton. The funniest thing was, even though I couldn’t breath I managed to croak out the words, “That was a good kick!”. You gotta laugh 🙂

Cheers

Tim…

Update: I’ve used the “WP-Hashcash” plugin approach on my article comments, so now they must be posted from my page, and the comment must be posted within 15 minutes of the initial request for the page. Let’s see how that works 🙂

Converting Documents to Highlighted HTML using Oracle Text…

I had a question on my forum about converting documents stored in BLOBs to HTML with highlighted search terms. On reading this my instant reaction was, “I don’t have a clue!”, but it’s actually quite easy using the “CTX_DOC.MARKUP” procedure. Here’s a simple example:

https://oracle-base.com/articles/10g/ctx_doc_markup.php

Cheers

Tim…

Oracle OpenWorld and famous namesake…

One of the perks of being an Oracle ACE is getting a free pass to Oracle OpenWorld in October. Fun, fun, fun! See you in San Francisco 🙂

I noticed today that there is a famous Tim Hall from Shropshire (my original county), seen here. I must outdo him by blogging from space, or something like that 🙂

Cheers

Tim…

Enough of AJAX already…

It seems like every other post I read mentions AJAX somewhere. The whole AJAX thing seems like a total farse to me. Let’s take a quick walk through the recent history of application development:

  • Back in the day everything was host-based. Everyone had dumb terminals linked to the corporate mainframe.
  • Fast forward a few years and host-based computing is evil and client-server rules.
  • Fast forward a few years and client-server is evil and everybody wants multi-tier architecture and browser clients, which has essentially moved us back to a variation on the host-based systems with clunky interfaces.
  • Bringing us to the present, where people still want their multi-tier environments, but they want client-server style flexibility in their interfaces.

So AJAX comes to the rescue, providing client-server style controls and functionality in web pages by roping together lots of stuff we’ve been using for years. The problem is, it brings all those old problems of cross-browser compatibility back into focus.

I read an article by Frank Nimphius this morning, where he mentions some of the problems with running AJAX applications in browsers, like the “Back” button, and asks if we need an AJAX client, rather than a browser. I understand what he’s saying, but I think he’s asking the wrong question. AJAX is a cobbled together solution for a bigger problem. The problem is the browser environment, so I say fix the problem.

Browsers are good at what they were designed for, but they don’t do complex client-server type work without using poor makeshift solutions like AJAX. You can use ActiveX controls or Java Applets, but neither is universally accepted and both suffer similar problems to AJAX with respect to browser navigation. What we really need is a new standard web application client or runtime environment with all the required functionality built in. That way complex web development can move out of the dark ages. I’m not saying it’s going to be easy, but it’s got to beat the rag-tag AJAX solutions I’ve seen so far!

Cheers

Tim…