Imagine that you have successfully completed a DBA course. Now you are hired as
ID: 3739458 • Letter: I
Question
Imagine that you have successfully completed a DBA course. Now you are hired as a database security expert to train the database administration team. At the first training session, you ask the trainees to perform the following tasks using SQL*Plus. Write the query and show the output for the following steps (as sys or system): a. Create a user named CH0306 with the same password. Use all default values for the user options. b. Modify the user options to change the default tablespace to TOOLS. (Note: You need to create a TOOLS tablespace before you answer this question.) c. Lock the CH0306 account. d. Change the CH0306 user password to DBSEC. e. Query the CH0306 account using the data dictionary view DBA_USERS. f. Query the CH0306 user’s quotas using the data dictionary view DBA_TS_QUOTAS. g. Provide unlimited storage quotas to CH0306 on TOOLS and USERS.
Explanation / Answer
a. create user <user-name> identified by <password>;
create user CH0306 identified by CH0306
Create Tablespace TOOLS DATAFILE 'data_file.dbf' size 10M; // WE HAVE TO CREATE A TABLESPACE NAMED TOOLS FOR ALLOCATING TO THE USER CH0306
b. For changing the default table space to TOOLS
alter user CH0306 identified by CH0306 default TABLESPACE TOOLS ;
c. To lock the user account
ALTER USER CH0306 ACCOUNT LOCK;
d. To change the user password,
alter user CH0306 identified by DBSEC;
e. select * from dba_users where username='CH0306';
It will display all information related to the user CH0306 such as user id, password, account status, expiry date,default tablespace etc.
f. select * from DBA_TS_QUOTAS WHERE USERNAME='ch0306';
g. alter user CH0306 QUOTA UNLIMITED ON TOOLS;