Can I prevent the schema owner from doing either all DDL changes OR specific DDL changes (ie. alter table)?
One way is to give no quota on any tablespace in the database. But is there any other way?
Printable View
Can I prevent the schema owner from doing either all DDL changes OR specific DDL changes (ie. alter table)?
One way is to give no quota on any tablespace in the database. But is there any other way?
you can make the tablespace read-only
If the tablespace is read-only then DML cannot occur. I want to prevent only DDL!
Making tablespace read only doesn't prevent making DDL changes, it just make changes to data dictionaryQuote:
Originally posted by davey23uk
you can make the tablespace read-only
Code:
SQL> alter tablespace users read only;
SQL> select tablespace_name, status from dba_tablespaces where tablespace_name = 'USERS';
TABLESPACE_NAME STATUS
------------------------------ ---------
USERS READ ONLY
SQL> select table_name, tablespace_name from tabs where table_name = 'TEST1';
TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
TEST1 USERS
SQL> alter table test1 add (address varchar2(100));
Table altered.
SQL> desc test1
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NUMBER
NAME VARCHAR2(10)
ADDRESS VARCHAR2(100)
SQL> select tablespace_name, status from dba_tablespaces where tablespace_name = 'USERS';
TABLESPACE_NAME STATUS
------------------------------ ---------
USERS READ ONLY
Sameer
No way realy. The owner allways has all the rights to his own objects. BTW, seting quota on any tablespace to 0 is not the way - the owner can still happyly execute any DDL to any of his segments in that tablespace (with the exception of ALTER TABLE MOVE or ALTER INDEX REBUILD and alike).Quote:
Originally posted by gopi
One way is to give no quota on any tablespace in the database. But is there any other way?