As most of you have noticed all StackExchange sites have been under maintenance/recovery a few days ago so they switched on READ-only mode. From their blog I know they used to use MS SQL Server 2008, and recently have moved on to SQL 2012.

The question is how do you switch READ-only mode in a specific RDBMS, thus keep the service up during maintenance?

Since I use CUBRID RDBMS most of the time, I know they provide an ACCESS_MODE switch in CUBRID Broker middleware (cubrid_broker.conf) which allows DBAs to dynamically turn a READ/WRITE-enabled service to READ-only (+ ...) and vice versa. This is especially useful in an HA environment.

In MySQL as I know, even if you turn read_only=1 in my.cnf/my.ini, users with SUPER and replication privileges still can write to a read only server.

I wonder how this is done in other relational databases. How does SO do this?

  • I wouldn't mind if this question and its answers become of "community wiki" type. I'm doing research on this topic so I would appreciate very much answers specific for each database as detailed as possible. – Eye Nov 1 '12 at 9:15

I'm going to attempt to answer this question for Oracle.

Historically Oracle has never supported read-only databases.

It's possible to make a tablespace read-only: http://docs.oracle.com/cd/B28359_01/server.111/b28310/tspaces006.htm

Individual tables can be made read-only: http://www.oracle-base.com/articles/11g/read-only-tables-11gr1.php

Or it's possible to make a select-only role and assign that to the user who logs in rather than the normal readwrite-role.

|improve this answer|||||
  • 1
    There's a "funny" quirk to read-only tablespaces though: "You can drop items, such as tables or indexes, from a read-only tablespace". i.e. you can do DDL on read-only tablespaces, with strange semantics. – Mat Nov 1 '12 at 8:58
  • 1
    You a wrong. A database can be set in read only mode. Specify OPEN READ ONLY to restrict users to read-only transactions, preventing them from generating redo logs – miracle173 Nov 2 '12 at 1:09
  • @Mat: The semantic is not strange if you are aware of the diffenece between data and metadata. The important reason for allowing read only tablespaces is to handle special situation like data on read only media , tablespace export or special database upgrade procedures. In this situtations that "funny quirk" "with strange semantics" is a very useful or even necessary feature. – miracle173 Nov 2 '12 at 1:43
  • 1
    In Oracle you can also switch your current session to "READ ONLY" if you want to prevent accidental updates – a_horse_with_no_name Nov 2 '12 at 8:40
  • @a_horse_with_no_name: I was not able to find anything in the manuals. Can you post a reference that shows how to do this? – miracle173 Nov 2 '12 at 21:26

In SQL Server it's:


But a full solution is going to rely on the application as well if you want it to be unobtrusive to the user. Most applications will be logging data all the time, even if the user isn't explicitly 'writing' to the db (The 'Viewed' stat on SE pages being an obvious example) So just setting the database to read only will probably end up with lots of errors being thrown back to the user, unless the app has been written to cope with this.

|improve this answer|||||

Some PostgreSQL solutions:

  1. Create ReadOnly user and connect to database using it.

    CREATE OR REPLACE FUNCTION create_ro_user( in_username TEXT, in_password TEXT ) RETURNS VOID AS $BODY$
        IF EXISTS ( SELECT 7 FROM pg_user WHERE usename = in_username ) THEN
          RAISE NOTICE 'user [%] already exists', in_username;
          EXECUTE 'CREATE USER ' || quote_ident(in_username) || ' WITH PASSWORD ' || quote_literal(in_password);
        END IF;
    /* schemas */
        PERFORM execute( 'GRANT USAGE ON SCHEMA ' || nspname || ' TO ' || in_username )
        FROM pg_namespace
        WHERE nspname !~ '^_'
          AND nspname !~ '^pg_'
          AND nspname != 'information_schema';
    /* tables and views */
        PERFORM execute( 'GRANT SELECT ON TABLE ' || table_schema || '."' || table_name || '" TO ' || in_username )
        FROM information_schema.tables
        WHERE table_schema !~ '^_'
          AND table_schema !~ '^pg_'
          AND table_schema != 'information_schema';
    $BODY$ LANGUAGE 'plpgsql' STRICT;  


  2. REVOKE privileges from current application user:

    GRANT USAGE ON SCHEMA public TO app_user;
    GRANT SELECT ON ALL TABLES IN SCHEMA public to app_user;   

    note: I didn't test it so please correct this if there should be done something more or something else. You have to check if it will work when user have active connection to db already.

  3. You can also set default_transaction_read_only=on on connection by default, but it can by switched to off in session. More here: postgresql 9.1 doc and here: postgresql 9.1 doc

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.