Make table read-only
Ralf Zwanziger
(without moving it to a read-only tablespace).
I haven't found any command like "alter table xxx read only" in the
docs.
Bye,
Ralf
Mark C. Stock
"Ralf Zwanziger" <ralf_o...@gmx.de> wrote in message
news:7n57n0t979rtkrvpc...@4ax.com...
the owner (owning schema) always has full privileges -- if you just grant
SELECT to end-users (end-user schemas) then the table is read-only to them
are you targeting security issues or backup issues?
++ mcs
Mladen Gogala
"Truncate table" will prevent users from updating any row in the table.
It will not prevent inserts.
--
Artificial Intelligence is no match for natural stupidity.
Howard J. Rogers
No, there's no such concept. Except, as Mark notes elsewhere, that if a user
only has permissions to select, then -for that user- the table is
effectively read-only. But the table's owner will always have read-write
access to it.
But what is the problem with moving it to a read-only tablespace? That,
after all, is precisely what tablespaces are there for: to allow your
database to be chopped up into small pieces that have different
administrative requirements. If you have a table which is now read-only,
its characteristics have changed, and it should be handled accordingly.
What's the actual business requirement/problem here, then? And why is moving
the table not an option?
Regards
HJR
Mark C. Stock
"Mladen Gogala" <gog...@sbcglobal.net> wrote in message
news:pan.2004.10.18....@sbcglobal.net...
Mladen, I sure hope you're not serious...
TRUNCATE TABLE is as command, not a privilege, and it will prevent users
from reading any data in the table, because it deletes all rows -- and
afterwards inserts (by users with INSERT privileges) are not only allowed,
but many, many, many will be necessary to restore the data destroyed by the
TRUNCATE TABLE command!
++ mcs
Ralf Zwanziger
>It will not prevent inserts.
Very funny :)
drop table will do the same :)
Pete Finnigan
Further to Howard, Mladin and Marks comments you might find interesting
an entry in my Oracle security weblog which you can find here:-
http://www.petefinnigan.com/weblog/archives/00000009.htm that talks
about some of the issues with creating read only users. I know you want
a read only table but it may still be of interest.
kind regards
Pete
--
Pete Finnigan (email:pe...@petefinnigan.com)
Web site: http://www.petefinnigan.com - Oracle security audit specialists
Oracle security blog: http://www.petefinnigan.com/weblog/entries/index.html
Book:Oracle security step-by-step Guide - see http://store.sans.org for details.
Robert
> It will not prevent inserts.
what the HELL are you trying to do to people ???
That's pretty evil.
Michel Cadot
"Ralf Zwanziger" <ralf_o...@gmx.de> a écrit dans le message de
news:7n57n0t979rtkrvpc...@4ax.com...
You can add a trigger before insert, update or delete that raise an error, something like:
create or replace my_trigger
before insert or update or delete on my_table
begin
raise_application_error (-20001,'Updating the table is not allowed');
end;
/
This will work for all including the owner of the table.
--
Regards
Michel Cadot
Howard J. Rogers
Except of course thatinsert triggers don't fire if you are doing a direct
load insert or a direct path SQL Loader load.
So yes, it's a good point, but it doesn't *necessarily* achieve what the
user wanted.
Regards
HJR
Steve Howard
In addition to the other posts, I will just add that you could,
although not as desirable, add a trigger which would prevent any
changes.
SQL> create table t(c number);
Table created.
SQL> declare
2 i number;
3 begin
4 for i in 1..100 loop
5 insert into t values(i);
6 end loop;
7 end;
8 /
PL/SQL procedure successfully completed.
SQL> commit;
Commit complete.
SQL> create or replace trigger t_trg
2 before delete or update or insert on t
3 for each row
4 begin
5 RAISE_APPLICATION_ERROR(-20999,'You can''t make changes to the
data!');
6 end;
7 /
Trigger created.
SQL> update t set c = 12;
update t set c = 12
*
ERROR at line 1:
ORA-20999: You can't make changes to the data!
ORA-06512: at "SCOTT.T_TRG", line 2
ORA-04088: error during execution of trigger 'SCOTT.T_TRG'
SQL> delete from t;
delete from t
*
ERROR at line 1:
ORA-20999: You can't make changes to the data!
ORA-06512: at "SCOTT.T_TRG", line 2
ORA-04088: error during execution of trigger 'SCOTT.T_TRG'
SQL> insert into t values(101);
insert into t values(101)
*
ERROR at line 1:
ORA-20999: You can't make changes to the data!
ORA-06512: at "SCOTT.T_TRG", line 2
ORA-04088: error during execution of trigger 'SCOTT.T_TRG'
SQL> select count(*) from t;
COUNT(*)
----------
100
SQL>
...just another alternative
Regards,
Steve
Michel Cadot
"Howard J. Rogers" <h...@dizwell.com> a écrit dans le message de
news:41741292$0$20129$afc3...@news.optusnet.com.au...
> "Michel Cadot" <micadot{at}altern{dot}org> wrote:
>
> >
> > news:7n57n0t979rtkrvpc...@4ax.com...
> >> Is ist possible to make an oracle table read-only?
> >> (without moving it to a read-only tablespace).
> >> I haven't found any command like "alter table xxx read only" in the
> >> docs.
> >>
> >> Bye,
> >> Ralf
> >>
> >
> > You can add a trigger before insert, update or delete that raise an error,
> > something like:
> >
> > create or replace my_trigger
> > before insert or update or delete on my_table
> > begin
> > raise_application_error (-20001,'Updating the table is not allowed');
> > end;
> > /
> >
> > This will work for all including the owner of the table.
>
> Except of course thatinsert triggers don't fire if you are doing a direct
> load insert or a direct path SQL Loader load.
You're perfectly right!
Howard J. Rogers
> Ralf Zwanziger <ralf_o...@gmx.de> wrote in message
> news:<7n57n0t979rtkrvpc...@4ax.com>...
>> Is ist possible to make an oracle table read-only?
>> (without moving it to a read-only tablespace).
>> I haven't found any command like "alter table xxx read only" in the
>> docs.
>>
>> Bye,
>> Ralf
>
> In addition to the other posts, I will just add that you could,
> although not as desirable, add a trigger which would prevent any
> changes.
No... the word "any" is incorrect there, as I posted earlier today... but
here's proof. I'll follow your (excellent) example, mostly.
SQL> connect scott/tiger
SQL> create table t(c number);
Table created.
SQL> declare
2 i number;
3 begin
4 for i in 1..100 loop
5 insert into t values(i);
6 end loop;
7 end;
8 /
PL/SQL procedure successfully completed.
SQL> commit;
Commit complete.
SQL> create or replace trigger t_trg
2 before delete or update or insert on t
3 for each row
4 begin
5 RAISE_APPLICATION_ERROR(-20001,'No DML!');
6 end;
7 /
Trigger created.
SQL> update t set c = 12;
update t set c = 12
*
ERROR at line 1:
ORA-20999: No DML!
ORA-06512: at "SCOTT.T_TRG", line 2
ORA-04088: error during execution of trigger 'SCOTT.T_TRG'
SQL> insert /*+ append */ into t nologging select * from t;
insert /*+ append */ into t nologging select * from t;
*
ERROR at line 1:
ORA-20999: No DML!
ORA-06512: at "SCOTT.T_TRG", line 2
ORA-04088: error during execution of trigger 'SCOTT.T_TRG'
[Which, incidentally, goes to prove that I was wrong earlier to state that
insert triggers don't fire during direct load inserts. They do, clearly].
SQL> select count(*) from t;
COUNT(*)
----------
100
[New terminal window]
[oracle@opal oracle]$ sqlldr scott/tiger control=t.ctl direct=true
SQL*Loader: Release 10.1.0.2.0 - Production on Tue Oct 19 06:00:58 2004
Load completed - logical record count 8.
[oracle@opal oracle]$ sqlplus scott/tiger
SQL> select count(*) from t;
COUNT(*)
----------
108
(The t.ctl simply asked to load t.csv into table t. T.csv in its turn was
just a list of numbers 1 to 8).
Direct SQL Loads really *don't* fire insert triggers, and hence the table
isn't truly read only.
My only point here is not to rubbish the idea of before DML triggers,
because they clearly rule out most DML possibilities. But I'd hate someone
to rely on it as a means of, say, "guaranteeing" the data is free from all
changes from this point on, because it's not. I doubt it would pass
'archiving' muster with the tax office, for example.
Regards
HJR
Hans Forbrich
You are correct that you can not alter a table to become r/o. So far
read-only is not an attribute of table, but is a security mechanism
controlled by the 'ACL. SO you need to look to the security subsystem, not
the object subsystem.
What is your basic security model? Do you allow everyone to access the
userid that owns the schema (and therefore the table)? Who will not be
allowed to update the table? When will the table be 'Read-Only'?
There are several ways to accomplish this. I show one long-winded variant at
the bottom, based on the following model:
- All users log in under their own userid (otherwise how do you tell it's
user A vs user B???);
- Each user is granted access to the table(s), view(s) and other database
objects, either explicitly or through a role;
- The Grant command allows the administrator to decide which combination of
SELECT, UPDATE, INSERT, DELETE a user/role gets to use;
- A 'GRANT SELECT TO user' without any additional UPDATE, INSERT or DELETE
is equiv. to read-only.
A read only table could be accomplished by creating the table under a
separate schema, exposing that table to the primary schema via a view,
granting only select on the table to the 'public' schema, and exposing the
view to the rest of the world. Following show that: ro_schema contains
the table, gen_schema contains the view, ro_user is the end user denied
update.
SQL*Plus: Release 9.2.0.1.0 - Production on Thu Oct 18 13:09:45 2001
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
SQL> create user gen_schema identified by x;
User created.
SQL> create user ro_schema identified by x;
User created.
SQL> grant connect, resource to gen_schema, ro_schema;
Grant succeeded.
SQL> create user ro_user identified by x;
User created.
SQL> grant connect to ro_user;
Grant succeeded.
SQL> connect ro_schema/x@ora1
Connected.
SQL> create table abc (
2 a1 varchar2(4) );
Table created.
SQL> insert into abc values ('A1');
1 row created.
SQL> insert into abc values ('B2');
1 row created.
SQL> commit;
Commit complete.
SQL> grant select on abc to gen_schema with grant option;
Grant succeeded.
SQL> connect gen_schema/x@ora1
Connected.
SQL> create view abc as select * from ro_schema.abc;
View created.
SQL> select * from abc;
A1
----
A1
B2
SQL> insert into abc values ('C2');
insert into abc values ('C2')
*
ERROR at line 1:
ORA-01031: insufficient privileges
SQL> grant select on abc to ro_user;
Grant succeeded.
SQL> connect ro_user/x@ora1
Connected.
SQL> select * from ro_schema.abc;
select * from ro_schema.abc
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> select * from gen_schema.abc;
A1
----
A1
B2
SQL> select * from abc;
select * from abc
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> create synonym abc for gen_schema.abc;
Synonym created.
SQL> select * from abc;
A1
----
A1
B2
SQL> insert into abc values ('D1');
insert into abc values ('D1')
*
ERROR at line 1:
ORA-01031: insufficient privileges
SQL> update abc set A1='C2' where A1='B2';
update abc set A1='C2' where A1='B2'
*
ERROR at line 1:
ORA-01031: insufficient privileges
SQL>
DA Morgan
For the table owner ... No!
But if another schema ... just grant SELECT and don't grant any other
object privilege.
--
Daniel A. Morgan
University of Washington
damo...@x.washington.edu
(replace 'x' with 'u' to respond)
DA Morgan
And whether serious or not I would suggest that such suggestions NEVER
be made as it is quite possible that some newbie or lurker might
actually try it not realizing the impact.
DA Morgan
That was my reaction as well. I think if an attempt at humor a
very poor one and in very bad taste.
Steve Howard
> My only point here is not to rubbish the idea of before DML triggers,
> because they clearly rule out most DML possibilities. But I'd hate someone
> to rely on it as a means of, say, "guaranteeing" the data is free from all
> changes from this point on, because it's not. I doubt it would pass
> 'archiving' muster with the tax office, for example.
>
> Regards
> HJR
You are correct. The other one I thought about after I posted is the
necessity for a before DDL trigger for "DROP TABLE." At some point,
I'm thinking, what exactly are we doing here?!! ;)
Howard J. Rogers
Yeah, even putting the table into a read only tablespace won't stop DDL
trashing it. Practically, even in R/O tablespace, it will need a before DDL
trigger to stop that sort of thing ....so that's a good call, too!
Regards
HJR
Fabrizio
to make a table "read only" set a constraint to "DISABLE VALIDATE".
No delete, update or insert will be permitted on that table.
If there is no constraint you can create a "dummy" one.
Never tested but it should work.
Regards
Fabrizio
Howard J. Rogers
> This is a trick I read while studying for OCP:
>
> to make a table "read only" set a constraint to "DISABLE VALIDATE".
> No delete, update or insert will be permitted on that table.
> If there is no constraint you can create a "dummy" one.
>
> Never tested but it should work.
>
> Regards
> Fabrizio
It's not a trick. The disabled novalidate state of a constraint is there to
lock a segment from having DML performed against it whilst index or other
maintenance is being performed.
You can't drop an index if it's used to enforce a constraint. To drop it,
you disable the constraint -but that would allow users to pile all sorts of
violating records into the table. So in 8i and above, you can disable but
VALIDATE, meaning the constraint's switched off (so the index can be
dropped) but the table is locked, so no violating records can be introduced
into it whils your index maintenance activity takes place.
The essence of that scenario is that the constraint is TEMPORARILY disabled,
but validated, only for as long as it takes to perform your maintenance
operations. The intention on Oracle's part at least was, and is, that the
constraint should be fully enabled again once those operations are
finished.
Your suggestion is therefore certainly clever, but it again is a dangerous
one, in the sense that it doesn't *really* make the table read only
(because I can always alter table X enable constraint Y). It would be a
risky proposition to think 'my data is safe' using this technique, because
it's safe only so long as no-one re-enables the constraint.
It is also risky, because altering the status of constraints can affect the
optimiser, so execution plans could go awry.
And it is a management nightmare, too, because you would not ordinarily want
a constraint to be left in the disabled state permanently -but presumably
this is now an exception... and exceptions are always a pain to administer.
I wouldn't be recommending this approach for long-term (more than a few
hours) in a production environment as a way of protecting data against DML
activity.
Regards
HJR
MS
the trick.
So, for the mentioned example:
SQL> alter table t disable table lock;
This prevents sqlldr direct load, as well as truncate, and DDL's on
this table (including drop table).
-Madhu S
Mark C. Stock
"MS" <qc...@attbi.com> wrote in message
news:a2b6d46b.04101...@posting.google.com...
interesting
i don't think i've seen mentioned that the table owner can have her account
disabled or create session privilege revoked -- but that affects more than
just the one table, although it might fit into a broader scenario
++ mcs
Fabrizio
This solution was only a short note in a book and I never thought
thoroughly about it (I'm even not sure how I could remember it). Clearly
your understanding of oracle concepts is better than mine.
I only believed there was a simple way to prevent DMLs on a table even
for the owner (I still have to test it) so, in my opinion, the answer to
the original question should be "yes".
It happened I missed a very marginal part: the cons... :(
Kind Regards
--
Fabrizio Magni
replace mycontinent with europe
Asif Merchant
primary key(c1)
2 deferrable initially immediate )
3 /
Table created.
SQL> insert into t2 values (1, 'A');
1 row created.
SQL> commit;
Commit complete.
SQL> alter table t2 modify constraint pk_t1 disable validate;
SQL> insert into t2 values (1,'B');
insert into t2 values (1,'B')
*
ERROR at line 1:
ORA-25128: No insert/update/delete on table with constraint
(TESTUSER123.PK_T1) disabled and validated
SQL> select * from t2;
C1 C2
---------- ----------
1 A
DA Morgan <damo...@x.washington.edu> wrote in message news:<1098158539.523979@yasure>...
Howard J. Rogers
> SQL> create table t2 ( c1 int,c2 varchar2(10), constraint pk_t1
> primary key(c1)
> 2 deferrable initially immediate )
> 3 /
>
> Table created.
>
> SQL> insert into t2 values (1, 'A');
>
> 1 row created.
>
> SQL> commit;
>
> Commit complete.
>
> SQL> alter table t2 modify constraint pk_t1 disable validate;
>
> SQL> insert into t2 values (1,'B');
> insert into t2 values (1,'B')
> *
> ERROR at line 1:
> ORA-25128: No insert/update/delete on table with constraint
> (TESTUSER123.PK_T1) disabled and validated
>
> SQL> select * from t2;
>
> C1 C2
> ---------- ----------
> 1 A
Yes, someone else has already suggested this today -and as I pointed out,
it's risky because it will affect execution plans, and be a pain to manage
long-term.
This thread is certainly interesting, but it's getting a bit dangerous if
you ask me. What you have done here is not to make a table read-only, but
to employ a perfectly legitimate technique to achieve utterly unreliable
ends.
A constraint can be re-enabled at any time, and that would not be easy to
spot being done. The data in the table is thus rendered immediately
suspect.
In other words: consider why someone would want to make their table
read-only. Perhaps it is for audit reasons. Perhaps it is for historic
archiving reasons. Perhaps it is for legal reasons. Perhaps it is because
someone doesn't want/need to keep backing a table up night after night.
All of those possible reasons would be seriously compromised by this DISABLE
VALIDATE technique, because there is no guarantee that the constraint stays
disabled.
Compare that to doing the "official" thing, which is to move the table into
read-only tablespace (and potentially then to move the data files involved
onto some sort of read-only media). That tablespace cannot be made
read-write again except by issuing a command which is recorded in the alert
log -which means attempts to compromise the table's data integrity are
highly visible. (And if you've moved the data files onto read-only media,
even the attempt to make it read-write would fail). The only other way to
do it would be to move the table into a new read-write tablespace -and that
(assuming for a moment the table is quite large) would not again be a
trivial operation that could sneak under the radar.
So this suggestion is certainly inventive. As is the one about disabling the
table lock. But they are all reversible with near-invisible commands (or
require extra measures already mentioned in this thread, such as before DDL
triggers, to make them 'stick'... and the dropping or invalidating of a
trigger is a trivial and near-invisible operation, too, to a determined
hacker).
So, if security and assurance and auditability and simplicity are what the
original poster was after, then I still think that the move to a read-only
tablespace is the only practical and safe answer. The disabling of the
table lock was indeed neat, however! A shame it is so readily (and
sneakily) reversible.
Regards
HJR
Michel Cadot
As using read only tablespace is an excluded option for the OP, we can go
deeply in the other solutions.
We can avoid the re-enabling of the constraint or locks with a
"before alter" trigger (neglecting all your warnings on these solutions).
Another solution may be to create a read only snapshot with no refresh
and use it instead of the table and protect the snapshot with a "before alter"
trigger.
--
Regards
Michel Cadot
Howard J. Rogers
I'm not suggesting that the other options are not interesting and
intriguing. And perhaps they will indeed help the original poster (who has
not said that rebuilding to another tablespace is a complete and utter
no-no, merely that he was hoping for a quick and inexpensive option that
would achieve the same thing. So actually, we don't know whether it is a
truly excluded option, or whether he's just looking for a quick fix. That's
how I remember his post, anyway).
But that's the point. These other options don't achieve the same thing as
making a tablespace read-only, and they therefore might not actually *be*
solutions for him.
It all depends on what the reason for making something read-only is, doesn't
it? (Which I don't think we know in this particular case). If its for audit
or history or legal reasons, and the table must be read-only long term and
without any possibility of sneaking in modifications when no-one's looking,
then these 'alternatives' just don't pass muster, as I say. The only one
that counts in that league is "proper" read-only tablespace. But if the
requirement is for something to flip a table into a locked state
temporarily, over lunch, whilst the DBA is downing a few glasses, then the
alternatives that have been suggested in this entertaining thread are
perfectly viable.
> We can avoid the re-enabling of the constraint or locks with a
> "before alter" trigger (neglecting all your warnings on these solutions).
But 'drop trigger X' is NOT an audited statement, is it? Of course, you
*could* audit that statement, like any other. But then who polices the AUD$
table?
However, alter tablespace read write *is* audited, in the alert log, which
can be written to an operating system directory to which the DBA has no
access, however powerful he is on the database itself.
That's my point. Anything that uses triggers to achieve "read only" status
for a table is fooling management if they need to place reliance on that
status. Certainly it works for an hour. Or three. But not forever. So if
"forever" is what is needed, by the original poster or anyone else, then
triggers can't count. Neither can disabling the table locks, or disable
novalidating the constraints.
Again: it comes down to the real business requirement here. Imagine a table
which MUST remain read only for seven years. Even a one minute interlude in
read-write mode would be disastrous from the legal point of view. So, even
if you could guarantee to audit the disabling of the trigger, or the
enabling of the constraint, that wouldn't help: finding out about it after
the event is too late; the damage has been done, the table has had a
read-write interlude. God knows what happened to its data in that one
minute. There is only one option that *guarantees* no interludes. Triggers
and code and alter table statements don't past that most stringent of
tests.
But, I accept that we still don't know regarding the original poster whether
that most stringent of tests (or something like it) is what he is actually
after.
> Another solution may be to create a read only snapshot with no refresh
> and use it instead of the table and protect the snapshot with a "before
> alter" trigger.
Same deal. It doesn't past legal and auditing stringency standards as a
technique.
The other danger with this thread is in its potential for myth-making. If
the question is "Can I make a table read-only" the answer is, and must be,
No. Because otherwise, a myth will arise that there is a fundamentally
reliable way of doing it. But there isn't, except making the data files
themselves read-only. And that *requires* the use of a read-only
tablespace. Back to square one.
Precision of language, again, I guess. Technically, you cannot make a table
read-only. You can only make it *appear* (or behave, if you prefer)
read-only for a period of time. Which might be good enough sometimes. But
it's not -fundamentally- the same thing.
Are there subtleties to the answer we can provide for those with less
stringent requirements? Yes... but we shouldn't say that those make a table
read-only. I don't know what we call it, actually. Maybe we should say they
are methods of temporarily suspending DML on a table?
Regards
HJR
DA Morgan
Fascinating. Where did you find this documented?
And yes I expect your answer to embarass me so go ahead.
DA Morgan
Reversible yes. But on the positive side by probably less than 1000
people on the planet.
Same holds true for one of my favorite tricks:
INSERT INTO product_user_profile
VALUES
('SQL*Plus', 'PUBLIC', 'SELECT', NULL, NULL, 'DISABLED', NULL, NULL);
Do this at your own risk.
And if you lose your job don't come whining to me. ;-)
DA Morgan
Or a read-only view and not give access to the original table at all.
Pete Finnigan
<ralf_o...@gmx.de> writes
>Is ist possible to make an oracle table read-only?
>(without moving it to a read-only tablespace).
>I haven't found any command like "alter table xxx read only" in the
>docs.
>
>Bye,
>Ralf
>
There has been some good ideas in this thread and I agree with Howard's
comments that we need to know the complete requirements for the OP's
read only table in terms of how long it needs to be read-only.
In the meantime to throw another idea into the fray to make the table
read-only is to use Row Level Security to prevent any DML being
successful. Again as with triggers and table locks its just as easy for
a user with the correct privileges to disable the policies again. The
problem also is that privileged connections are exempt from the policies
created and its an extra cost option. Whether its of use depends on your
requirements.
Mark C. Stock
I never noticed it before, either, but it's been sitting right there in
front of us for quite a while -- according to the 7.3 SQL Reference it was
introduced in 7.2
v7.3 manual:
Disables DML and DDL locks on a table to improve
performance in a parallel server environment. For
more information, see Oracle7 Parallel Server
Concepts & Administration.
A comment in the appendix under new features (which seems misplaced after
ALTER TABLESPACE, but appears to apply to the ALTER TABLE example) adds:
This feature is of most use in a parallel server environment where a
table lock can affect system performance.
v8, v9, and 10g manuals simply state:
Specify DISABLE TABLE LOCK to disable table locks, thereby
preventing DML operations on the table.
But preface that with :
Oracle Database permits DDL operations on a table only if the table can be
locked
during the operation. Such table locks are not required during DML
operations.
The v7 manual comment about no DML table locks appears to be true -- started
playing with it in my 8.1.7 sandbox (yes, I'll be installing 10g -- as soon
as my client in Florida pays up)...
With table locks disabled, update statements don't get SX locks, just TX
locks and apparently row locks. Very interesting.
++ mcs
Asif Merchant
affect lot of things and why would somebody wanna do this.
Anyways the question was can it be done or no. so yes it can be done.
Regds,
Asif.
"Howard J. Rogers" <h...@dizwell.com> wrote in message news:<41759464$0$20127$afc3...@news.optusnet.com.au>...
Brian Dick
> Is ist possible to make an oracle table read-only?
> (without moving it to a read-only tablespace).
> I haven't found any command like "alter table xxx read only" in the
> docs.
>
> Bye,
> Ralf
You could make a table read-only with Virtual Private Database (VPD). The
SELECT policy generator would return an empty string. The INSERT, UPDATE,
and DELETE policy generator would throw an exception. VPD can only be
circumvented by SYS and users granted EXEMPT ACCESS POLICY.
Michel Cadot
> "Michel Cadot" <micadot{at}altern{dot}org> wrote:
>
> >
Just one precision on what i thought about triggers.
I didn't say to use them for auditing purpose but to avoid the statement,
that is use raise_application_error to abort the statement.
But i agree with you: all that (and the read only view from Daniel) don't make a real
read-only table but something like a workaround.
Btw, "audit trigger" audits drop trigger (as well as create trigger, alter trigger,
alter table enable/disable all triggers).
--
Regards
Michel Cadot
MS
it's not emphasised in the "new features". I first noticed in 8i
under "alter table " syntax (in SQL reference), though per Mark Stock
it was available in 7.x. I found it very interesting but never had to
use it, till date.
I usually make it a point (when time permits!) to scan thru the entire
Sql reference manual every time a new version comes in , so as not to
miss "hidden" features.
-Madhu S
DA Morgan <damo...@x.washington.edu> wrote in message news:<1098253232.730656@yasure>...
Howard J. Rogers
[snip]
> Just one precision on what i thought about triggers.
> I didn't say to use them for auditing purpose
[neither did I, I hope]
> but to avoid the statement,
> that is use raise_application_error to abort the statement.
> But i agree with you: all that (and the read only view from Daniel) don't
> make a real read-only table but something like a workaround.
A very good definition.
> Btw, "audit trigger" audits drop trigger (as well as create trigger, alter
> trigger, alter table enable/disable all triggers).
True. I realised that. But it audits it, usually, into AUD$. Which is at the
mercy of the DBA's good intentions. And auditing can be turned off -which,
of course, is itself audited (because it would require an instance
bounce... hello Alert Log!). But again: knowing it was done is too late for
the high-security crowd.
But we agree. I like the "workaround" definition.
Regards
HJR
Howard J. Rogers
> Didnt see it until I finished posting. I understand that it will
> affect lot of things and why would somebody wanna do this.
>
> Anyways the question was can it be done or no. so yes it can be done.
See Michel Cadot's post this morning. And my reply. All the suggestions made
in this thread, including that one, are nothing but "workarounds": You can
make a table temporarily unavailable for DML. But that is not the same
thing as making it actually and really read only.
Whether someone is happy with the temporary workaround is one thing. But it
is slack terminology to say a workaround is the same as a solution. Or that
yes is the same as no. Or that you can make a table read-only.
But I smell another myth in the making, anyways.
HJR
Howard J. Rogers
Madhu, have you actually tried this?
For example, in my 9i Release 2 database on Windows, I get this:
SQL> select sum(sal) from scott.emp;
SUM(SAL)
----------
29025
SQL> alter table scott.emp disable table lock;
Table altered.
SQL> update scott.emp set sal=9;
14 rows updated.
SQL> select sum(sal) from scott.emp;
SUM(SAL)
----------
126
Which doesn't look very read-only at all! That the 'disable lock' command
has done something is certain, because if as Scott I try:
SQL> lock table emp in exclusive mode;
lock table emp in exclusive mode
*
ERROR at line 1:
ORA-00069: cannot acquire lock -- table locks disabled for EMP
But the records were definitely updated, and therefore, nice idea though it
was, I can't see that it does what you claimed it would do. Any
clarification, please? Am I doing something wrong, or what?
Regards
HJR
Howard J. Rogers
> MS wrote:
>
>> In addition to the trigger to disable DML, disabling table lock does
>> the trick.
Ah. I'll answer my own question then. I see I am to disable the table lock
*in addition* to writing the triggers etc. which are actually to do the
ordinary DML prevention. My apologies for misunderstanding. I had hoped
this one command would do the lot.
I'm less excited about it now, I'm afraid, than I was!
Regards
HJR
Dave
the table into and out-of a read-only TS I wouldn't think that there'd
be much of a downside to dedicating a tablespace to just this table,
then toggling the status as required.
Or has someone already suggested that?
Howard J. Rogers
I think it's been implicit. The point is to use read-only tablespace. How
the table gets there is immaterial, really. If it already happens to reside
in a tablespace which can be made read-only, so much the better. But I
think the assumption has been made in the thread so far that the table is
buried in a tablespace along with a lot of other tables -so you can't flip
the status of the existing tablespace, because it would affect all those
other tables. Hence the suggestion to move it to another tablespace whose
read-write status can indeed be modified at will.
There is nothing wrong with a table having a tablespace all to itself. That
is the why we have tablespaces, after all: to break a database up into
multiple, administratively-convenient, pieces. In this case, if a table
needs to flip in and out of read-only mode periodically, then being in its
own tablespace makes it capable of being managed in precisely that way
without impacting on anything else. It's a thoroughly sensible suggestion.
Regards
HJR
Holger Baer
> Is ist possible to make an oracle table read-only?
> (without moving it to a read-only tablespace).
> I haven't found any command like "alter table xxx read only" in the
> docs.
>
> Bye,
> Ralf
>
It's not what you asked for, but external tables are readonly, at least
as long as you use the ORACLE_LOADER access driver and not the (new in
10g) ORACLE_DATAPUMP one.
Cheers,
Holger
HansF
> The problem also is that privileged connections are exempt
> from the policies created and its an extra cost option.
It's an extra cost option? Has that changed recently - I thought CPD was
part of the base?
I know Label Security, which is built using VPD, is extra cost.
/Hans
Pete Finnigan
yes of course you are right, i was getting my brain muddled with label
Security. VPD is part of the enterprise edition.
Thanks for the correction,
kind regards
Pete
In article <PD7ed.42998$qU.610@clgrps13>, HansF <news...@telus.net>
writes
--