Security policy for Oracle database
- 2020-05-06 11:50:41
- OfStack
The ORACLE tutorial you are looking at is: security policy for Oracle databases. -- Oracle is a relational database management system with powerful functions and excellent performance, which occupies an important position in today's large-scale database management systems. In the MIS system we developed, Oracle 7.3 database was selected. Under normal circumstances, Oracle database will ensure data security, stability, to provide users with the right data, but as a result of the computer system failure (hardware failure, software failure, network failure and system failure) affect the operation of the database system, affect the validity of the data in the database, or even destroy the database, make the loss of all or part of the data in the database, the whole system will be paralyzed. Therefore, how to ensure the security of Oracle database becomes an important part of the security of MIS system.
-- security policy for Oracle database includes database backup and recovery, user role management.
-- the structure
used for database backup
The Oracle database USES several structures to protect data: database backup, logs, rollback segments, and control files.
- 1. Database backing is made up of the operating system backing of the physical files that make up the Oracle database. When the media failure database recovery, using backup files to recover damaged data files or control files.
- 2. Each Oracle database instance provides a log of all changes made in the database. Each running Oracle database instance correspondingly has an online log that works with LGWR, the Oracle background process, to immediately record all changes made to the instance. Archive (offline) logs are optional, and an Oracle database instance forms an online log archive once the online logs are filled. Archived online log files are uniquely identified and merged into archive logs.
- 3. The rollback segment is used to store the old value of the value modified by an ongoing (uncommitted) transaction, and this information is used to undo any non-committed changes during database recovery.
- 4. A control file that is typically used to store the state of the physical structure of a database. Some state information in the control file is used to boot Oracle during instance recovery and media recovery.
-- online journal
Each instance of an Oracle database has an online log associated with it. An online log consists of multiple online log files. The online log file (online redo log file) fills in the log entry (redo entry), which records the data used to reconstruct all changes made to the database.
-- three, archive log
When Oracle wants to archive a full online log filegroup, an archive log is created (archived redo log). It has the following USES for database backup and recovery:
- 1. Database backup and online and archived log files ensure that all committed items can be recovered in the event of operating system and disk failure.
- 2. Online backup can be performed and used with database open and normal system use if archived logs are permanently saved.
The database can be run in two different ways: NOARCHIVELOG or ARCHIVELOG. Online log archiving is not possible when the database is used in NOARCHIVELOG mode. Archive online logs if the database is running in ARCHIVELOG mode.
-- four, Oracle backup features
-- Oracle backup includes both logical and physical backups.
- 1. Logical backup
A logical backup of the database consists of reading a database recordset and writing the recordset to a file.
-- (1) output (Export) the output can be the entire database, the specified user, or the specified table.
-- (2) input (Import) input reads in the binary dump file created by the output and executes its command.
- 2. Physical backup
A physical backup contains copies of the files that make up the database, regardless of its logical content.
-- Oracle supports two different types of physical file backup: offline backup (offline backup) and online backup (online backup).
-- (1) offline backup
Offline backup is used when the database is normally closed and the database is at "offline" to back up the following files:
All data files
All control files
All online logs
init.ora (optional)
-- (2) online backup
Online backup can be used to backup any database operating in ARCHIVELOG mode. In this way, the online logs are archived, creating a complete record of all jobs within the database.
The online backup process has powerful capabilities. First, it provides a complete point in time (point-in-time) recovery. Second, allow the database to remain open while the file system is backed up.
-- backup mode features compare
Mode type recovery feature
The Export logic can restore any database object to the state it was in at the time of the output Offline Backups physical returns the database to a closed state: if the database is running in ARCHIVELOG mode, it can be restored to a state at any point in time.
Online Backups physically restores the database to
at any point in time
-- 5. Oracle database role management
-- the basic measures taken by the Oracle database system in using roles to manage database security are:
By verifying the user name and password, it prevents non-Oracle users from registering with Oracle database and performing illegal access operations to the database.
Grant users certain privileges, limit the user's power to manipulate the database.
Granting access to database entities prevents users from accessing unauthorized data.
Provides a database entity access audit mechanism to enable the database administrator to monitor the access of data in the database and the use of system resources.
The view mechanism is used to restrict access to the collection of rows and columns of the base table.
--
security policy for Oracle database in MIS system instance Since there are three ways to backup Oracle database, each with a different recovery feature, database and file system backup, logical backup, and physical backup should be integrated.
-- one, backup strategy
1. At the operating system level, each database file is automatically distributed on each physical disk using a high-capacity disk array, using disk imaging technology. In this way, when a disk is physically damaged, the operating system will automatically raise an image disk to replace the failed disk, ensuring the normal operation of the database.
Since we are using a dual server, we keep a backup database on another server. The backup database has the same parameter state as the database in use, which minimizes recovery time in the event of a database failure by requiring minimal recovery.
Maintains a backup of multiple control files on multiple different physical disks. Control files are used to boot Oracle during database recovery, so keeping a backup of multiple control files ensures that control files are available for database recovery in the event of a disk failure.
Have the database run in ARCHIVELOG (archive) mode, with the archive log stored on the logical disk of another image.
Perform an online backup operation every night, backing up all data files, all archive log files, and a control file.
Perform an output (Export) operation once a week.
-- ii. Recovery strategy
- 1. The instance failed
Recovery from an instance failure is automatic. Instance failure is typically caused by a server failure. When the database instance fails, restart the server, start the database, Oracle checks the data files and online log files, and synchronizes all the files to the same point in time.
- 2. Disk failure
If it is a control file that is missing, simply close the database and copy from where the control file is kept.
If it is a data file that is lost, the online backup from the previous night can be used to restore it, as follows:
-- 1) save the lost file in its original location from the backup.
-- 2) load database
-- 3) restore database
-- 4) open database
- 3. Object
was mistakenly deleted or modified
In this case, you want to be able to trace back to the time before the error occurred. This is called point in time recovery. The steps to complete the recovery are as follows:
-- 1) output (Export) and input (Import) from the current database to the standby database to make the standby database consistent with the current database.
-- 2) scroll forward to the point in time before the error occurred.
-- 3) output the error-affected logical objects from the standby database.
-- 4) input the affected objects into the current database using the output file generated in the previous step.
-- iii. User role management
- 1. All clients are classified by the nature of their work and are granted different user roles.
- 2. Different database object access permissions are granted to different user roles, depending on the data source they are using.
-- based on the above security policy, it can prevent illegal users from accessing the database and restrict the permission of legal users to manipulate the database; Can make the database after the failure, will not lose any data, and can make the database quickly recover to the time point before the failure, to ensure the safety of the data and the continuous operation of the entire system.
-- security policy for Oracle database includes database backup and recovery, user role management.
-- the structure
used for database backup
The Oracle database USES several structures to protect data: database backup, logs, rollback segments, and control files.
- 1. Database backing is made up of the operating system backing of the physical files that make up the Oracle database. When the media failure database recovery, using backup files to recover damaged data files or control files.
- 2. Each Oracle database instance provides a log of all changes made in the database. Each running Oracle database instance correspondingly has an online log that works with LGWR, the Oracle background process, to immediately record all changes made to the instance. Archive (offline) logs are optional, and an Oracle database instance forms an online log archive once the online logs are filled. Archived online log files are uniquely identified and merged into archive logs.
- 3. The rollback segment is used to store the old value of the value modified by an ongoing (uncommitted) transaction, and this information is used to undo any non-committed changes during database recovery.
- 4. A control file that is typically used to store the state of the physical structure of a database. Some state information in the control file is used to boot Oracle during instance recovery and media recovery.
-- online journal
Each instance of an Oracle database has an online log associated with it. An online log consists of multiple online log files. The online log file (online redo log file) fills in the log entry (redo entry), which records the data used to reconstruct all changes made to the database.
-- three, archive log
When Oracle wants to archive a full online log filegroup, an archive log is created (archived redo log). It has the following USES for database backup and recovery:
- 1. Database backup and online and archived log files ensure that all committed items can be recovered in the event of operating system and disk failure.
- 2. Online backup can be performed and used with database open and normal system use if archived logs are permanently saved.
The database can be run in two different ways: NOARCHIVELOG or ARCHIVELOG. Online log archiving is not possible when the database is used in NOARCHIVELOG mode. Archive online logs if the database is running in ARCHIVELOG mode.
-- four, Oracle backup features
-- Oracle backup includes both logical and physical backups.
- 1. Logical backup
A logical backup of the database consists of reading a database recordset and writing the recordset to a file.
-- (1) output (Export) the output can be the entire database, the specified user, or the specified table.
-- (2) input (Import) input reads in the binary dump file created by the output and executes its command.
- 2. Physical backup
A physical backup contains copies of the files that make up the database, regardless of its logical content.
-- Oracle supports two different types of physical file backup: offline backup (offline backup) and online backup (online backup).
-- (1) offline backup
Offline backup is used when the database is normally closed and the database is at "offline" to back up the following files:
All data files
All control files
All online logs
init.ora (optional)
-- (2) online backup
Online backup can be used to backup any database operating in ARCHIVELOG mode. In this way, the online logs are archived, creating a complete record of all jobs within the database.
The online backup process has powerful capabilities. First, it provides a complete point in time (point-in-time) recovery. Second, allow the database to remain open while the file system is backed up.
-- backup mode features compare
Mode type recovery feature
The Export logic can restore any database object to the state it was in at the time of the output Offline Backups physical returns the database to a closed state: if the database is running in ARCHIVELOG mode, it can be restored to a state at any point in time.
Online Backups physically restores the database to
at any point in time
-- 5. Oracle database role management
-- the basic measures taken by the Oracle database system in using roles to manage database security are:
By verifying the user name and password, it prevents non-Oracle users from registering with Oracle database and performing illegal access operations to the database.
Grant users certain privileges, limit the user's power to manipulate the database.
Granting access to database entities prevents users from accessing unauthorized data.
Provides a database entity access audit mechanism to enable the database administrator to monitor the access of data in the database and the use of system resources.
The view mechanism is used to restrict access to the collection of rows and columns of the base table.
--
security policy for Oracle database in MIS system instance Since there are three ways to backup Oracle database, each with a different recovery feature, database and file system backup, logical backup, and physical backup should be integrated.
-- one, backup strategy
1. At the operating system level, each database file is automatically distributed on each physical disk using a high-capacity disk array, using disk imaging technology. In this way, when a disk is physically damaged, the operating system will automatically raise an image disk to replace the failed disk, ensuring the normal operation of the database.
Since we are using a dual server, we keep a backup database on another server. The backup database has the same parameter state as the database in use, which minimizes recovery time in the event of a database failure by requiring minimal recovery.
Maintains a backup of multiple control files on multiple different physical disks. Control files are used to boot Oracle during database recovery, so keeping a backup of multiple control files ensures that control files are available for database recovery in the event of a disk failure.
Have the database run in ARCHIVELOG (archive) mode, with the archive log stored on the logical disk of another image.
Perform an online backup operation every night, backing up all data files, all archive log files, and a control file.
Perform an output (Export) operation once a week.
-- ii. Recovery strategy
- 1. The instance failed
Recovery from an instance failure is automatic. Instance failure is typically caused by a server failure. When the database instance fails, restart the server, start the database, Oracle checks the data files and online log files, and synchronizes all the files to the same point in time.
- 2. Disk failure
If it is a control file that is missing, simply close the database and copy from where the control file is kept.
If it is a data file that is lost, the online backup from the previous night can be used to restore it, as follows:
-- 1) save the lost file in its original location from the backup.
-- 2) load database
-- 3) restore database
-- 4) open database
- 3. Object
was mistakenly deleted or modified
In this case, you want to be able to trace back to the time before the error occurred. This is called point in time recovery. The steps to complete the recovery are as follows:
-- 1) output (Export) and input (Import) from the current database to the standby database to make the standby database consistent with the current database.
-- 2) scroll forward to the point in time before the error occurred.
-- 3) output the error-affected logical objects from the standby database.
-- 4) input the affected objects into the current database using the output file generated in the previous step.
-- iii. User role management
- 1. All clients are classified by the nature of their work and are granted different user roles.
- 2. Different database object access permissions are granted to different user roles, depending on the data source they are using.
-- based on the above security policy, it can prevent illegal users from accessing the database and restrict the permission of legal users to manipulate the database; Can make the database after the failure, will not lose any data, and can make the database quickly recover to the time point before the failure, to ensure the safety of the data and the continuous operation of the entire system.