Fine-Grained Access to Network Services in 11g…

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.

Cheers

Tim…

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…