Restricting DDL changes for schema owner

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!

Quote:

---

Originally posted by davey23uk
you can make the tablespace read-only

---

Making tablespace read only doesn't prevent making DDL changes, it just make changes to data dictionary

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

Quote:

---

Originally posted by gopi
One way is to give no quota on any tablespace in the database. But is there any other way?

---

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).