Assigning Default Permanent and Temporary Tablespaces- Users and Basic Security

Ensuring that users have a correct default permanent tablespace and temporary tablespace helps prevent issues of inadvertently filling up the SYSTEM or SYSAUX tablespaces, which could cause the database to become unavailable as well as create performance problems. The USERS tablespace is normally the default tablespace.

When maintaining a database, you should verify the default permanent and temporary tablespace settings to make certain they meet your database standards. You can look at user information by selecting from the DBA_USERS view:

Here is a small sample of the output:

 
All your users should be assigned a temporary tablespace that has been created as type temporary. Usually, this tablespace is named TEMP, and there could be temporary tablespaces for a PDB or just in the CDB.

If you find any users with inappropriate default tablespace settings or you want to change to use a different temporary tablespace, you can modify them with the ALTER USER statement:

SYSTEM should never be assigned as a temporary tablespace, and SYSTEM and SYSAUX should not be assigned to users.

Modifying Users

Sometimes you need to modify existing users for the following types of reasons:

•     Change a user’s password

•     Lock or unlock a user

•     Change the default permanent or temporary tablespace

•     Change a profile or role

•     Change system or object privileges

•     Modify quotas on tablespaces

Use the ALTER USER statement to modify users. Listed next are several SQL statements that modify a user. This example changes a user’s password, using the IDENTIFIED BY clause:

This example locks a user account:
This example alters the user’s quota on the USERS tablespace:

Note since ALTER USER is a highly privileged command and there are many reasons for using it, it might now fall into the hands of a security team to execute. There are other commands and procedures that can be written around this, and then the permissions are given to those to execute.also, a database vault limits the ability to alter users and allows the security teams to perform these actions.