-
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?
Don't blame me, I'm from Red Sox Nation.
-
you can make the tablespace read-only
-
If the tablespace is read-only then DML cannot occur. I want to prevent only DDL!
Don't blame me, I'm from Red Sox Nation.
-
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
-
Re: Restricting DDL changes for schema owner
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).
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|