Oracle User privilege Management and auditing

1 Introduction Information is important for success, but if information is compromised or exploited incorrectly, it poses a threat to success. Oracle provides a wide range of security features to protect users ' information from unauthorized access and intentional or unintentional destruction. This security is provided by granting or revoking permissions on the basis of user to user, permissions to permissions, and is attached to the existing security mechanism of the computer system (which is independent of the user). On the basis of realizing the authority assignment, we are faced with the real-time tracking of the system resources and database resources, monitoring the user's behavior to the database, which is the necessary audit function of the large database management system. Here, we will discuss the Orale user Rights Assignment and the audit realization thought and method together. 2 User Management Mode Dbms:database Management System, database management systems, centralized and unified data management and manipulation. The main DBMS now includes Oracle SQL server,sybase Adaptive Server 11,microsoft SQL Server 6.x,7.x, in which all have their own complete user management model, are generally carried out in the following ways: Login User: DBO, mainly used to provide connectivity to the database services; Role users: Divide the logged-in user into groups that have various database operations rights, and a logged-on user can play a different role; DBMS provides the basic ways of these user management, and each has its own audit and management methods for tables, views, processes, triggers, etc. In the actual application, if the actual situation allows, the actual users can establish a login (account), and all the accounts for strict authority management. However, if the number of users is not fixed, and there may be hundreds, the complexity of management and difficulty can be imagined. This is called Single-user-multiuser mode. It refers to the database login user mode, all applications are logged in a unified user, the user has all the tables, views, procedures, functions, and so on all the operational rights, and these objects are created and owned by dbo, you can call these users as an application user (stored in a table) , and the user who logs on to the database is the mode user. In fact, in order to prevent data logging outside the system, you can add a join user who can only read a table that records the login parameters of the mode user (the specific part can be stored in encrypted form); This kind of single-multi-user way in the large-scale MIS system, because of its simple realization, the idea is clear, so the application is quite many, its advantage is obvious: (1) Simple to set up, especially authorization, can be more easily realized; (2) Simple management, as long as the maintenance of a model users can be; (3) is transparent to the developer, that is, the development user login in the dbo way; 3 Design Ideas The main points of the current more feasible approach are: (1) All entities (tables, views, etc.) are established by a logged-on user (DBO), but the user does not have the right to connect and manipulate those entities (insert,delete,update, etc.); (2) Classify all the actual users, and generalize them into several specific roles (actual roles); (3) One kind of actual role corresponds to a login user, establishes the account system, carries on the role assignment, the authority setting; (4) In application, when a user connects, according to the actual role played by the corresponding login user login; (5) According to the corresponding table of the user's available modules (functions), appropriate processing, so that users only in the custom, permitted scope for functional operations and database operations. (6) Establish the necessary tables for the audit, used to detail the database behavior to be tracked user's machine properties, permissions and roles, the start and deadline of each Access database, table-level access objects, data manipulation, data manipulation objects and specific actions, record values before and after the operation, and so on. According to the above points, in a specific application, the development work involved include: Table Design: (1) Analysis of the actual role (group); (2) Establish the Application User account table, which records the group to which the user belongs, and establishes the user Group table; (3) Establish a universal connection user (can only retrieve the user account table), all the users initially connect the database to the user, and then retrieve the user according to the actual login and user-owned groups, to the group of the corresponding users to connect; (4) Set up the module (function) table, establish the user, user group and the table of the corresponding table, that is, what the user can do what kind of operation. The Rights Assignment function design: (1) Establish the module (function) Manager, manage all the relevant information of the available modules; (2) Establish user, user group Rights Manager, manage the function that a user (group) can use. When the user launches the application record, the following procedure is followed: (1) All users have the initial connection with the fixed connection user; (2) The user enters own code and the password, confirms the user according to the Account table; (3) To obtain information such as the group to which the user belongs (that is, the name of the logged-on user who can connect to the database); (4) Reconnect to the database and assign roles; (5) Dynamic Data fragmentation according to the role; (6) Retrieving the user-owned group and the module information and layout, adjusting the menu or interface; (7) Open the main window and end. Audit function Design: A mature audit system needs to solve many problems, such as determining audit area, how to record audit trail files, what kind of audit information to collect, how many system resources and human resources are devoted to audit function, how much performance reduction is the maximum tolerable limit, who is responsible for monitoring audit results, What actions should be taken after a database is destroyed, how to perform database corruption reports, what methods to use to repair corrupted data, how long to review an audit method, and so on. We're here to simply talk about how to determine the audit area and collect and manage audit information. According to the audit area, the audit is divided into statement audit, privilege audit, model object audit and resource audit. Statement auditing is the monitoring of one or more specific users or all user-submitted SQL statements; A privilege audit is a system privilege that monitors the use of one or more specific users or all users; Pattern object auditing is the monitoring of behavior that occurs on one or more objects in a pattern. A resource audit is a monitoring of the number of resources assigned to each user. The main thing we talk about here is Pattern object auditing. (1) The whole idea of mode object audit To implement the audit function, you can select three audit tables in SYS mode in the Oracle database for audit support, respectively, aud$ (recording audit information), audit$ () and Audit_actions (description of the storage audit trail behavior Type Code), Although this Oracle audit captures who made the change and when it was changed, capturing data changes can be difficult and time-consuming. So what we're using is manually creating the audit information table and recording the data changes by setting up triggers. (2) Establish audit module (function) Manager (Audit information Form) as needed, manage information about all available audit modules, including user IDs and user names, session identifiers, accessed schema object names (tables, stored procedures, indexes, triggers, etc.), executed or attempted operations, complete code of operations, Time and date stamps and the system privileges used, and so on. The structure of the Audit information table is: "Modify Table Name" (20), "Action type" (10), "Old identity Number" (18), "Old name" (40), "New identity Number" (18), "New name" (40), "Machine name" (20), "User" (20), "Customer Information" (50), "Time" date, "modify field name 1 "(20)," field name old value 1 "(50)," field name new value 1 "(50)," Modify Field Name 2 "(20)," Field name old Value 2 "(50)," Field name new Value 2 "(50)," Session Number "(*)," ordinal "number (*). You can adjust the number of constantly fields, depending on your actual needs. (3) The use and maintenance of audit procedures by database administrators with DBA Authority, maintenance of audit information and periodic archiving, and database recovery in the case of a database disaster. 4 Some difficult points in the concrete realization (1) Create role users by logged-in user Figure 1 shows an interface for creating a role user GBSJ by the logged-on user gbase, where you can also control the read and write permissions of GBSJ users to all database tables, and you can ask if you want to copy the table structure and data to the current user. Figure 1 (2) Data slicing technology The first thing to say is what data fragmentation is. In Oracle's user rights assignment, only insert,update,select,delete,execute such as tables, functions, synonyms, views, packages, and so on are provided, and no field-level permission settings are provided (in fact, The field-level permission setting for large databases has a security benefit, but it has a certain impact on performance, so for large mis systems, data fragmentation is unavoidable. Generally, a typical MIS database platform is designed like this, as shown in Figure 2. Figure 2 The allocation of user rights is accomplished by the Oracle Temporary system table: The ① establishes related fields in the table (CUSTOMER) that need to be fragmented, such as sgrant_num number (20) and the subordinate department sunit_id varchar (10), ② Create a dynamic view of the table CUSTOMER_PV SELECT * from CUSTOMER A where Cd_warehouse. SUNIT_ID is null or exists (SELECT * from CUSTOMER B where a.sunit_id = b.sunit_id and (B.sgrant_num >= to_number (SUBSTR (USERENV (' Client_info '), 1, 10)) and (B.sgrant_num <= to_number (SUBSTR (USERENV (' Client_info '), 11,10))) ③ modifies the fragment number in the user environment Client_info when the user logs on to the system; ④ in this way, there is no fragmentation problem for the designer, and for the end user, the organization fragment number of the user can be defined to constrain the data that the user is able to access. The schematic diagram is shown in Fig. 3. Figure 3 The advantages of this data fragmentation are: The ① is automatically completed by the server and has no effect on the design. ② speed, no significant impact on the huge amount of data; Disadvantages are: The ① can only be vertically fragmented according to the organization (or other), not flexible enough; ② technology implementations are complex, and changes to the primary table directly affect the view (requiring regeneration); ③ Once the organizational structure changes, the fragmentation needs to be reset. (3) The implementation of the audit function can not capture the behavior of the user's customer information and action information and data before and after the action How to capture customer information ①: After a careful look at the Oracle database documentation, we finally found that this functionality can be achieved by invoking the USERENV function of Oracle itself. The way to capture a machine name is Userenv (' TERMINAL '), which captures the user name of the logged-on computer by Userenv (' Client_info '), and the user name that captures the login database is read directly out of the Oracle value. ② Capture Behavior actions: The method for capturing the current session identifier is USERENV (' SESSIONID '). ③ the method of capturing data before and after a behavior action: A trigger is built in the database, and the data before and after the action is inserted into the audit table in real time for the table of behavioral action. The audit control interface is shown in Figure 4: Figure 4 Under this control interface, we can achieve by machine name, user and session monitoring and management audit information, browsing the session information of the current active session, and can filter out the audit information through the condition, and complete the audit information archiving and printing functions, and through the audit information switching function, you can browse archived audit information. At present, this model audit function has been realized, and has been applied in a large system, and achieved satisfactory results. 5 concluding remarks Oracle User Rights Assignment and maintenance as well as audit are very complex topics, for a period of time, both the database administrator and programmers, will be a big challenge. To solve this problem is to ensure the data security, accuracy, convenience, flexibility and portability of large database system will be the foundation of Enterprise informatization construction. Due to the author's level and energy is limited, can not have a higher contribution in this field, only hope that this article and everyone to discuss.