Informix Management: Protecting IDs data

Objective

Protecting your data requires a trade-off between security and access capabilities. How do you find the most appropriate way?

At present, the company and government have established strict standards for the auditing and control of data, which makes database security a hot spot recently. DBAs must ensure that users have access to the data needed to complete their work while avoiding data risk.

The only way to absolutely protect the data is to shut down the server and lock it up and keep it from being used. However, to make the database work, you must allow access to the data. IDS applies security measures at the database, table, and column levels. Security measures can also be applied to the record level in the view. All security measures are based on the user name used to connect to the database.

I'll explain how the Informix security feature helps you protect your data while allowing the right users to access the data.

Security recommendations

There are different database security requirements for each environment and for each phase of database development. The development environment must allow programmers to quickly create and modify database objects. Testing and production environments require stability, and you need to control what you can do with database objects. A key part of an effective database security model is defining and assigning DBA, Database Security Officer (DSO), and object owner roles.

For the DBA role, you should create an abstract user login. DBAs are essentially database super users. Setting up a separate login reduces the risk of users accidentally deleting the database. This principle is particularly important in the production environment. The DBA logon name should be the creator of all tables in the database and in the database. The DBA should be the only user with modify privileges on any table in the database. As the creator of all tables in the database and database, the DBA can grant privileges to other users.

Some production environments may require a DSO, a separate position outside the DBA, who is responsible for data security. DSO should also be a separate abstract user login. In order to be able to add new users to the database, DSO requires DBA database privileges. DSO also needs to grant privileges to each table in the database in order to grant privileges to other users.

In order to establish a role with DSO privileges, the DSO must be assigned to all privileges that users may need for each table in the database. The following is the SQL syntax using the grant command with GRANT OPTION:

Grantselect,insert,update,delete,indexontable_nametodso_login_name

Withgrantoption;

......

Public privilege

Informix uses the keyword public to represent the privileges that apply to all users. The Public keyword is a very powerful tool to open access to data. Similarly, to prevent all unauthorized users from connecting to the database and selecting data, you can completely undo the public privilege.

Object owner Privilege

Users who create databases or database objects (tables, indexes, functions, procedures, and so on) are the owners of this object, and he controls access and privileges for that object. The owner of the object has all table-level privileges, and these privileges cannot be undone. This limitation can cause problems when the system moves from the development environment to the production environment, or when the original owner of the table or database leaves the organization. No other user, even the DBA, can revoke the owner's privileges. Therefore, the owner of the database or table must be carefully determined.

The owner of the table is the only user who can grant this table privileges to other users. Some applications also use the OWNER.TABLENAME syntax to access tables. In one of my client's databases, tables have different owners in the development, acceptance testing, and production phases. A table can have only one owner. When you transfer application code from a development environment to a test environment and then to a production environment, you must change the owner of the table, which can cause problems. In different environments, you must modify the Owner.tablename in the application code, which increases the risk.