Me I would just grant "read-only" prvileges to a global role:how could you make a table read-only?".
added comments to the code
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
You can also use DBMS_RLS package too for making the table read only
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.My preference too. :-)
SQL*Plus makes things harder for the developers..
8) Dump the table to a flat file and removeHemant 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?
permissions on the flat file
this file can then be made available as an "external"
table if it needs to be
accessed in SQL.