1 2 Previous Next 17 Replies Latest reply: May 29, 2010 12:32 AM by 737189 RSS

    Slightly off topic: Read-only tables pre 11g

    Chris Muir-Oracle
      Hi gang

      I'm just writing up a database quiz for a local user group and I was hoping I could get a bit of inspiration from the database experts.

      One of the questions will be "prior to 11g with the introduction of read-only tables, how could you make a table read-only?". The answers I've come up with:

      1) Security priviliges (schema + grant SELECT)
      2) Triggers
      3) Create a check constraint with disable validate
      4) Read-only tablespace
      5) Read-only database (standby)
      6) (Slightly crazy) Create view, and instead-of triggers that do nothing (similar to 2)
      7) Write the query results on a piece of paper and then turn the database off

      Anybody have any other answers, real or slightly off topic like mine please? ;)

      Cheers,

      CM.
        • 1. Re: Slightly off topic: Read-only tables pre 11g
          26741
          8) Dump the table to a flat file and remove permissions on the flat file
          this file can then be made available as an "external" table if it needs to be
          accessed in SQL.
          • 2. Re: Slightly off topic: Read-only tables pre 11g
            442613
            You can also use DBMS_RLS package too for making the table read only

            -Amit
            http://askoracledba.wordpress.com/
            • 3. Re: Slightly off topic: Read-only tables pre 11g
              Chris Muir-Oracle
              Thanks gang! :)

              CM.
              • 4. Re: Slightly off topic: Read-only tables pre 11g
                311441
                Hi Chris

                Interesting question.

                I've just posted another possible option on my Blog:

                http://richardfoote.wordpress.com/2008/05/15/read-only-table-before-11g-a-day-in-the-life/

                Cheers

                Richard Foote
                http://richardfoote.wordpress.com/
                • 5. Re: Slightly off topic: Read-only tables pre 11g
                  108476
                  how could you make a table read-only?".
                  Me I would just grant "read-only" prvileges to a global role:

                  create role myusers . .
                  grant select on mytab to myusers;

                  --------------------

                  Hope this helps. . .

                  Donald K. Burleson
                  Oracle Press author
                  Author of "Oracle Tuning: The Definitive Reference":
                  http://www.dba-oracle.com/bp/s_oracle_tuning_book.htm
                  • 6. Re: Slightly off topic: Read-only tables pre 11g
                    311441
                    Hi Don

                    How does that prevent the owner of the table from making modifications or from granting DML privileges to other users ?

                    Cheers

                    Richard Foote
                    http://richardfoote.wordpress.com/
                    • 7. Re: Slightly off topic: Read-only tables pre 11g
                      APC
                      Can I just say I don't think this thread is "off topic" in the slightest. Helping people out with pub quizzes is at least as relevant as doing other people's homework. And I think it has generated enough different solutions to serve as a useful resource.

                      Not least for any members of Chris's group who happen to be surfing the forum.....

                      Cheers, APC

                      Blog : http://radiofreetooting.blogspot.com/
                      • 8. Re: Slightly off topic: Read-only tables pre 11g
                        426850
                        Check constraint and trigger solutions may have problems with sqlldr direct path operations, so using it together with alter table disable lock may be mandatory depending on the needs. Especially if DDLs are also wanted to be avoided.

                        This topic was once mentioned on Tom Kyte's blog or asktom but I couldn't find the source to link here.
                        SQL> conn hr/hr
                        Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 
                        Connected as hr
                        
                        -- cleaning objects
                        SQL> drop table tong purge ;
                        
                        Table dropped
                        
                        SQL> drop view vw_tong ;
                        
                        View dropped
                        
                        -- creating the demo table
                        SQL> create table tong ( col1 number ) ;
                        
                        Table created
                        
                        SQL> alter table tong add constraint cc_tong check ( 1=0 ) disable validate;
                        
                        Table altered
                        
                        SQL> alter table tong disable table lock;
                        
                        Table altered
                        
                        -- some DDL tests
                        SQL> drop table tong ;
                        
                        drop table tong
                        
                        ORA-00069: cannot acquire lock -- table locks disabled for TONG
                        
                        SQL> truncate table tong ;
                        
                        truncate table tong
                        
                        ORA-25128: No insert/update/delete on table with constraint (HR.CC_TONG) disabled and validated
                        
                        SQL> alter table tong parallel ;
                        
                        alter table tong parallel
                        
                        ORA-00069: cannot acquire lock -- table locks disabled for TONG
                        
                        SQL> lock table tong in exclusive mode ;
                        
                        lock table tong in exclusive mode
                        
                        ORA-00069: cannot acquire lock -- table locks disabled for TONG
                        
                        -- some DML tests
                        SQL> select * from tong ;
                        
                              COL1
                        ----------
                        
                        SQL> update tong set col1 = col1 + 1 ;
                        
                        update tong set col1 = col1 + 1
                        
                        ORA-25128: No insert/update/delete on table with constraint (HR.CC_TONG) disabled and validated
                        
                        -- creating dependent objects test
                        SQL> create index nui_tong on tong(col1) nologging ;
                        
                        Index created
                        
                        SQL> create view vw_tong as select * from tong ;
                        
                        View created
                        added comments to the code

                        Message was edited by:
                        TongucY
                        • 9. Re: Slightly off topic: Read-only tables pre 11g
                          Billy~Verreynne
                          > You can also use DBMS_RLS package too for making the table read only

                          My preference too. :-)

                          We dealt with this exact same question a while back in the PL/SQL forum. I posted a basic example there using FGAC in [url http://forums.oracle.com/forums/thread.jspa?messageID=2072624&#2072624]this thread.
                          • 10. Re: Slightly off topic: Read-only tables pre 11g
                            Karthick_Arp
                            FGAC is a great idea. I also tried for a table. Here it is.

                            http://karthickarp.blogspot.com/2008/05/read-only-table.html

                            But in your example you have not handled INSERT i believe.

                            Regards,

                            Karthick.
                            http://karthickarp.blogspot.com/
                            • 11. Re: Slightly off topic: Read-only tables pre 11g
                              APC
                              You can also use DBMS_RLS package too for making the table read only
                              My preference too. :-)
                              FGAC is quite complicated to work with. Just the mere fact that we don't get meaningful errors in SQL*Plus makes things harder for the developers.. Also it is only available in the Enterprise Edition.

                              Cheers, APC

                              Blog : http://radiofreetooting.blogspot.com/
                              • 12. Re: Slightly off topic: Read-only tables pre 11g
                                Billy~Verreynne
                                > FGAC is quite complicated to work with. Just the mere fact that we don't get meaningful errors in
                                SQL*Plus makes things harder for the developers..

                                Er.. and just what is the point there? <confused look> :-)

                                > Also it is only available in the Enterprise Edition.

                                Interestingly, this is about the only edition we use. So we're kind of spoiled and consider enterprise features basically as the "norm". Kinda nice...
                                • 13. Re: Slightly off topic: Read-only tables pre 11g
                                  426850
                                  8) Dump the table to a flat file and remove
                                  permissions on the flat file
                                  this file can then be made available as an "external"
                                  table if it needs to be
                                  accessed in SQL.
                                  Hemant in first sight this seems to be yet another interesting choice, but do you really think this can be a real option for this need since external tables can not be indexed(wouldn't it be nice to have this feature :) and can be only accessed full and parallel?
                                  • 14. Re: Slightly off topic: Read-only tables pre 11g
                                    26741
                                    Select the option depending on your needs and your constraints.

                                    This is doable for a small table, certainly.

                                    We were listing the options available -- for a quiz
                                    not necessarily a "tightly constrained real world
                                    scenario" but just to list all possible options.
                                    1 2 Previous Next