Re: How to LOCK TABLE/READ ONLY table in own schema after data insertion

From: Jagjeet Singh <jagjeet.malhi_at_gmail.com>
Date: 7 Jun 2006 05:35:30 -0700
Message-ID: <1149683730.468142.153710@i40g2000cwc.googlegroups.com>

This can be done using policies also.

Policies are there for VPD and are having log of thing/features.

It solves your purpose also ..

 We will create a function and it's returning value would be  appended for each select/update/delete statement.

 In your case we will be appending a *where caluse* for  all update and delete statements to that table.

---

SQL> create table t as select rownum r
  2 from dict where rownum < 11;

Table created.

SQL> select * from t;

         R

---

         1
         2
         3
         4
         5
         6
         7
         8
         9
        10

10 rows selected.

SQL> create or replace function mypolfunc   2 (p_schema varchar2,p_object varchar2)   3 return varchar2
  4 as
  5 begin
  6 return '1=0';
  7* end;
SQL> / Function created.

• we would be retruning '1=0' for all updates/deletes ****

SQL>
SQL>
SQL> exec

dbms_rls.add_policy(user,'T','mypol',user,'mypolfunc',STATEMENT_TYPES=>'update,delete');

PL/SQL procedure successfully completed.

• we are attaching a policy that will fire agains all updates + deletes

SQL> select * from t;

         R

---

         1
         2
         3
         4
         5
         6
         7
         8
         9
        10

10 rows selected.

SQL> delete from t;

0 rows deleted. <<--- because of '1=0'

SQL> update t set r=r ;

0 rows updated. <<---

SQL> select * from t;

         R

---

         1
         2
         3
         4
         5
         6
         7
         8
         9
        10

10 rows selected.

  Whenever you want to load database just disable the policy.

SQL> exec dbms_rls.enable_policy(user,'T','mypol',false)

PL/SQL procedure successfully completed.

SQL> delete from t;

10 rows deleted.

• even you can just put a trigger on that table for blocking update or deletes.

 create or replace trigger
 after insert or update on that table
 begin
 raise_application_error(-20001,' not allowed ' );  end;
/

sanjeev_atvankar_at_hotmail.com wrote:
> Jagjeet Singh wrote:
> > >But after inserting the data into call_by_call_hist i want to lock this
> > >table till next insert,that means no one can change the data from hist
> > >table i.e.In this hist table only insert and fire a query is possible.
> >
> > Do you want to lock the table after loading till next job.
> > or you want to block only updates.
> >
> > Regards,
> > Js

>

> Yes,I want to lock the table after loading till next job.

>

> Actually i want to use this table for (insert and select) purpose means
> NO delete and update allowed.
> but table should be in own schema means creator of this table is myself
> and i will have authority to only insert and select but NO delete and
> update.

>

> please give me elaborative solution(Possibly Tutorial or link)
>
> Thanks in advance
Received on Wed Jun 07 2006 - 07:35:30 CDT