Introduction to basic measures for Oracle Database Security Management

Data security refers to protecting data to prevent illegal use and avoid intentional or unintentional loss, leakage or destruction of data. Because the database system stores a large amount of data in a centralized manner and the data is shared by multiple users, security constraints are a very prominent issue.

The basic measures taken by the Oracle database system to implement database security management include:

◆ Verify the user name and password to prevent non-Oracle users from registering with the Oracle database and illegally accessing the database.
◆ Grant the user certain permissions, such as connect and resource, to restrict the user's permission to manipulate the database.
◆ Grant users the access and execution permissions to database entities (such as tables, tablespaces, and processes) to prevent users from accessing unauthorized data.
◆ Provides a database Entity Access audit mechanism so that the database administrator can monitor the data access and system resource usage in the database.
◆ The view mechanism is used to restrict access to the row and column sets of the base table.
In practical applications, many systems often manage fake user identities (that is, non-database users), while real user identities and logon passwords are hidden in application systems, or the configuration files processed by various compression and encryption. However, this often leaves a hidden risk. As long as the application is analyzed, the database users and passwords used by the system will be analyzed, and the security will disappear. On the other hand, the system code is written by the programmer. If the programmer has the intention to destroy it, there is no security in this mode, it is easy for him to obtain the database user and password for Logon without analyzing the Code he has mastered.

When real database users are used, permission allocation is difficult. When the number of users and the number of application tables are large, roles must be used to manage application permission allocation. Of course, you cannot directly assign permissions or roles to users. Otherwise, you can use SQL * PLUS and other front-end tools to access the system, some operations that do not pass the application system check may not conform to the application logic.

In practice, we found that the role function can be used in another way to prevent the preceding Security "Vulnerability ". In this way, the user uses his/her own identity and password for registration, but he/she does not have any permissions to manipulate the database before being authorized. The role authorized to the user is buried in the application. Only the application can know the role name and password, so as to activate the role and grant the user the corresponding permissions. In addition to the application system, a user can connect to Oracle, but does not activate the corresponding role. He cannot do anything, and developers do not know the user's identity and password, he cannot log on to Oracle, even if he can calculate the role identity and password.

The following example shows the specific implementation process:

We assume that xiayan is able to account the payroll at work. paytable (account is the owner of the table paytable) has the query and update permissions. Instead of directly granting these permissions to xiayan, we construct a role (for example, checkerrole ), this role is suitable for xiayan and then assigned to xiayan. However, a password is required when the role is activated. The password is not disclosed to xiayan. Each user needs a default role, which is the default role when the user connects to Oracle. This role only has the connect permission. We assume it is defaultrole.

The specific operation SQL is given below.

(1) set various roles and their Permissions

CREATE ROLE checkerrole IDENTIFIEDBYxm361001; CREATE ROLE defaultrole IDENTIFIEDBYdefaultrole; GRANTSELECT,UPDATEONaccount.paytableTOcheckerrole; GRANTCONNECTTOdefaultrole;

(2) create a user

CREATEUSERxiayanIDENTIFIEDBYxiayan;

(3) Authorization

GRANTcheckerroleTOxiayan; GRANTdefaultroleTOxiayan;

(4) set the user's default role

ALTERUSERxiayanDEFAULTROLEdefaultrole;

(5) registration process

CONNECTxiayan/xiayan@Oracle

At this time, the user only has the permissions of the default role.

(6) activate a role

SETROLEcheckerroleIDENTIFIEDBYxm361001;

After the operation is successful, xiayan has the checkerrole permission.

The roles and passwords here are fixed. In the application system, you can set them by the application administrator, which is more convenient and secure.