Oh boy. All privileges
and DBA. Really?
They give cs4 the ability to do pretty much everything. That's bad, mmmkay? Grant your database users as few privileges as you can.
But even with a stripped down set, if cs4 owns the table, they can still change the data. Even if you make it read only. It's their table. So they can make it read-write again:
grant create session, create table, unlimited tablespace
to cs4 identified by cs4;
create table cs4.t (
x int
);
insert into cs4.t values (1);
commit;
alter table cs4.t read only;
conn cs4/cs4
insert into t values (2);
SQL Error: ORA-12081: update operation not allowed on table "CS4"."T"
alter table t read write;
insert into t values (3);
select * from t;
X
1
3
If you want to ensure cs4 can only read a table, not change it, you should:
- Create the table in another schema
- Grant only cs4 create session and select on the required table:
drop user cs4 cascade;
grant unlimited tablespace to u identified by u;
create table u.t (
x int
);
insert into u.t values (1);
commit;
grant create session to cs4 identified by cs4;
grant select on u.t to cs4;
conn cs4/cs4
insert into u.t values (2);
SQL Error: ORA-01031: insufficient privileges
alter table u.t read write;
SQL Error: ORA-01031: insufficient privileges
insert into u.t values (3);
SQL Error: ORA-01031: insufficient privileges
delete u.t;
SQL Error: ORA-01031: insufficient privileges
select * from u.t;
X
1