Restricting user to use particular tablespace
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 3 123 LastLast
Results 1 to 10 of 23

Thread: Restricting user to use particular tablespace

Hybrid View

  1. #1
    Join Date
    Jun 2000
    Location
    chennai,tamil nadu,india
    Posts
    159

    Restricting user to use particular tablespace

    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
    Share on Google+

  2. #2
    Join Date
    Nov 2002
    Location
    Dublin, Ireland
    Posts
    133
    create user xyz identified by xyz
    default tablespace TBS_NAME
    temporary tablespace TEMP ;

    if you have already created user then :
    alter user xyz default tablespace TBS_NAME ;

    HTH
    Giani
    Share on Google+

  3. #3
    Join Date
    Jun 2000
    Location
    chennai,tamil nadu,india
    Posts
    159
    Dont be smart.I asked you how to restrict other users if they were not using default tablespace in their script.
    Share on Google+

  4. #4
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    If that's not the answer, I guess we just don't understand the question.
    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."
    Share on Google+

  5. #5
    Join Date
    Jun 2001
    Location
    Dublin, Ireland
    Posts
    66
    I dont think Giani was being smart. If you want users to create other users then you should insist that they follow your rules.
    Perhaps you could supply them with a creation script that they MUST use when creating users.
    Something like


    accept user_name prompt 'Please enter the Oracle user name: '
    accept password hide prompt 'Please enter the users password: '
    create user &&user_name identified by &&password;
    grant create session to &&user_name;
    alter user &&user_name quota 0 on user_tablespace;
    alter user &&user_name default tablespace user_tablespace;
    alter user &&user_name temporary tablespace temp;
    exit

    HTH
    Share on Google+

  6. #6
    Join Date
    Feb 2001
    Posts
    295
    I don't think it's possible. You can restrict default temporary tablespace (only on Oracle 9i), but not default tablespace. You can use a trigger as a workaround (AFTER CREATE ON DATABASE) and change the default tablespace just after the user creation.

    However, that workaround seems disgusting (at least for me). What do you think about changing the policy? Is it really necessary that application users have the right to create users on database?
    Last edited by adrianomp; 03-20-2003 at 11:26 AM.
    An ounce of action is worth a ton of theory.
    —Friedrich Engels
    Share on Google+

  7. #7
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    Originally posted by adrianomp
    Is it really necessary that application users have the right to create users on database?
    In the best security model I know ("'tis my own invention" said the White Knight), the IT dept should not be creating users - it's the job of HR and/or line-management. OF COURSE they shouldn't be using a "create user" statement! {descends from soap-box}
    "The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman
    Share on Google+

  8. #8
    Join Date
    Feb 2001
    Posts
    295
    In the best security model I know ("'tis my own invention" said the White Knight), the IT dept should not be creating users - it's the job of HR and/or line-management. OF COURSE they shouldn't be using a "create user" statement! {descends from soap-box}
    In these terms, I completely agree with you. However, take care of the database health is a duty of the DBA. If it's possible to embed the correct operation on an application, fine. Otherwise it's better to restrict access than let others make a mess on the database.
    An ounce of action is worth a ton of theory.
    —Friedrich Engels
    Share on Google+

  9. #9
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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.
    I think you should be more smart, or at least read some documents to understand basics before attacing others that are trying to help you.
    Originally posted by msuresh
    create user xyz identified by xyz
    default tablespace TBS_NAME
    temporary tablespace TEMP ;
    There's absolutely nothing smart in that, it's just how users should be created. And if you had been smart enough you would have realised that the above command creates a user that can't create any object in any tablespace whatsoever - not in TBS_NAME (the default tablespace for that user), nor in any other tablespace!

    It's not the assigned defaut tablespace that gives user the ability to create objects - it's what privileges and tablespace quotas you assign to that user.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?
    Share on Google+

  10. #10
    Join Date
    Aug 2001
    Posts
    267
    jmodic is right . One guy tried to help you with his post. if you comment like that _______________ Fill in the blanks
    Raghu
    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