Overview of Oracle Database Security Measures

Oracle's security measures mainly include three aspects: user identification and authentication; authorization and inspection mechanisms; and audit technology (whether to use audit technology can be flexibly selected by users ); in addition, Oracle allows users to flexibly define their own security measures through triggers.

I. user identification and Identification
In Oracle, the outermost security measure is to allow users to identify their own names and then the system will verify them. Oracle allows the user to identify three times repeatedly. If the three times fail, the system automatically exits.

Ii. Authorization and Check Mechanism
Oracle has two types of permissions: system permissions and database object permissions. the DBA is responsible for granting and revoking system permissions, each User grants and revokes the permissions of the database objects they have created.

Oracle allows repeated authorization to grant a permission to the same user multiple times without errors. Oracle also allows invalid revocation, that is, the user does not have a certain permission, but the operation to revoke this permission is still successful.

1. System Permissions
Oracle provides more than 80 system permissions, such as creating sessions, creating tables, creating views, and creating users. When creating a user, DBA must grant some permissions to the user.

Oracle supports the role concept. A role is a set of system permissions to simplify permission management. In addition to allowing DBA to define roles, Oracle also provides predefined roles, such as connect, resource, and DBA.

Users with the connect role can log on to the database to query and manipulate data. You can execute alter table, create view, create index, drop table, drop view, drop index, Grant, revoke, insert, select, update, delete, audit, noaudit, and other operations.

The resource role can create a table, that is, execute the create table operation. The user who creates the table will have all permissions on the table.

The DBA role can execute some authorization commands to create tables and manipulate data in any tables. It covers the first two roles and can also perform some management operations. The DBA role has the highest level of permissions.

For example, after DBA creates a user u1, it wants to alter table, create view, create index, drop table, drop view, drop index, Grant, revoke, insert, select, update, delete, if you grant U1 system permissions such as audit and noaudit, you can simply grant the connect role U1:

Grant connect to U1;

In this way, more than a dozen grant statements can be omitted.

2. database object permissions
In Oracle, database objects that can be authorized include basic tables, views, sequences, synonyms, stored procedures, and functions. The most important thing is basic tables.

Oracle supports three levels of security for basic tables: Table-level, row-level, and column-level.

(1) Table-level security
The table creator or DBA can grant table-level permissions to other users. Table-level permissions include:
ALTER: Modify Table Definitions
DELETE: DELETE table records
INDEX: Create an INDEX on a table.
INSERT: INSERT data records into the table
SELECT: query records in a table
UPDATE: Modify Table Data
ALL: ALL the preceding Permissions

GRANT and REVOKE statements are used for table-level authorization.

(2) Row-level security
Oracle row-Level Security is implemented by views. Using a view to define a horizontal subset of a table and limiting user operations on the view provides row-level protection for the table. Authorization and revoke on the view are exactly the same as those at the table level.

For example, if you only allow user U2 to view the data of students in the Student table, create the view S_IS for the students in the information system, and then grant the SELECT permission of the view to U2 users.

(3) column-level security
The column-level security of Oracle can be implemented by the view or defined directly on the basic table.

You can use a view to define the vertical subset of a table to implement column-level security. The method is similar to the above.

The GRANT and REVOKE statements are also used to define and REVOKE column-level permissions on basic tables. Currently, only the column-level permission of Oracle is UPDATE. When the column-level UPDATE permission is revoked, Oracle does not allow the collection of one column and one column, and only the UPDATE permission of the entire table can be revoked. For example,

Grant update (Sno, Cno) on SC TO U2;

Grant the UPDATE permission for the Sno and Cno columns in the SC table to the U2 user.

Revoke update on SC FROM U2;

Revoke the UPDATE permission of U2 users for Sno and Cno columns in the SC table.

In Oracle, table, row, and column objects form a hierarchical structure from top to bottom. the permissions of objects at the upper level restrict the permissions of objects at the lower level. For example, if a user has the UPDATE permission on a table, that is, all columns in the table have the UPDATE permission.

Oracle uses decentralized control over database objects. users WITH the with grant option are allowed to GRANT the corresponding permissions or their subsets to other users, but circular authorization is not allowed, that is, the authorized person cannot grant the permission back to the authorized person or its ancestor.

Oracle records all permission information in the data dictionary. When you perform database operations, Oracle first checks the validity of the operations based on the permission information in the data dictionary. In Oracle, security check is the first step in any database operations.

Iii. Oracle audit technology
In Oracle, audit is divided into user-level audit and system-level audit. User-level audit is an audit that any Oracle user can set. It mainly audits the database tables or views created by the user, record all successful and/or unsuccessful access requirements and various types of SQL operations on these tables or views.

System-level audit can only be set by DBA to monitor logon requirements for success or failure, to monitor grant and revoke operations, and to operate under other database-level permissions.

Oracle's audit function is flexible. Users can choose whether to use audit, which tables are audited, and which operations are audited. Therefore, Oracle provides the audit statement setting audit function, and the noaudit statement cancels the audit function. When setting audit, you can specify in detail which SQL operations are audited. For example,

You can use the following statement to audit operations on the SC table structure or data:

Audie alter, update on SC;

To cancel all audit operations on the SC table, use the following statement:

Noaudit all on SC;

In Oracle, audit settings and audit content are stored in the data dictionary. The audit settings are recorded in the SYS. Tables data dictionary table, and the audit content is recorded in the SYS. audit_trail data dictionary table.

Iv. User-Defined security measures
In addition to system-level security measures, Oracle also allows users to use database triggers to define special and more complex user-level security measures. For example, the student table can only be updated during working hours. You can define the following trigger, where sysdate is the current system time:

Create or replace trigger secure_student
Before insert or update or delete
On student
Begin
If (to_char (sysdate, 'dy ') in ('sat', 'sun '))
Or (to_number (sysdate, 'hh24') Not between 8 and 17)
Then
Raise_application_error
(-20506,
'You may only change data during normal business hours .'
);
End if;
END;

Once defined, the trigger is stored in the data dictionary. This trigger is automatically triggered every time you execute INSERT, UPDATE, or DELETE operations on the Student table. The system checks the system time at that time, if it is Saturday or Sunday, or if it is not, the system rejects the user's update operation and prompts an error message.

Similarly, you can use triggers to further refine audit rules so that audit operations are fine-grained.

To sum up, Oracle provides a variety of security measures and multi-level security checks. Its security mechanisms are independent of the operating system's security mechanisms, data Dictionary plays an important role in Oracle Security Authorization, check, and audit technology.