Your mission is to develop a single SQL script that will perform all the following tasks:

1. Create three (3) users (schema) as follows: (15 points)

User	Password	Other Parameters
DBSEC_ADMIN	tec5363admin	Tablespace: USERS Temporary tablespace: TEMP
DBSEC_CLERK	tec5363clerk	Same as above
DBSEC_DEV	tec5363$dev	Same as above

2. Create three profiles as specified in the following table: (30 points)

Profile	Resources	Password
DBSEC_ADMIN_PROF	SESSIONS_PER_USER=5 CONNECT_TIME=8 hours IDLE_TIME=1 hour	PASSWORD_LIFE_TIME= 1 month PASSWORD_GRACE_TIME=7 days
DBSEC_DEV_PROF	CONNECT_TIME=2 hours IDLE_TIME=2 hours CPU_PER_CALL=1 minute	PASSWORD_LIFE_TIME= 1 month PASSWORD_GRACE_TIME=14 days
DBSEC_CLERK_PROF	SESSIONS_PER_USER=1 CPU_PER_CALL=5 seconds CONNECT_TIME=8 hours IDLE_TIME=30 minutes LOGICAL_READS_PER_CALL=10 0 Database Blocks	FAILED_LOGIN_ATTEMPTS=3 PASSWORD_LIFE_TIME= 1 month PASSWORD_LOCK_TIME=3 days PASSWORD_GRACE_TIME=14 days

3. Create roles with the following privileges: (30 points)

Role Name	Privileges
DBSEC_ADMIN_ROLE	SELECT and ALTER on all DBSEC tables
DBSEC_CLERK_ROLE	SELECT, INSERT, and UPDATE on all DBSEC tables
DBSEC_SUPERVISOR_ROLE	SELECT, INSERT, UPDATE and DELETE on all DBSEC tables
DBSEC_QUERY_ROLE	SELECT only on CUSTOMER table owned by DBSEC

4. Assign roles and profile to the users as in the following table: (15 points)

User Name	Role	Profile
DBSEC_ADMIN	DBSEC_ADMIN_ROLE	DBSEC_ADMIN_PROF
DBSEC_CLERK	DBSEC_CLERK_ROLE	DBSEC_CLERK_PROF
DBSEC_DEV	DBSEC_ADMIN_ROLE plus DBSEC_SUPERVISOR_ROLE	DBSEC_DEV_PROF

5. Connect as DBSEC_CLERK/tec5363clerk. (5 points)

6. Perform a simple query on SUPPLIER table owned by DBSEC. (5 points)

Tips:

1. DBSEC tables refer to the tables you created under previous project. DBSEC is the owner of those data.
2. When you need to connect to SYSTEM user, please use a syntax so that it will ask the user to type the password. A typical example will be: "connect system;"
3. Please discuss among fellow students in Communicty Solutions if you do not know for sure which account you should login in order to perform each of the above tasks. Your script will be tested under "scott/tiger" schema. In other words, I will login as SCOTT before testing your script.
4. For step 6 above, you may have to specify the table name as "dbsec.supplier."
5. Exact spelling conforming the specified names is required for the project and all other projects this semester.
   

One and only one SQL script file will be required and submitted through WebCT. Please note that 20 points will be taken off if the script file is not executable under SQL Plus, or is not in one file. Thus, please test it so that you can perform all above tasks in one file.

Convention for File Name: The file naming convention will be enforced throughout this semester. Five (5) points will be taken off if the file is not named according to the convention.

.

At the end of this week. Please see the course schedule for exact due date.