I’ve just posted an article on SecureFiles in Oracle 11g. It looks like Oracle have done a pretty good job of improving LOBs in 11g. Depending on the LOB contents, and provided you can cope with the processing overhead, you can certainly save some serious space using the compression and deduplication options. Anyone who’s used Transparent Data Encryption (TDE) will recognize the encryption options.
I can’t see the old-style (BasicFile) LOBs lasting very long now this is in place. 🙂
There’s some good stuff out on the net today:
- Tom is back and talking about the level of skills in the IT industry. I think we all share his pain. 🙂
- Mary Ann Davidson has a great piece on security. This links in nicely with what Tom was saying, in so far as it relates to skills with the IT industry, albeit from a security standpoint.
- Continuing the security theme, Pete Finnigan made some comments on my recent article on fine grained network access controls. I still get a kick out of being mentioned by famous bloggers, even if it is an attempt to keep me on the straight and narrow. Thanks for the heads-up Pete. 🙂
- Jake from Oracle AppsLab has a piece on the move back to the desktop. I find myself having conversations on this subject so often these days.
- Finally, David Aldridge is trying to add fuel to a fire. 🙂
Sorry to everyone I didn’t mention. 🙂
Continuing my OCP 11g upgrade campaign, I’ve been looking at Fine-Grained Access to Network Services in Oracle Database 11g Release 1.
This represents a pretty major security improvement for Oracle 11g. In previous versions the all-or-nothing security associated with database callouts was a little ham-fisted to say the least.
On the down side, I think it will confuse a few people when they are upgrading existing databases, but it’s a small price to pay for the peace of mind.
Someone on my forum was having a problem with the Secure External Password Store feature and to be honest I hadn’t got a clue because I had never used this feature. A few minutes of messing about with it resulted in this:
I was trying to explain to a colleague the concept of using application users, rather than logging directly into the schema owner. Although it’s a very basic point, it seemed worthy of a write-up, especially because it’s been a long time since I’ve written anything about Oracle. So here is it:
Schema Owners and Application Users
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
sys@db10g> CREATE USER test_user1 IDENTIFIED BY test_user1 QUOTA UNLIMITED ON USERS;
sys@db10g> GRANT CONNECT, CREATE TABLE TO test_user1;
sys@db10g> CREATE USER test_user2 IDENTIFIED BY test_user2 QUOTA UNLIMITED ON USERS;
sys@db10g> GRANT CONNECT TO test_user2;
sys@db10g> CONN test_user1/test_user1@db10g
test_user1@db10g> CREATE TABLE test_tab1 (
2 id NUMBER
test_user1@db10g> INSERT INTO test_tab1 (id) VALUES (1);
1 row created.
test_user1@db10g> GRANT SELECT ON test_tab1 TO test_user2;
test_user1@db10g> CONN test_user2/test_user2@db10g
2 l_id test_user1.test_tab1.id%TYPE;
4 SELECT id
5 INTO l_id
6 FROM test_user1.test_tab1
7 WHERE id = 1
8 FOR UPDATE;
PL/SQL procedure successfully completed.
test_user2@db10g> LOCK TABLE test_user1.test_tab1 IN EXCLUSIVE MODE;
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!