Create an Oracle database user that meets the production environment requirements

If you start Oracle database-based application development, you may think about this problem. How to Create a database user that meets the requirements of the production environment and what permissions should be granted to the user.

It is necessary for project developers and managers to think about this issue.

For convenience, you can directly grant DBA permissions. This is no problem in the development environment. I have seen many developers do the same. However, when deployed to the production environment, it will be blocked by the production environment administrator. A database user of a common application should not have such a large permission.

How can we create users and grant permissions to meet the requirements of the production environment in the future?

From the perspective of database management, new users should have independent data table space, temporary tablespace, and limited permissions. User data can only be stored in their own tablespace, you can only access and operate on your own database objects.

The following describes how to create a user based on these requirements.

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 ";

Through the above operations, we have created a database user that basically meets the application development and production needs.

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;

For more information about Oracle, see Oracle topics page http://www.bkjia.com/topicnews.aspx? Tid = 12