Restricting user to use particular tablespace - Page 2
DBAsupport.com Forums - Powered by vBulletin
Page 2 of 3 FirstFirst 123 LastLast
Results 11 to 20 of 23

Thread: Restricting user to use particular tablespace

  1. #11
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    Originally posted by msuresh
    Dont be smart.I asked you how to restrict other users if they were not using default tablespace in their script.
    another peep who need to RTFM
    Share on Google+

  2. #12
    Join Date
    May 2002
    Posts
    2,645
    Here is an analogy:

    create role bitch_slapped;
    grant bitch_slapped to msuresh;

    Now, even though you are bitch_slapped, can you do anything with that role? No. Just like create user doesn't mean, by itself, the user can actually create anything in his/her default tablespaces. bitch_slapped has no privileges associated with it, just as being bitch_slapped should be.
    Share on Google+

  3. #13
    Join Date
    Feb 2000
    Location
    Singapore
    Posts
    1,758
    Originally posted by adrianomp
    You can restrict default temporary tablespace (only on Oracle 9i), but not default tablespace.
    No, you can't restrict TEMP tablespace also.
    Code:
    SQL> select * from database_properties
      2  where property_name like '%TEMP%';
    
    PROPERTY_NAME                  PROPERTY_VALUE                 DESCRIPTION
    ------------------------------ ------------------------------ ------------------------------------------
    DEFAULT_TEMP_TABLESPACE        TEMP                           Name of default temporary tablespace
    
    SQL> create user u1 identified by u1 default tablespace users
      2  temporary tablespace temp1;
    
    User created.
    
    SQL> select username, default_tablespace, temporary_tablespace 
      2  from dba_users where username = 'U1';
    
    USERNAME                       DEFAULT_TABLESPACE             TEMPORARY_TABLESPACE
    ------------------------------ ------------------------------ ------------------------------
    U1                             USERS                          TEMP1
    
    SQL>
    Sanjay G.
    Oracle Certified Professional 8i, 9i.

    "The degree of normality in a database is inversely proportional to that of its DBA"
    Share on Google+

  4. #14
    Join Date
    Aug 2002
    Location
    Bangalore, India
    Posts
    405

    Re: Restricting user to use particular tablespace

    Originally posted by msuresh
    Hi,
    We gave a create user priv. for some application users and they they create the user without specifying default tablespace and their defaut tablespace is system now.I took care now.My question is

    During creation of a new user ,is there any way to force/restrict to make tablespace as default tablespace.

    Thanks
    What about writing a system level trigger for create/alter user statement? Do some homework on system Triggers. You need to have versions higher than 8 for this.
    -nagarjuna
    Share on Google+

  5. #15
    Join Date
    Oct 2001
    Location
    GA, USA
    Posts
    79
    I am completely agree with pando, stecal and others that if you are asking question and people are trying to help you then you donít have any right to show your over smartness to other people. If you are too smart then why donít you do RTFM? Or donít ask questions.
    Circumstances do not rise to meet our expectation. Embrace what you actually get. Open your eyes. See things for what they really are Thereby sparing yourself the pain of false attachements.
    Share on Google+

  6. #16
    Join Date
    Apr 2002
    Location
    Bangalore
    Posts
    47

    Thumbs up

    Hey when someone suggests u a script whether u ues it or not is up to u.Also i agree to the point that the Dba is the one who should be creating the database users.Dba is not there to clean up the mess created by the developers.He is responsible for the mess not to happen first
    Cheers
    Murali
    Share on Google+

  7. #17
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    Originally posted by dasari98
    Hey when someone suggests u a script whether u ues it or not is up to u.Also i agree to the point that the Dba is the one who should be creating the database users.Dba is not there to clean up the mess created by the developers.He is responsible for the mess not to happen first
    This is one of the most idiotic threads I have read in this forum. I agree with the above words. Only the DBA users should have the right to create users. Dot.
    Oracle Certified Master
    Oracle Certified Professional 6i,8i,9i,10g,11g
    email: ocp_9i@yahoo.com
    Share on Google+

  8. #18
    Join Date
    Feb 2001
    Posts
    295
    No, you can't restrict TEMP tablespace also.
    SANJAY,

    By defining a default temporary tablespace, users created without a temporary tablespace would be assigned to it, not SYSTEM anymore. That's what I meant.

    Anyway, I still do not understand why Oracle recommends not to use SYSTEM as default/temp tablespace and sets it as default, instead of looking for any temporary tablespace or simply rejecting the command.
    An ounce of action is worth a ton of theory.
    óFriedrich Engels
    Share on Google+

  9. #19
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Originally posted by adrianomp
    Anyway, I still do not understand why Oracle recommends not to use SYSTEM as default/temp tablespace and sets it as default, instead of looking for any temporary tablespace or simply rejecting the command.
    I guess this might be 1 of the reasons.

    * SYSTEM tablespace is the must creat TBS & is created during DB creation....and oracle simply doesnt want to throw error if any body wises to create user.....
    Hence System Tbs wud be as defualt...
    Now if u create a TBS, how will oracle know that it shud be defualt unless any command to change from System to XYX TBS ... and as of now no such command is avilable.
    i hope they provide 1.

    Abhay.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
    Share on Google+

  10. #20
    Join Date
    Aug 2002
    Location
    Bangalore, India
    Posts
    405
    Originally posted by adrianomp
    SANJAY,

    Anyway, I still do not understand why Oracle recommends not to use SYSTEM as default/temp tablespace and sets it as default, instead of looking for any temporary tablespace or simply rejecting the command.
    If Oracle starts automating such things, our database will end up like a SQL SERVER database.
    -nagarjuna
    Share on Google+

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