How to stop users creating tables in SYSTEM TS
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: How to stop users creating tables in SYSTEM TS

  1. #1
    Join Date
    Sep 2001
    Location
    Düsseldorf, Germany.
    Posts
    588
    Hi

    User has been defined a default tablespace and also temp tablespace.

    How do I stop user creating tables in SYSTEM tablespace?

    Thanks

    Sameer

  2. #2
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    assign them a dfeault tablespace that is not system...???

  3. #3
    Join Date
    Sep 2001
    Location
    Düsseldorf, Germany.
    Posts
    588
    Yes. The default tablespace assigned to user is USERS and not SYSTEM, temp tablespace is TEMP

    But if user executes

    create table test (id number) tablespace system it gets created in system tablespace

    The way we allocate quota on tablespace, Is there is any to stop them by allocating 0 quota on system tablespace.

    I tried

    alter user xxxx quota 0 on system

    it works.. but still user able to create table in SYSTEM tablespace...

    How do I stop this??


  4. #4
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    that is because you gave user RESOURCE role

  5. #5
    Join Date
    Sep 2001
    Location
    Düsseldorf, Germany.
    Posts
    588
    Thanks.. I think u eat/breath Oracle :-)

    Sameer


  6. #6
    Join Date
    May 2002
    Posts
    2,645
    You can also assign a quota of zero to ensure a user can not create any objects in a particular tablespace, including system.

    alter user username quota 0 on system;

  7. #7
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    you cant do that if user has resource role since it has this unlimited tablespace system privilege...

  8. #8
    Join Date
    Jan 2001
    Posts
    3,131

    Wink

    Generally what I do for a situation such as this is.....

    Dress up in an evil clown outfit (don't forget the oversized shoes) track down the user and open fire with a paintball gun. This has proven to be a highly effective means of deterrent for both developers and users alike. Of course in some countries this is labeled as “unacceptable” or “politically incorrect” but fortunately for us we can still implement this type of educational alternative.

    MH
    I remember when this place was cool.

  9. #9
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    I prefer:
    DROP USER xyz CASCADE;
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  10. #10
    Join Date
    Jan 2001
    Posts
    3,131

    Thumbs up

    Originally posted by marist89
    I prefer:
    DROP USER xyz CASCADE;
    HARDCORE!!
    I remember when this place was cool.

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