Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
This is very DBMS specific! I hope people will make one answer for each DBMS. –  Colin 't Hart Nov 1 '12 at 8:29
    
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 think this is not a good question because will be several correct answers to this questions (at least one for each DBMS) the question for which DBMS will you accept as the right one? I think the question should be posed for each databases you are interested in. –  miracle173 Nov 2 '12 at 1:16
    
Thank you for your feedback. I've seen great questions with answers which include all databases in one which are accepted and preferred then those which provide only one answer. In any case I will vote up all valid answers and accept the one I think provides more complete answer even if it's related to only a single database. –  Eye Nov 2 '12 at 1:42

3 Answers 3

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.

share|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
    
Oh, I didn't know about this fact in Oracle. Thank you for your answer! –  Eye Nov 1 '12 at 9:11
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

In SQL Server it's:

ALTER DATABASE <dbname> SET READ_ONLY;

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.

share|improve this answer
    
Thank you for the answer! And yes, I assume the fact that the Web app has to be designed to handle Read-only mode. In SE this time, for instance, most "writable actions" have been automatically disabled which is smart! –  Eye Nov 1 '12 at 9:10

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$
      BEGIN
        IF EXISTS ( SELECT 7 FROM pg_user WHERE usename = in_username ) THEN
          RAISE NOTICE 'user [%] already exists', in_username;
        ELSE
          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';
      END;
    $BODY$ LANGUAGE 'plpgsql' STRICT;  
    

    EDIT:

  2. REVOKE privileges from current application user:

    REVOKE CREATE ON SCHEMA public FROM PUBLIC;
    REVOKE USAGE ON SCHEMA public FROM PUBLIC;
    REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA public from app_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

share|improve this answer
    
Thank you for your answer. One problem with this solution is that you have to change your application code to reflect this new read-only user. Is there a way to do this on DB side only? –  Eye Nov 2 '12 at 1:32
1  
@Eye I have extended my answer wiht point 2. –  sufleR Nov 2 '12 at 8:22

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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