Friday, November 25, 2011

Oracle PL/SQL: Making All Tables in a Schema Read-Only

This article covers preventing casual users from writing data to some tables within an Oracle Schema. This routine will prevent the user writing data to ALL tables, but you can then just remove the constraints from the tables that you wish the users to still write data to (i.e. tables that audit the user logging in).

Recently we wanted to migrate users from an old system to a new one giving them access to both systems at the same time but making the old system "read only". It's possible, in Oracle, to set either the whole Database or a complete Table Space but we discovered that when the users connected an audit record was created. We didn't want to change the code in the existing system but wanted the guarantee the users could not update any old data. The script we came up with is:

declare
  cursor c_Tables is
    select owner, table_name from all_tables where owner = '';
begin
  for v_Table in c_Tables loop
    dbms_output.put_line(v_Table.owner || '.' || v_Table.Table_Name);
    begin
      execute immediate 'ALTER TABLE ' || v_Table.owner || '.' ||
                        v_Table.Table_Name || ' ADD CONSTRAINT ' ||
                        substr(v_Table.Table_Name, 1, 27) ||
                        '_RO check(1=1) disable validate';
    exception
      when others then
        dbms_output.put_line(v_Table.owner || '.' || v_Table.Table_Name ||
                             sqlerrm);
    end;
  end loop;
end;

This script adds a constraint to each table (called _RO, for example the MASTER_BATCH_REC_HDR table would have a constraint called MASTER_BATCH_REC_HDR_RO. Of course if you use the full 30 characters for lots of your table names you might run into a problem if the first 27 characters are the same!

This constraint can never be evaluated so every insert/update will fail but select statements will still run.

Of course the problem with audit records is that you still have to insert them so after a few tries running the application (and reading the error messages) we worked out which tables to drop the existing constraint from in order to allow the audit records to be inserted but no more.

 


Post a Comment