Skip to main content

Object Operations

info

Operations on database objects typically refer to actions such as adding, deleting, and modifying various objects within the database. These operations include but are not limited to the management of tables, views, indexes, stored procedures, triggers, and other objects. Some modifications to database objects may affect the compatibility of existing applications, requiring the redesign of indexes and queries. In high-concurrency environments, structural modifications may temporarily affect service availability.

The PawSQL audit rule system primarily captures operations that may have a negative impact on the database and provides warning messages. The rule set for Object Operations in the PawSQL audit rule system is as follows:

Prohibition of Dropping Columns Used in Indexes​

Code: DroppingColumnsUsedInIndexDisallowed

Rule Description: It is prohibited to drop columns that are used in indexes, as this may lead to the index becoming invalid and data integrity issues.

Warning Level: Warning

Audit Object: ALTER TABLE DROP COLUMN

Trigger Condition: A column used in an index has been dropped.

Configurable: No

Supported Database Types: All (ALL)

Prohibition of Adding NOT NULL Constraints to Columns​

Code: ModifyingColumnAddNotNullDisallowed

Rule Description: It is prohibited to add NOT NULL constraints to columns that already contain data, as this may lead to data insertion errors.

Warning Level: Warning

Audit Object: ALTER TABLE MODIFY COLUMN

Trigger Condition: NOT NULL constraints have been added to columns with existing data.

Configurable: No

Supported Database Types: All (ALL)

Prohibition of Adding Default Values to Columns​

Code: ModifyingColumnAddDefaultDisallowed

Rule Description: It is prohibited to add default values to columns, especially when there is existing data in the column, to avoid data inconsistency.

Warning Level: Warning

Audit Object: ALTER TABLE MODIFY COLUMN

Trigger Condition: Default values have been added to columns.

Configurable: No

Supported Database Types: All (ALL)

Prohibition of Modifying Default Values of Columns​

Code: ModifyingColumnChangeDefaultDisallowed

Rule Description: It is prohibited to modify the default values of columns to avoid uncertainty during data insertion.

Warning Level: Warning

Audit Object: ALTER TABLE MODIFY COLUMN

Trigger Condition: The default value of a column has been modified.

Configurable: No

Supported Database Types: All (ALL)

Prohibition of Adding Columns with Default Values​

Code: AddingColumnsWithDefaultDisallowed

Rule Description: It is prohibited to add columns with default values to avoid potential issues during data insertion.

Warning Level: Warning

Audit Object: ALTER TABLE ADD COLUMN

Trigger Condition: The added column has a default value.

Configurable: No

Supported Database Types: All (ALL)

Adding CHECK Constraint Should Be Deferred​

Code: AddCheckConstraintShouldBeDeferred

Rule Description: When adding a CHECK constraint, it should be specified as NO VALID to avoid performance issues caused by immediate validation of existing data.

Warning Level: Warning

Audit Object: ALTER TABLE ADD CONSTRAINT

Trigger Condition: A CHECK constraint has been added without being specified as NO VALID.

Configurable: No

Supported Database Types: All (ALL)

Create Index Before Constraint​

Code: CreateIndexBeforeConstraint

Rule Description: Before creating a constraint, the related index should be created first to improve the efficiency of constraint checks.

Warning Level: Warning

Audit Object: ALTER TABLE ADD CONSTRAINT

Trigger Condition: No related index has been created before creating a constraint.

Configurable: No

Supported Database Types: All (ALL)

Prohibition of Updating Columns Used in Indexes​

Code: UpdatingColumnsUsedInIndexDisallowed

Rule Description: It is prohibited to update columns that are used in indexes, as this may lead to the index becoming invalid and data inconsistency.

Warning Level: Warning

Audit Object: ALTER TABLE MODIFY COLUMN

Trigger Condition: A column used in an index has been updated.

Configurable: No

Supported Database Types: All (ALL)

Recommendation to Use Online Mode When Creating Indexes​

Code: CreateIndexUsingOnlineMode

Rule Description: It is recommended to use online mode when creating indexes to reduce the impact on database operations.

Warning Level: Warning

Audit Object: CREATE INDEX, ALTER TABLE ADD INDEX

Trigger Condition: Indexes have been created without using online mode.

Configurable: No

Supported Database Types: PostgreSQL/Oracle/openGauss

Prohibition of Dropping Indexes​

Code: DroppingIndexDisallowed

Rule Description: It is prohibited to drop indexes, as indexes are crucial for query performance.

Warning Level: Warning

Audit Object: DROP INDEX

Trigger Condition: An index has been dropped.

Configurable: No

Supported Database Types: All (ALL)

Prohibition of Specifying Collation When Creating Indexes​

Code: Collation4IndexDisallowed

Rule Description: It is prohibited to specify collation when creating indexes, as this may affect the performance of the index and the consistency of query results.

Warning Level: Warning

Audit Object: CREATE INDEX, ALTER TABLE ADD INDEX

Trigger Condition: Collation has been specified when creating an index.

Configurable: No

Supported Database Types: All (ALL)

Prohibition of Changing Column Names​

Code: ChangingColumnNameDisallowed

Rule Description: It is prohibited to change column names, as this may break application logic and queries that depend on the column.

Warning Level: Warning

Audit Object: ALTER TABLE RENAME COLUMN

Trigger Condition: A column name has been changed.

Configurable: No

Supported Database Types: All (ALL)

Prohibition of Changing Column Data Types​

Code: ChangingColumnTypeDisallowed

Rule Description: It is prohibited to change the data type of a column, as this may lead to data loss or inconsistency.

Warning Level: Warning

Audit Object: ALTER TABLE MODIFY COLUMN

Trigger Condition: The data type of a column has been changed.

Configurable: No

Supported Database Types: All (ALL)

Prohibition of Shortening Data Type Length​

Code: ShortenDataTypeLengthDisallowed

Rule Description: It is prohibited to shorten the length of a data type, as this may lead to data truncation.

Warning Level: Warning

Audit Object: ALTER TABLE MODIFY COLUMN

Trigger Condition: The length of a data type has been shortened.

Configurable: No

Supported Database Types: All (ALL)

Prohibition of Decreasing Field Precision​

Code: DecreasingPrecisionDisallowed

Rule Description: It is prohibited to decrease the precision of a field, as this may lead to a loss of data precision.

Warning Level: Warning

Audit Object: ALTER TABLE MODIFY COLUMN

Trigger Condition: The precision of a field has been decreased.

Configurable: No

Supported Database Types: All (ALL)

Prohibition of Setting Non-Null Default Values for Columns in the List​

Code: LargeObjectDefaultNonNullDisallowed

Rule Description: It is prohibited to set non-null default values for columns of BLOB, CLOB, TEXT, and other large object types, as this may lead to performance issues and waste of storage space.

Warning Level: Warning

Audit Object: CREATE TABLE, ALTER TABLE ADD COLUMN

Trigger Condition: A non-null default value has been set for a column of a large object type.

Configurable: Yes

Example: The expression can be configured as JSON,BJSON,XML,BLOB, CLOB,TEXT, indicating that non-null default values are prohibited for these types of columns.

Supported Database Types: All (ALL)

Non-Null Default Values Should Be Set for Columns Not in the List​

Code: NonNullDefaultValue4NormalColumnsRequired

Rule Description: For columns that are not of large object types, non-null default values should be set to ensure data integrity.

Warning Level: Warning

Audit Object: CREATE TABLE, ALTER TABLE ADD COLUMN

Trigger Condition: Non-null default values have not been set for columns that are not of large object types.

Configurable: Yes

Example: The expression can be configured as JSON,BJSON,XML,BLOB, CLOB,TEXT, indicating that non-null default values should be set for all other types of columns.

Supported Database Types: All (ALL)

Prohibition of Setting Non-Null Constraints for Columns in the List​

Code: NonNullConstraint4ColumnsInListDisallowed

Rule Description: It is prohibited to set non-null constraints for columns of BLOB, CLOB, TEXT, and other large object types, as this may lead to performance issues and data insertion errors.

Warning Level: Warning

Audit Object: CREATE TABLE, ALTER TABLE MODIFY COLUMN

Trigger Condition: A non-null constraint has been set for a column of a large object type.

Configurable: Yes

Example: The expression can be configured as JSON,BJSON,XML,BLOB, CLOB,TEXT, indicating that non-null constraints are prohibited for these types of columns.

Supported Database Types: All (ALL)

Prohibition of Dropping Tables/Views​

Code: DropTable/ViewDisallowed

Rule Description: It is prohibited to drop tables or views to prevent data loss and application interruptions.

Warning Level: Warning

Audit Object: DROP TABLE, DROP VIEW

Trigger Condition: A table or view has been dropped.

Configurable: No

Supported Database Types: All (ALL)

Prohibition of Dropping Columns​

Code: DroppingColumnsDisallowed

Rule Description: It is prohibited to drop columns in a table to prevent data loss and application interruptions.

Warning Level: Warning

Audit Object: ALTER TABLE DROP COLUMN

Trigger Condition: A column in a table has been dropped.

Configurable: No

Supported Database Types: All (ALL)

Only Tables and Views with Specified Naming Conventions Can Be Dropped​

Code: DropTable/ViewOnlyWithConversion

Rule Description: This rule restricts the dropping of tables and views to those that conform to specific naming conventions, which helps maintain consistency and maintainability of the database structure.

Warning Level: Warning

Audit Object: DROP TABLE, DROP VIEW

Trigger Condition: The name of the dropped table or view does not conform to the specified naming convention (e.g., ending with _del$).

Configurable: Yes

Example: The expression can be configured as a regular expression, such as .*_del$, indicating that only tables or views ending with _del are allowed to be dropped.

Supported Database Types: All (ALL)

Only Columns with Specified Naming Conventions Can Be Dropped​

Code: DropColumnsOnlyWithConversion

Rule Description: Similar to RuleCode TableR083, this rule restricts the dropping of columns to those that conform to specific naming conventions.

Warning Level: Warning

Audit Object: ALTER TABLE DROP COLUMN

Trigger Condition: The name of the dropped column does not conform to the specified naming convention.

Configurable: Yes

Example: The expression can be configured as a regular expression, such as .*_del$, indicating that only columns ending with _del are allowed to be dropped.

Supported Database Types: All (ALL)

IF NOT EXISTS Should Be Specified When Creating Tables/Views/Indexes​

Code: IfNotExists4CreateObjectsRequired

Rule Description: When creating tables, views, or indexes, the IF NOT EXISTS statement should be used to avoid errors caused by the existence of the object.

Warning Level: Warning

Audit Object: CREATE TABLE, CREATE VIEW, CREATE INDEX

Trigger Condition: The creation operation did not use the IF NOT EXISTS statement.

Configurable: No

Supported Database Types: All (ALL)

IF EXISTS Should Be Specified When Dropping Tables/Views/Indexes​

Code: IfExists4AlterDropObjectsRequired

Rule Description: When dropping tables, views, or indexes, the IF EXISTS statement should be used to avoid errors caused by the non-existence of the object.

Warning Level: Warning

Audit Object: DROP TABLE, DROP VIEW, DROP INDEX

Trigger Condition: The deletion operation did not use the IF EXISTS statement.

Configurable: No

Supported Database Types: All (ALL)

Prohibition of Modifying the Default Character Set of Tables​

Code: ModifyTableCharSetDisallowed

Rule Description: It is prohibited to modify the default character set of tables to avoid data conversion issues and potential data corruption.

Warning Level: Warning

Audit Object: ALTER TABLE MODIFY COLUMN

Trigger Condition: The default character set of a table has been modified.

Configurable: No

Supported Database Types: MySQL