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?
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
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
Save it into a SQL file (let's call it "create_user.sql"). Then you'd invoke it from SQL*Plus using
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.
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.