DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: creating a user

  1. #1
    Join Date
    Jan 2003
    Posts
    33

    creating a user

    Hello,

    I am trying to create a user (using the user "sys" for it), who should have only rights to see/create/modify the tables were created by himself.

    He should not even see other things in the database.

    Advices?

    Thanks!
    Regards

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    create user x identified by y;
    grant create session to x;
    grant create table to y;

    They wont be able to see any other schemas UNLESS they have been granted to public

  3. #3
    Join Date
    Jan 2003
    Posts
    33
    Thanks davey!

    what is "y" in this case?
    Last edited by ter055; 10-01-2004 at 06:32 AM.

  4. #4
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    the password for the user.

    Should of been x in the grant create table statement - sorry

  5. #5
    Join Date
    Jan 2003
    Posts
    33
    now I have the problem (after following your indications), that the user x cannot create a table:

    SQL> create table mytable(mt_num number);
    create table mytable(mt_num number)
    *

    ORA-01950: no privileges on tablespace 'SYSTEM'

  6. #6
    Join Date
    Feb 2003
    Location
    Leeds, UK
    Posts
    367
    The problem here is that you did not specifiy the default tablespace for the user when you created it, thus it defaults to SYSTEM and the user has a 0 quota on that tablespace. You should NEVER create user segments in the SYSTEM tablespace, so it's good this didn't work. Choose the tablespace you want the user to create their tables etc. in (let's assume you choose the USERS tablespace):


    Code:
    ALTER USER WHOEVER DEFAULT TABLESPACE USERS QUOTA UNLIMITED ON USERS;
    Now the user has an unlimited quota on their tablespace, and consume as much space as there is available. You could of course limit their quota by specifying bytes, kilobytes or whatever

  7. #7
    Join Date
    Jan 2003
    Posts
    33

    Thumbs up

    .

    Thank you both for helping me on this!
    Last edited by ter055; 10-01-2004 at 08:57 AM.

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