Brief introduction of basic measures of Oracle Database security management

Data security is the protection of data to prevent illegal use, to avoid the data intentionally or unintentionally lost, leaked or destroyed. Because of the large amount of data stored in the database system, which is shared by many users, security constraint is a very prominent problem.

The basic measures taken by Oracle database system in implementing database security management are as follows:

Prevents non-Oracle users from registering with the Oracle database and illegally accessing the database by verifying the user name and password.

Grant users certain permissions, such as Connect,resource, to limit the user's power to manipulate the database.

Grant user access to database entities (such as tables, table spaces, procedures, and so on) to prevent users from accessing unauthorized data.

The database entity Access Audit mechanism is provided so that the database administrator can monitor the data access and system resource usage in the database.

Use the view mechanism to restrict access to the rows and columns collection of the base table.

In practical application, many systems often use false user (that is, not database user) identity to manage, while the real user's identity and login password are hidden in the application system, or through a variety of compressed encryption and other processing configuration files. However, this often leaves a hidden danger, as long as from the analysis of the application, the final analysis of the system used by the database users and passwords, then its security has disappeared. On the other hand, the system code is written by the programmer, if the programmer has a destructive intent, this pattern is not a trace of security, because he through his own code without analysis of the user can easily access the database users and passwords.

And the use of real database users, there is the difficulty of assigning permissions, especially the number of users and the number of application tables are many, when it is necessary to use roles to manage the allocation of application rights. Of course, can not directly assign permissions or roles directly to the user, otherwise users can be different from the application system, and the use of sql*plus and other front-end tools into the system, do not have the application of the system to check the operation, the resulting results may not conform to the application logic.

In practice, we find that we can use the role function in another way to prevent the security "vulnerabilities" appearing above. In this way, the user registers with his or her own identity and password, but does not have any permissions to manipulate the database until it has been granted a role. The role that the authorized user uses is buried in the application, and only the application knows the name and password of the role, thereby activating the role and using the appropriate permissions. Outside the application system, the user can connect to Oracle, but does not activate the corresponding role, he is unable to do anything, and the developer does not know the user's identity and password, he has no way to log in to Oracle, even if he can calculate the identity and password of the role.

The following example gives a specific implementation process:

We assume that the user Xiayan has the ability to query and update the Payroll account.paytable (account is the owner of Table paytable) at work, and that we do not grant Xiayan directly, but instead construct a role ( such as the attendance Clerk checkerrole), this role is suitable for Xiayan, and then the role granted Xiayan, but the role in the activation requires a password, the password is not Xiayan public. Each user needs a default role, which is the default role for users when they connect to Oracle. This role only connect permission, we fake for defaultrole.

The specific operation SQL is given below.

(1) Set various roles and their rights

CREATE role Checkerrole IDENTIFIEDBYxm361001;

CREATE role Defaultrole Identifiedbydefaultrole;

Grantselect,updateonaccount.paytabletocheckerrole;

Grantconnecttodefaultrole;

(2) Create user

Createuserxiayanidentifiedbyxiayan;

(3) Authorization

Grantcheckerroletoxiayan; Grantdefaultroletoxiayan;

(4) Set the user default role

Alteruserxiayandefaultroledefaultrole;

(5) Registration process

Connectxiayan/xiayan@oracle

The user has only the permissions for the default role at this time.

(6) Activating roles

SETROLEcheckerroleIDENTIFIEDBYxm361001;

After the operation succeeds, Xiayan has the Checkerrole permission.

The roles and passwords here are fixed, and it is more convenient and safe for the application managers to set up their own applications in the application system.