In-depth Oracle user management and specifications

Each Oracle database has problems such as the increasing amount of information and the increasing number of Oracle users. How to effectively manage the data of various departments of the company should first beginOracle user managementStart. TheOracle user managementTo effectively organize and maintain Oralce users based on a standardized system.

I. CurrentlyOracle user managementProblems

1. Excessive Permissions
Common users who program and browse Oracle databases often have DBA (database administrator privilege) and can modify or delete the database system.

2. Poor security
Many Oracle users store data in the system tablespace by default. This not only affects the normal operation of the system, but also affects data information of different users, which is transparent and has poor confidentiality. With the continuous addition of data, the entire database system may crash.

3. Regular passwords
The bad habits of usernames and passwords formed at the early stage of Oracle debugging are retained until now. The passwords of SYSTEM users SYS and SYSTEM are also well known.

Ii. Standardized Management of Oracle users

Summary aboveOracle user managementAccording to the database usage, the following suggestions are provided for standardized management:

1. Oracle DBA (Database Administrator) Specifications

(1) the password of Oracle users in the SUN Solaris operating system should be kept strictly confidential and should never be set to Oracle. A dedicated database administrator should be designated to modify the password on a regular basis.
(2) the password of SYS and SYSTEM administrator users initialized by Oracle should be changed from the original MANAGER to other strings that are hard to remember.
(3) the Management port of Oracle web server can be used by DBAs to browse databases. Therefore, the ADMIN password of the Administrator should be kept confidential and should not be set to MANAGER. A dedicated database administrator should be designated to modify the password on a regular basis.
(4) It is best to use a window interface to manage Oracle DBAs on the SUN or Linux server console. The premise is that the Oracle user starts the server, and then enters SVRMGRM under the window command line, that is, the OracleSERVER MANAGER menu management is started. After logging on as SYSDBA, the database system can be maintained.

2. SQL * PLUS programming user specifications

(1) specifications of the Storage Structure
Considering that SQL * PLUS programming can meet various application requirements of various industries, companies, and departments, our SQL * PLUS programming users should also standardize in this direction: different types of applications must have different users. Different types of applications must have different storage locations, including the creation and planning of physical files, default tablespace, and temporary tablespace: when preparing to write a large scale (from Oracle data volume and
Users) in applications, you should first create a logical storage location-tablespace, and define the storage path of physical files and the size of the occupied hard disk.

① The default storage path of physical files is/Oracle_home/dbs. Run the UNIX Command df-k on the command line to view the usage of the hard disk Resource Partition. If Oracle_home has a usage rate of more than 90 ‰ and one or more idle hard disk resource partitions can be used, we 'd better change the default storage path of physical files to the idle hard disk Resource Partition path. In this path, we can plan the storage of resource physical files as follows:
Xxx tablespace
Xxx industry/xxx company/xxx department/xxx service. dbf

DEMO tablespace
Default_datafile_home1/col/elec/sys4/demo1.dbf
Default_datafile_home1/col/elec/sys4/demo2.dbf
Four parts of the company's system are used to simulate the physical files of the system
HUMAN tablespace
Default_datafile_home1/col/elec/human. dbf
Company Personnel Department personnel management system physical files
BOOK tablespace
Default_datafile_home1/col/elec/book. dbf
Company Data room library management system physical files
QUESTION tablespace
Default_datafile_home1/col/elec/client/question. dbf
Company customer service department
PC tablespace
Default_datafile_home1/col/chaoxun/client/pc. dbf
Company PC after-sales service system physical file
...... Tablespace
Default_datafile_home2 /.................................
And so on.
Ult_datafile_home1 indicates Oracle_home/dbs;
Default_datafile_home2 indicates the idle hard disk Resource Partition path.

② The size of physical files depends on the amount of data in the application system, data objects, and packages. Generally, in a small system used for Simulation Demonstration, the initial physical file of the tablespace can meet the requirement at 2 MB. If the information is full, you can add physical files, expand the tablespace (the size of each expansion can also be set to 2 MB). Generally, the actual application system can increase the initial physical file size of the tablespace, but do not allocate too much space at a time (because it is not easy to recycle space, but it is easy to expand space). This also needs to be analyzed based on the specific situation: applications with large amounts of information and long-time storage can have up to several hundred MB or even up to several GB of space as conditions permit. Applications with small amounts of information and frequent refreshing in the short term, the tablespace can be controlled below 2 MB.

