This
practice covers creating a new user in the database, assigning a temporary
password that must be changed at first login, and assigning the users default
data and temporary tablespace.
1.
Create a new user in the database that will have database authentication
and an assigned default data tablespace and temporary tablespace.
The user should have a limit of 25 megabytes storage on the default data
tablespace. The user will be prompted to change their password when they connect
to the database for the first time. Hint: The assigned default and temporary
tablespaces must already exist in the database.
|
SQL>
CREATE USER jsmith
2
IDENTIFIED BY 9jsmith1
3
DEFAULT TABLESPACE users 4
TEMPORARY
TABLESPACE temp 5
QUOTA
25M ON users 6
PASSWORD
EXPIRE; User
created. |
This
practice covers altering the attributes of a database user that has been
previously created, and dropping a database user that currently exists.
Instructions
1.
Alter an existing database user to have 10 megabytes of storage in the
USERS tablespace.
|
SQL>
ALTER USER jsmith
2
QUOTA 10M ON users; User
altered. |
2.
Examine the attributes for the attributes for the user that has been
altered. Display the amount of storage in bytes that the user has already
utilized on the tablespace and the maximum amount of storage in bytes the user
can utilize on the tablespace.
|
SQL>
SELECT USERNAME,
TABLESPACE_NAME, BYTES, MAX_BYTES
2 FROM
DBA_TS_QUOTAS
3 WHERE USERNAME =
‘JSMITH’; USERNAME
TABLESPACE_NAME
BYTES MAX_BYTES --------
---------------
----- ---------- JSMITH
USERS
0
10485760 |
3.
Alter an existing database user to be unable to create any new object
storage on the USERS tablespace
|
SQL>
ALTER USER jsmith
2 QUOTA
0 ON USERS; User
altered. |
4.
Examine the attributes for the user after the storage attribute for the
tablespace has been changed.
|
SQL>
SELECT USERNAME,
TABLESPACE_NAME, BYTES, MAX_BYTES
2 FROM
DBA_TS_QUOTAS
3 WHERE USERNAME =
‘JSMITH’; no
rows selected. |