Oracle Database User Creation specifications

In Oracle databases, each user generally has his/her own data storage space such as data table space, temporary tablespace, and index tablespace, and you cannot write it to other tablespaces at will. For example, we often see tables built on users tablespaces, and others built on system tablespaces. If you don't believe it, check your database. The permissions of new users also need to be strictly controlled. Generally, connect and resource roles are granted. In addition, create view and other permissions can be added as needed. In 10 Gb, the resource role has a system permission of unlimited tablespace, which must be removed manually.

If you need to restrict your resources, such as password expiration cycle and the maximum number of user session connections, you can use profile.

This is the specification created by database users. The specific creation process is listed step by step.

First, create the table space and temporary space for storing data required by the user.

Create tablespace TBS_MIKI datafile '/u01/oradata/servdb/datafile/tbs_miki01.dbf' size 10 M;
Create temporary tablespace TBS_MIKI_TEMP datafile '/u01/oradata/servdb/datafile/tbs_miki_temp01.dbf' size 10 M;

Note the tablespace naming method.

Create a new user, such as usr_miki.

Create user USR_MIKI identified by USR_MIKI
Default tablespace TBS_MIKI
Temporary tablespace TBS_MIKI_TEMP
PROFILE DEFAULT
Quota unlimited on TBS_MIKI;

Finally, assign the role connect, resource

GRANT "CONNECT" TO USR_MIKI with admin option;
GRANT "RESOURCE" TO USR_MIKI with admin option;
Alter user USR_MIKI default role "CONNECT", "RESOURCE ";

Remove unlimited tablespace because after 10g2, the system permission is automatically assigned to the resource role.

Revoke unlimited tablespace from usr_miki;

Alter user USR_MIKI quota unlimited on TBS_MIKI;

Some permissions are required, such as creating views, creating materialized views, and creating synonyms. After all, the resource role has fewer permissions.

Through these steps, we have created a database user that basically complies with the development and production specifications.

This user has the connect and resource roles. All data can only be written to the tbs_miki tablespace. If you need to add a new tablespace to save the index object separately, you can grant the table space quota to the user after it is created.

Create tablespace TBS_MIKI_IND datafile '/u01/oradata/servdb/datafile/tbs_miki_ind01.dbf' size 10 M;
Alter user USR_MIKI quota unlimited on TBS_MIKI_IND;