You were just hired as a new junior Oracle database administrator (DBA) to assist a team of five senior database administrators. In your first week, you were handed the task of writing a script for creating database users. The script will perform the following tasks and conform to the following specifications:
1. Create the following two (2) users with their respective passwords. Please use the exact spelling. (15 points)
| User Name |
Password |
| DBSEC |
Tec5363%1 |
| VPD_CLERK1 |
Jessie#22 |
The following parameters apply to the above two (2) users.
2. The default table space is USERS. (10 points)
3. The temporary table space is TEMP. (10 points)
4. The user has to change his/her password the first time when he/she log in the database. (10 points)
5. The user account will be assigned to the DEFAULT profile. (10 points)
6. The USERS table space has a 30MB quota. (10 points)
7. At the end of the creation, list the user information that shows a successful creation of the above user account. (15 points)
Tips:
a. You need system privilege to create new user. An easy way is to log in as SYSTEM before running the script. Or, you may use "CONNECT system" or "CONNECT system/&password." Upon running your script, you will enter the password. The password is what you entered in your installation.
My grading assumption: I will log in as SCOTT/tiger, before runing your script. In other words, it is required that you have a CONNECT statement in your script.
b. I generally like to write the script in Notepad or WordPad. In order to test it again and again, it is a good idea to add one statement of dropping the user at the beginning of the script. The drop statement will be like "DROP USER dbsec CASCADE." (See page 79)
c. A database dictionary view "dba_users" can be used to find user information. There are, of course, other ways to do it, which are acceptable as well.
d. Please note that the password specification IS case sensitive even though Oracle SQL statement in general is not case sensitive. Please test it before submission.
e. Exact spelling is critical for any successful collaboration among development team members. Therefore, exact spelling conforming the specification is required for the project and all other projects this semester.
f. When grading, I will test your script first to see if it works or not. Thus, please make sure the script runs before submission. Please note that 20 points will be taken off if the script file is not executable under SQL Plus.
