Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2010
    Posts
    5

    Unanswered: Script to create new users

    Hello,
    I am very new to this and I am practicing with some labs and have come across something I am having trouble with. What I am trying to do is:

    Write a script to use as a template for creating database users. The script has to prompt the administrator for a username and password, and then script creates the user according to the following:

    -use default tablespace USERS
    -use the temporary tablespace is TEMP
    -account needs to be initially locked
    -the account will be assigned to DEFAULT profile.
    -Tablespace quota for the user needs to be 10MB

    This is what I have so far?

    DEFINE username
    DEFINE password
    ACCEPT username char PROMPT Enter a username:
    ACCEPT password char PROMPT 'Enter password:

    CREATE USER &username IDENTIFIED BY &password
    DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP
    QUOTA 10M ON USERS
    PROFILE DEFAULT
    ACCOUNT LOCK;

    Will this work, and if so, how do I save this as a script that can be used over and over again to create new users? Thanks

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Save it into a SQL file (let's call it "create_user.sql"). Then you'd invoke it from SQL*Plus using
    Code:
    SQL> @create_user
    Will it work? As you are practicing, the best way to find it out is to, well, try it. It will either succeed (so you'll have to DROP those users, if you don't really need them), or it will fail (so you'll have to fix the script). In the latter case, consult the manual.

  3. #3
    Join Date
    Sep 2010
    Posts
    5

    Questiona about user default options

    Thanks, for the reply, it worked.

    I just have another question, when you create a new user, and it says to use all the default options for the new user, what is mean by "default" options. For example, I am doing this:

    CREATE USER TESTUSER IDENTIFIED BY Password
    DEFAULT TABLESPACE
    TEMPORARY TABLESPACE TEMP
    QUOTA UNLIMTED
    PROFILE
    PASSWORD EXPIRE
    ACCOUNT UNLOCK;

    Is using all the default options just mean leaving them blank or what? Thanks

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    I'd say that you're right - everything you omit uses its default values.

    Just a note, if I may: user you create with such a script can't do anything. It just exists. You'll have to grant certain privileges in order to let it connect to the database, create objects, etc. You'll do that with the GRANT statement.

    It is recommended to grant only privileges user really needs. There are certain predefined Oracle roles (such as CONNECT and RESOURCE) you'd perhaps want to research. They could give you some ideas of what your users might need. Do not grant those roles, but create your own ones and grant them to newly created users.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •