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

User created.

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

Grant succeeded.


User created.

sys@db10g> GRANT CONNECT TO test_user2;

Grant succeeded.

sys@db10g> CONN test_user1/test_user1@db10g
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
test_user2@db10g> DECLARE
2    l_id;
4    SELECT id
5    INTO   l_id
6    FROM   test_user1.test_tab1
7    WHERE  id = 1
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.


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!



Author: Tim...

DBA, Developer, Author, Trainer.

6 thoughts on “Is read-only access to production systems safe?”

  1. Amazing timing Tim, this very afternoon we discovered that users in the US were connecting to one of our production DBs via toad and causing problems.

    One of our DBAs had granted them direct access (as opposed to restricting them to using APIs) without letting the rest of the group know. Looks like read only access isn’t going to guarantee much either!

  2. Well, user IS a four-letter word after all ;-D. As a wiser man than I once said, “It’s impossible to make something foolproof because they keep making better fools”.
    I’ve come to the conclusion that there is no real SAFE access you can give users except none ;-D. At least we have security at the data level – that’s SOMETHING.

  3. You really need to define SAFE here.
    Are you concerned with malicious activity or incompetance ?
    Could it give read access to data that they shouldn’t have (credit card numbers for example) ?
    Do they have visibility of views like DBA_USERS ? That might be a starting off point for hacking other accounts. Even ALL_USERS gives a possible starting point.
    Can they execute procedures like UTL_FILE, UTL_SMTP ?
    Are you concerned about Denial of Service attacks. Even without a SELECT privilege, they may be able to set off anonymous PL/SQL blocks that burn up CPU in an infinite loop.
    The ALTER SESSION allows some ‘heavy’ session level parameters to be set (eg hash size) that could allow one session to impact others on the database (and they don’t even need ALTER SESSION privilege).

    “At least we have security at the data level – that’s SOMETHING.”
    Except for the occasional Oracle bug, and there’s been a few whoppers recently.
    The January CPU had a fix for one bug that allowed anyone with a valid username/password for a database to get DBA access. There’s also been holes allowing SQL injections using standard Oracle packages with grants to PUBLIC.

  4. Graham: I’ve had to do this on more than one occasion. You can’t guard against a boss that says, “Just do it!”

    Bill S: I’m all for no access πŸ™‚

    Gary: I meant safe in the context of the forum thread. I guess I should have made that clearer πŸ™ In this case we were discussing analysts in the company looking at data using tools like TOAD that can lock rows without the users knowledge. I wasn’t considering hacking by internal or external users. That’s too big a issue for a post here πŸ™‚



  5. Could I ask a question then with regards to oracle

    My company had decided to do away with our Data Warehouse and run all BI reports (pulling 2 years worth of data – average 120 page reporting touching 8-10 dimensions)

    straight over the top of our operational ERP system which runs on oracle

    would that be a bad idea?

  6. Hi.

    The answer, as always, is it depends!

    Typically, BI reports and OLTP systems don’t mix well because you have two very different workloads competing for resources. How good or bad an idea it is depends on your circumstances.

    If you have great kit and a low level workload, then maybe the additional overhead of the BI reports will be fine. If of course this is not the case, then your system will probably run like a dead dog once the workloads are combined.

    I guess the obvious thing to do is to test it.



Comments are closed.