Restricting DDL changes for schema owner
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Restricting DDL changes for schema owner

  1. #1
    Join Date
    Dec 2001
    Location
    Keene, NH
    Posts
    510

    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.

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    you can make the tablespace read-only

  3. #3
    Join Date
    Dec 2001
    Location
    Keene, NH
    Posts
    510
    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.

  4. #4
    Join Date
    Sep 2001
    Location
    Düsseldorf, Germany.
    Posts
    588
    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

  5. #5
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439

    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
  •  


Click Here to Expand Forum to Full Width