Example:

● Create an APP tablespace (Window Menu mode ):

Oracle_home/dbs/app. dbf 2 M physical files of an application

The usage of the/wwwdg partition where Oracle_home is located is 10‰, so physical files can be placed in the default path Oracle_home/dbs.

● Create the tablespace of an app (command line)

Oracle_home/dbs/app. dbf 2 M physical files of an application

% Svrmgrl
SVRMGR> connect internal;
SVRMGR> create tablespace app datafile 'app. dbf' size 2 M;
Note: The default physical file path is Oracle_HOME/DBS.
That is, wwwdg/Oracle/app/Oracle/product/7.3.2/dbs

③ The name of a tablespace should be an English character or character abbreviation similar to that used by the system. One or more physical file names corresponding to the tablespace should also be correlated. Different users have different default tablespaces, so the stored information cannot be accessed from each other. This is much more secure than storing all user information in the system tablespace. If you create a user using the Oracle web server to manage the port, the default and temporary tablespace must be the system tablespace, And the DBA must remember to change the user's default tablespace. Temporary tablespace stores temporary data segments, processes sorting, merging, and other intermediate operations. You can place them in a specially created tablespace based on actual application requirements. If the system tablespace is large, you can also place them in the system tablespace. It is best to store the data index created by the user separately from the data file in different tablespaces to reduce data contention and improve response speed.

Example: ● change the default tablespace Of The dcd user (Window Menu mode ):

● Change the default tablespace Of The dcd user (in command line mode)
% Svrmgrl
SVRMGR> connect internal;
SVRMGR> alter user dcd identified by dcdpwd default tablespace app;

(2) Permission specifications
It is sufficient for Oracle Database programmers to have the CONNECT and RESOURCE permissions to complete their work. To develop executable applications on the INTERNET, we recommend that you use the Oracle web server Management port to create SQL * PLUS programming for new users. Select PL/SQL AGENT in Oracle WEB SERVER ADMINISTRATION.

Example: ● create an SQL * PLUS programming new user new (Window Menu Mode)

Select create new dcd.

After you select the submit new service button, the screen prompts create dcd success! This process not only creates new Oracle users, but also installs the PL/SQL Toolkit of WEB SERVER and determines its valid INTERNET browsing port.

Select CONFIGURE 80

Select Directory Mappings
Configure INTERNET resources on the port

Modify WEB Request plug-in 80

Add new user WEB request application and Path

After configuring the corresponding INTERNET resources on the port and adding the WEB Request plug-in for new users, the DBA should remember to change the user's default tablespace from the system tablespace to the tablespace created for the relevant application. In this way, the INTERNET-oriented SQL * PLUS programming users are well planned.

(3) password and user name specifications
A considerable number of Oracle user names and passwords are consistent, which is a very insecure factor. We recommend that the Oracle user name and password must not be the same. The password should be five to six characters or more. Different users should not use the same password. The user name can be defined based on the English name of the actual application, but the user name defined based on the programmer's name is actually not standard, you can gradually improve the storage structure specification in future work.

3. Special Oracle user management specifications
When using Oracle databases, users who have special requirements must have the right to query, add, delete, and modify a table. DBA should create such a user, first determine the user name and password, then specify the default tablespace (including a TABLE) and temporary tablespace of the application, and finally authorize the TABLE owner: grant the connect role THE object-level permissions of SELECT, INSERT, DELETE, and update on the table, which can be freely selected based ON actual needs.

Example: ● grant object-level permissions to new users (using the command line method ):
Assume that the new user NEW2 needs to query, delete, and modify the table EMP of the DCD user.

% Svrmgrl
SVRMGR> connect internal; Log On As a system administrator
SVRMGR> create user new2 identified by new2345 default tablespace app;
SVRMGR> connect dcd/dcdpwd; Log On As A dcd user
SVRMGR> grant connect to new2;
SVRMGR> grant select on emp to new2;
SVRMGR> grant delete on emp to new2;
SVRMGR> grant update on emp to new2;

1. Step 4: Change the Oracle user name
2. Oracle users can learn about table space in two steps.
3. Oracle username re-indexing method
4. Explain how to query user tablespaces in Oracle
5. An analysis of the management method of the Oracle user permission table