Database Auditing-Computer Sciences Applications-Project Report, Study Guides, Projects, Research of Applications of Computer Sciences

Download Database Auditing-Computer Sciences Applications-Project Report and more Study Guides, Projects, Research Applications of Computer Sciences in PDF only on Docsity! i Table of Contents 1 Data Access Accountability ........................................................................................ 2 1.1 Auditing is Necessary .......................................................................................... 2 1.2 Key to Effective Solutions .................................................................................. 3 1.2.2 Mid Tier Portal ............................................................................................ 3 1.2.3 Trigger Based Solution ............................................................................... 4 1.2.4 Non Trigger Tracking at Data Source ......................................................... 4 2 Native Auditing Tools................................................................................................. 5 2.1 Native Auditing Problems................................................................................... 5 2.2 Third Party Auditing Solution ............................................................................ 6 2.2.1 Tradeoffs ..................................................................................................... 6 2.2.2 Database Auditing Features ........................................................................ 7 3 Privileged Users .......................................................................................................... 9 3.1 Challenge Regarding Privileged Users ............................................................. 10 3.1.1 Authorized Privileged Users ..................................................................... 10 3.1.2 Authorized Users with Excessive Privileges ............................................ 10 3.1.3 Unauthorized Accounts with Excessive Privileges................................... 10 3.1.4 Conclusions ............................................................................................... 10 3.2 Best Practices .................................................................................................... 11 3.2.1 Native Auditing ......................................................................................... 11 3.2.2 Real Time Activity Monitoring ................................................................ 12 3.2.3 Insider Attacks .......................................................................................... 12 4 Project Plan ............................................................................................................... 14 4.1 Project Chronology ........................................................................................... 15 4.2 Division of Work .............................................................................................. 16 4.3 Project Progress ................................................................................................ 17 4.3.1 6th Semester .............................................................................................. 17 4.3.2 Summer Semester ..................................................................................... 17 4.3.3 7th Semester Mid ...................................................................................... 17 4.3.4 7th Semester Mid- Final............................................................................ 18 5 Major Software Functionalities................................................................................. 20 5.1 Authentication ................................................................................................... 20 5.2 Users Query for Inspections.............................................................................. 20 5.3 Generate Auditing Report ................................................................................. 20 5.4 Suspicious Query Detection .............................................................................. 21 5.5 Generation of Email Alerts for Suspicious Actions .......................................... 21 5.6 View Email ....................................................................................................... 21 5.7 Malicious Query at off Hours ........................................................................... 21 6 Process Modeling ...................................................................................................... 22 6.1 Flow Charts Symbols ........................................................................................ 22 6.2 DB Audit Flowchart .......................................................................................... 22 7 Analysis Modeling .................................................................................................... 23 7.1 Context Diagram ............................................................................................... 24 7.1.1 Level 1 DFD ............................................................................................. 25 7.1.2 Detailed DFD ............................................................................................ 26 7.2 Behavioral Modeling ........................................................................................ 31 7.2.1 User Authentication .................................................................................. 31 7.2.2 Processing Query ...................................................................................... 32 docsity.com ii 7.2.3 Checking User Actions ............................................................................. 33 8 T SQL for Database .................................................................................................. 34 8.1 Sample Database Club ...................................................................................... 34 8.1.1 Member Table ........................................................................................... 34 8.1.2 Tour Table ................................................................................................. 36 8.1.3 Entry Table................................................................................................ 36 8.2 Technical Details of Implementation ................................................................ 37 8.2.1 Implementation of Insert Trigger .............................................................. 38 8.2.2 Implementation of GetCurrentIP .............................................................. 39 8.2.3 Implementation of Delete Trigger ............................................................ 40 8.2.4 Email Trigger ............................................................................................ 40 8.2.5 DDL Trigger ............................................................................................. 41 9 Software Interfaces ................................................................................................... 43 9.1 Sign In Page ...................................................................................................... 44 9.2 Post Query page ................................................................................................ 45 9.3 Generate Audit Report ...................................................................................... 46 9.4 Generate Email.................................................................................................. 47 9.5 View Email ....................................................................................................... 48 New Work  Chapter 3 Privileged Users  Chapter 8 T SQL for Database  Chapter 9 Software Interfaces docsity.com v Abstract Organizations have critical data about their employees and business transactions. They are very much conscious to protect data from unauthorized access. It is therefore need of the hour to introduce a security mechanism within the database system to ensure maximum security level. Auditing is the security mechanism that fulfills this need .it involves monitoring of DDL and DML commands. There are several methods available for auditing. Each of such methods has advantages or pitfalls. Major aspect is to keep in limits CAPEX and OPEX.In the preceding sections; analysis of various auditing capabilities is being discussed. On the basis of the literature survey as well as the discussion sessions with supervisors a design model of the software is presented that focus on major software functionalities. The software functionalities are related to user’s intrusions into the database system. In the end a sample database for testing software functionalities is being developed in SQL Server Management Studio. So for solution based on triggers is being focused. As it is an appropriate one for auditing user actions within a database. docsity.com i Time Schedule The Project plan is given as below for the project titled “Development of Database Auditing Infrastructure”. So for project is going as per specified in the schedule. docsity.com 2 Chapter 1: Case Study for Auditing Auditing is the process of scanning over past actions of database users and decides whether they are in conformance with official policies. In reality auditing is monitoring of queries send by a particular user. Databases are repositories of information that may come under privacy rules. All these information should be used only for intended purposes. Auditing helps to keep user activities clean and clear by monitoring them. The given below discussion has been taken from study of one paper. Paper discusses issue of “Data Access Accountability” and how effective auditing solutions can be implemented. Therefore only one reference is being provided at the end of this chapter. 1 Data Access Accountability Database security is always considered to be an area of interest not only for the owner of organization but also for database administrators and even common users. In the concern of data security following issues should be keep in mind.  Data should be changed in a specific defined way.  Data should be modified only by intended parties. There are two major problems in this concern  What keep away an authorized user from kept viewing data, one should not access?  Flaws that causes data to leak out to some user. 1.1 Auditing is Necessary In any organization auditing is implemented in some way. This is because one can’t say that it is specifically related to currently implemented RDBMS. The question arises that what keep a DBA to focus critically on the implementation of auditing within RDBMS system. Below are given answers to the question of “why auditing”  Enables monitoring of the user actions.  Restricts user to perform illegal actions. docsity.com 5 Chapter2: Native Auditing 2 Native Auditing Tools RDBMS products available by various vendors in today’s market provide native auditing capabilities. These tools also provide ability to make changes to structure and access to a particular database product. The tools can differ in the level of granularity provided for auditing. Below in table labeled (Table 2.1: RDMS Auditing Comparison) is provided a brief summary of popular RDBMS systems. RDBMS Product The auditing capabilities IBM DB2 Z/OS and IMS  IBM audit management expert (AME) stores its captured audit records in a DB2 table. Microsoft SQL Server  C2 auditing  Server side traces Oracle  Fine grained auditing Stores audit records in XML format.  This can also be done by adding an additional tool that is known to be as oracle audit vault. Sybase  Minimal native support  At an additional cost Sybase data auditing provides some other product from lumigent technology.(a third party product) Table 2. 1 RDBMS Auditing Comparison 2.1 Native Auditing Problems It should be kept in mind while discussing auditing that auditing is not similar to re-do or archive logs created by a particular RDBMS product. The common problems with native audit capabilities can be given as below  It is a common observation that native auditing tools add overhead to database server performance. docsity.com 6  It has also been reported by auditors that these tools don’t meet all the information required by them.  For any database management system the detection and access in real time is crucial and no such facility is being provided by native auditing tools.  Native auditing tools provide limited capability to effectively store audit records.  Possibility of increase in CAPEX and OPEX increases. However skilled auditors or Database administrators can tune these tools to manage them effectively and to save audit records in a separate file at different destination. By default native auditing is turned off when a database is installed on sever. According to industry analysts oracle provides the leading auditing capabilities. SQL server is lagged behind from Oracle in this scenario. The following example demonstrates the fact. In SQL server 2000, Microsoft introduces a new auditing mechanism, C2 auditing. This mechanism doesn’t provide the ability to use individual table tracing. So auditor needs to run server side traces. In native auditing tools, any command that specify DML or DDL command can be logged. Other than that any command by a user who turns off native auditing tools is also logged and recorded. 2.2 Third Party Auditing Solution More than two dozens of vendors provide third party solutions. Their major claim is that the overhead introduced by native auditing tools can be significantly drop down by using them. They also claim that they don’t require native auditing to be turned on. 2.2.1 Tradeoffs The three prime types of auditing solution provided by vendors are  Use of network based appliances that sniff packets on wire.  Software based solutions that are deployed with or without the use of agents.  Some combination of above. First approach that is the use of network based appliances tries to read packets on network as it travels to database server. However it is of no use for local access to server. docsity.com 7 Some activities can only be initiated by local access. If this case comes what would be the final solution. In such solution, company needs lot of appliances to meet the needs. The fact is shown by giving an example of a company with auditing solution (Figure2.1Auditing appliances). The Company also needs to configure those appliances. Where should be these appliances placed is also an important question? Figure 2. 1 Auditing appliances[6] Software agents and probes are typically deployed on database servers or application servers. Both of these servers are being accessed by RDBMS products. However this approach will increase overhead to around 5 %.The third solution that is the combination of two approaches usually gives better results. 2.2.2 Database Auditing Features As already stated there are a lot of database auditing vendors. These vendors claim for different auditing features. In the following discussion, major features provided by various vendors are discussed. Policy Creation This involves database administrators to define policy for auditing. However policy creation is not the ultimate solution as no product can provide complete set of rules that meets the need. This approach requires auditor to review policies from time to time. This also requires not logging changes by all users but only a set of users docsity.com 10 3.1 Challenge Regarding Privileged Users The main problem regarding the identification of privileged users is that who will define the boundaries among privileged and non privileged users? It is also not necessary that someone who is not privileged today can’t become privileged tomorrow. It is therefore necessary periodically recheck the status of various employees and then decide what to do and what not to do regarding their privileges. Care needs to be taken when defining privileged users. 3.1.1 Authorized Privileged Users The four major types users indicated above including system administrators, DBA, Auditors and security advisors have the maximum privileges. Tracking of their activities is a long and time consuming process. In some cases users can perform activities that don’t come under authorized activities. Actually the main point is that we can’t make a boundary on each user’s activities. 3.1.2 Authorized Users with Excessive Privileges The users that have excessive privileges are those special types of users that have promoted to some other designation within the same organization. They have two major privileges  Privileges due to their previous designation  Privileges newly granted to them 3.1.3 Unauthorized Accounts with Excessive Privileges As the name indicates, here the users are those that have used some illegal means to gain some authorities over the database engine. They may include two types of users  Authorized Users  Unauthorized Users 3.1.4 Conclusions If company and its security auditor are very much concerned regarding ethical use of database systems then following paradigms should be keep in mind docsity.com 11  Continuous monitoring of database breeching is the need of hour in every situation.  Updated security patches should be applied to security monitoring software  Special care should be taken to protect administrative and authoritative accounts. All of the above ensures optimum privileged users. It is a point to ponder that ultimate security is never possible within any RDBMS system, until is never used. The intruders are always there to seek for security breech. 3.2 Best Practices As stated before that security teams should always be circumspect for their system. Auditor plays role of arbiter in a DBMS. The best practices for judging the Privileged users are given as below  List of active databases should be made for the organization  List of privileged users for each and every database in an organization is maintained and upgraded as time proceeds by.  List of users with excessive privileges should be conserved  Scanning of excessive privileged users account, for modification and account deletion  Document the list of privileges that can be attributed to a particular user  Scan users and privileges to make a list of users and the privileges assigned to them  Make some policy and then choose some auditing mechanism. 3.2.1 Native Auditing Native auditing is a good approach to apply on a database. It seems enchanting but it has pitfalls in it. The main problem in this regard are given as below  It grasps almost all activities of users of database.  In fact questions of all forms like who, when what are taken in to account and are logged. docsity.com 12  This continuous monitoring of data consumes disk space and leaves the server in a problematic situation  Separation of duties is another problem. Separation of duties is just that “the person who needs to get audited should not be an auditor”.  Native auditing can allow a DBA to turnoff the auditing tool, to delete and modify audit logs.  Alerts are not generated in real time 3.2.2 Real Time Activity Monitoring An important way to analyze user’s action is to make policies and then make real time detection of user’s activities. This needs an insight into the users’ action and making an analysis of what is going on within an organization. Real time detection would ultimately require to not only monitor current actions but also application of latest patches to implemented system. Knowledge of latest threats should also be considered. [7]. 3.2.3 Insider Attacks A mechanism for logging database activities is necessary. The important aspect on this issue is that some mechanism for handling vulnerabilities of weak passwords should be employed. If the question arises that what is logging? This simply means to make a record of user actions. It is an important point that logging needs nothing to do with the database security. Log records are different than DAM which is database activity monitoring. If the database has security holes then  Logging won't be able to prevent someone to steal data.  Logging won't stop someone to exploit the vulnerabilities Logs are only valuable if they are frequently and carefully reviewed .The person who review logs should understand the difference between  Normal Access  Malicious activities Logging records has much more important for docsity.com 15 simply means a user centered approach should be employed from the very start, design phase. Currently to implement usability, interfaces have been made. They are made by keeping in mind user’s interaction with the software. Interfaces will be modified as software development progresses Up till now focuses on modules construction one by one. This has been achieved by implementing functionalities one by one and thereby integrating those functionalities with each other’s [9]. One change has been made in the requirements as specified in SRS submitted. Functionality of several DBA in an organization having similar privileges has been excluded because of following facts  One auditor can solve the whole problem as his main job is to monitor user activities.  Several DBA with equal privileges seems to be in practical  There is no limit to the rule “who will guard the guards” because the process never comes to an end. 4.1 Project Chronology The project timeline is given as below in figure (Figure 4.2: Project chronology). The chronology for a particular project specifies how project progresses throughout its course of development. It spans from the day when project was purposed to the day when it finally completes its cycle. It is very much useful to monitor changes that occur during course of development of project. On the other hand one can define mile stones. Achievements of these milestones help one to scroll through time available and progress of a particular project. docsity.com 16 Figure 4. 2 Project chronology [11] In this division of work, tasks duration has been estimated. They may get completed in less time or even in long duration as being specified. The reasons can be many on behalf of constraints like human constraints, resource availability constraints and so on. 4.2 Division of Work Division of work is described as below. This includes the intended work division throughout the whole course of development. It is different from time line as it specifies only what needs to done in three semester duration. Activity Semester Overlapped Activity Literature Survey and back ground research Semester 6 Progress Reporting SRS document and Project Plan Analysis of the infrastructure required and further survey Summer Vacations SQL language in detailed study Design phase + Modification in Design Semester 7 Interface Design Literature Survey + docsity.com 17 Implementation Software development Semester 8 Web application Development Integration of web application & software Interface Design Final Thesis Writing & Demonstration 4.3 Project Progress The progress of project in the sixth till the end of the seventh semester is given below. 4.3.1 6th Semester In the 6th semester initial project idea, project requirement specifications, and initial literature survey for understanding the basics of database auditing was being carried out. Two documents SRS (Software Requirement Specifications) and SPMP (Software Project Management plan) was submitted to the supervisor and the panel. 4.3.2 Summer Semester In the summer semester  Basics of SQL language was explored gaining some insight into advanced concepts.  Project literature survey was also being conducted. This was as specified in the initial project plan submitted in the sixth semester. 4.3.3 7th Semester Mid Following phases was being completed till the mid of 7 th semester.  Behavioral design phase  Analysis design phase  Process modeling  Initial database design and implementation for software testing. The midterm project progress report was submitted to the supervisor as well as to panel docsity.com 20 2005.In the remainder of the section major software requirements are listed. The design work completed up till now is also demonstrated. 5 Major Software Functionalities The major functional requirements of the intended module are given below. These functionalities are the main functionalities which are to be focused. 5.1 Authentication This functionality allow auditor to login to the tool after being login on to the database. This functionality requires extra privileges. This is because auditor is the person with the highest authority in RDBMS system. Auditor will also keep an eye on to the activities of the DBA. Login requires user name and passwords to authenticate auditor. After entering user name and password auditor will be able to use different functionalities of the case tool. Auditor may then logoff from tool. It is important to be noted that database login and login on to the case tool are two different concepts. 5.2 Users Query for Inspections This functionality allow auditor to post a query to the database. The query would be SQL based on a SQL Server database. It can be anything regarding  Common user daily activities  Any query that come under suspicious query.  Any query sent at some unusual time  After sending query the results of the query can also be seen. 5.3 Generate Auditing Report This functionality allows auditor to generate a report at any time. Case tool also generates report at the end of each month. The report covers aspects of the user as  Username  Users IP address docsity.com 21  Query sent by user  Name of the table on which operation was being performed  Old value of the record o Only in case of DML queries, this attribute can be included.  Name of operation performed  Time at which query was sent  Date of query sent 5.4 Suspicious Query Detection The case tool allow auditor to set some query to be suspicious. This means that whenever such query is performed on the database an alert is generated for the auditor. This alert shows all of the details as discussed under generate report heading. 5.5 Generation of Email Alerts for Suspicious Actions As soon as some suspicious query is being performed, an email is also sent to auditor showing all the details as discussed under heading of generate report. This email will inform auditor that something alarming has occurred and needs auditor to take keen interest in it and take actions accordingly 5.6 View Email Case tool provides auditor to view email sent to it after some suspicious query. So to view email is the functionality that is built in the case tool. View email functionality has been provided because auditor also has the ability to send email also. 5.7 Malicious Query at off Hours Case tool will also generate an email to the database auditor as soon as some query is being performed at some unusual timing for example at 2 AM. The email generation is triggered and the email covers all the aspects discussed under generate report heading. docsity.com 22 6 Process Modeling In the context of process modeling [18] flow charts have been constructed so far .Flow charts are the modeling techniques used for structured development. Unlike data flow diagrams flow charts are used to show a detailed description of business logics or rules. As the modeling approaches become advanced and more sophisticated, flow charts lose their importance. They still are useful for process modeling for depicting complex logics in a simple way. [12] 6.1 Flow Charts Symbols Below is given a table labeled (Table5.1 Flow chart symbol). This table visualizes the symbols and their meanings in the underlying diagrams. Name of Symbol Symbol Diagram Process Database Multi line Document Data Flow Table 5. 1 Flow Chart Symbols 6.2 Audit DB Flowchart In the subsequent figure (Figure 6.1 Flowchart of system), flowchart for intended software is shown at an abstract level. The software would be directly associated to database server. It will monitor all the activities occurring on a database server. Rules and docsity.com 25 Audit DB Post Query Email Reports Alerts U se r da ta Rep orts Alerts Email Level 0 DFD Figure 7. 1DFD symbols 7.1.1 Level 1 DFD The below diagram labeled (Figure 7.2: Level 1 DFD) demonstrates a level 1 DFD for the whole system. In this diagram the individual functionalities of the system are targeted. However still there is a single representation for the whole system. Here control panel display means GUI prompting for various actions [13]. The above diagram shows the point where the user login to the software interface. Users can logoff too. User can then use command buttons on the interface of the system to  Send a query to the connected database  View query results  Generate report  Send Email  View mail In case of any suspicious query an alert is generated to auditor. Auditor can then take necessary actions as per rules of the organization. docsity.com 26 Control Panel Display User Log in Log off interact with users User Login Sto p P roc ess ing Po ss ib le A ct io n Process PSW and Uid PSW Processing Display Messag e Display Messag e In va lid PS W V alid P S W Mail Message Control Panel Display Control Panel Display M es sa ge Message Configu re System Monitor Activitie s Send Query us er a ct iv iti es Query Processing Generat e Report Report Generation Control Panel Display Results Results Generate Message Generat e Alert Suspicious Query Mes sage Message Email Sending Em ail Sen din g Figure 7. 2 Level 1 DFD 7.1.2 Detailed DFD In the detailed DFD construction all the activities acting in the system are described with their detailed processes. In such DFD representations, each and every activity is described as combinations of different sub activities [13}. User Authentication Authentication is the major starting process when dealing with any security related software. All users should be authenticated. This is necessary to make a check on user actions as well as to make sure that no one is using others privileges. When talking about docsity.com 27 a RDBMS product, all users must be verified first. This authentication decides the privileges they have on database and related tools. The intended user of the intended software would be the database auditor. Auditor should be login to database server before logging in to tool. User name and passwords provided by him are verified. If user name and password matches, login service succeeds else auditor needs to try again as given in (Figure 7.3 User login). user login control panel displ user successful login to DB server tool login process username and psw unsucc essful login succe ss ful login user authentication login successful control panel display display login failed Figure 7. 3 User login Configure System In the diagram labeled (Figure 7.4 Configure system) the actions of the auditor are being targeted. Auditor after logging on to the system can then configure the whole system. This means the user can then set parameters to set the vulnerability of data. Here vulnerability of data means the data that is much critical from security point of view. Unusual timings can also be set which means the timings at which any query is not expected in normal situations. docsity.com 30 Send Alert Monitor Query Save Query Results Record User Activities Query Found to be suspicious Generat e Alerts DFD:Level 2 Suspicious Query Po st ed Q ue ry Figure 7. 7 Suspicious query Data Vulnerability Settings Auditor of the system has the choice to set some data and query to be suspicious depending upon the sensitivity of the data for the organization. If the query or data is set to be suspicious then any access to that data is considered important. The diagram is shown below labeled (Figure 7.8 Data vulnerability settings). Set data Suspici ous Set query Suspicio us Monito r data + Query Set Vulnera ble data Au dit So l C on nec ted Qu ery Se tti ng s Data Settings Query D ata Vulnerability Settings Figure 7. 8 Data vulnerability settings docsity.com 31 Note: The above diagrams have been created using tool smart Draw [14]. 7.2 Behavioral Modeling In the context of behavioral modeling activity diagrams have been constructed. They are being used in collaboration with state diagram and collaboration diagram. Activity diagrams common symbols used in the below modeling are given as below [15]. These diagrams show the workflow of the system activities. They are quite similar to state diagram as like activities of states of doing things. Parallel and sequential activities can easily be shown with the help of it. It is useful in those cases when a certain operation can be sub divided into several sub activities and one is interested in exploring these activities. This shows that how individual use cases unfold and how many use cases depend on other use cases. Similarly an activity diagram records the dependencies among activities. Although an activity diagram can be useful for modeling workflow business modeling is somewhat more than simply this. UML is being criticized by critics in this regard [15]. The common symbols are shown in (Table 5.3: Activity Level Diagram Symbol) Name of Symbols Symbols Notations Activity Start State End State` Data Flow Decision Table 5. 3 Activity Level Diagram Symbols 7.2.1 User Authentication The activity diagram shown with tag (Figure 7.9: User login) the process of user login. The general user of the intended software product would be auditor. Auditor has been assigned a user name and passwords. For successful authentication, auditor provides docsity.com 32 correct user name and passwords. If the login fails, one won’t be able to carry on one’s work. Login Successful Login Login Failure Follow Instructions Start Activity Yes No End state Carry on Work Try Again Figure 7. 9 User Login 7.2.2 Processing Query This activity diagram labeled (Figure7.10Processing query) shows the actions performed by auditor after successful login. After login to the tool, auditor can  Send query  Generate report  View report  Set data vulnerability  Send and receive email Successful Login Send Query View Reports Receive Email Get Results Analyze User Check Email Generate Alert Decision Saving Results Generate Report Do Nothing NewActivity 11 Activity Start Do nothing Save Results Query Sending Figure 7. 10 Processing query docsity.com 35 o Joining date of some specific member in a club.  gender  membertype o Member type can be like open, junior, senior, social. The diagram for this table is given as specified in SQL Server management studio express. Diagram indicates various columns of table and their respective data types. Figure 8. 1Member table dbo The table view after record insertion has been given as below. The records are just arbitrary at this moment. They have been inserted, as it will be thereafter beneficial to make test on them. Figure 8. 2 Records in member table docsity.com 36 8.1.2 Tour Table This table has following columns  Membertype o This table comes up due to primary key foreign key relationship between member table and tour table.  Tourname o This indicates name of tour.  Fee o Each type of member has different fee to pay. TSQL for this table is given in Appendix Below is given a view labeled (Figure 8.3 Records in tour table) of record inserted in this table. Figure 8. 3 Records in tour table 8.1.3 Entry Table The table has following columns in it. The description is given as below  tourId o Specific id of tour that has been arranged  memberId o Comes from member table as a result of primary key foreign key relationship.  Touryear docsity.com 37 o Year in which tour was arranged.  Tourmonth o Month of year in which tour was arranged. TSQL for this table is given in Appendix B.The Diagram for this table is given as below (figure 8.4 tour table) as depicted by SQL Server Management Studio Express 2005. Figure 8. 4 Tour table Below is given a view of records inserted in this table labeled figure ( Figure 8.5 Records in dbo.entry)The inserted records are arbitrary and are included just for sake of testing software later on. Figure 8. 5 Records in dbo.entry docsity.com 40 Problems Encountered There were problems in implementation. As firstly IP address was not appearing in readable format. So the focus returned on how to implement it in such a way that it appears in a readable format. For this an exploration was made on system stored procedures. Here one was sys.dm_exec_connections. This somewhat solves problem. As it determines and return Ip address of user on the basis of user identity on network. 8.2.3 Implementation of Delete Trigger This trigger was implemented to monitor delete queries performed by database users. The implementation mechanism adapted was to use logical table of SQL Server. Table that was used is named as “deleted”. So any delete action performed by a database user was recorded to it. This helps in maintaining log of database user delete queries. For this purpose to achieve a separate delete_audit table is constructed. Here three columns are added to enhance clarity for auditor and to achieve purpose of monitoring. The added columns are  Name the operations of database  Time at which the operation has been performed  IP address of user performing operation As soon as a delete action is performed on database, a new entry or record appears in this table. The values inserted in this new record has been taken from  Delete action  Date and time of record deletion , calculated from getdate() function  Ip Address of user , calculated from GetCurrentIP() function  Replica columns are filled by making use of local variables in trigger. Records inserted are assigned newly defined variables and hence then put in this replica table. Problems Encountered are same as for insertion table. 8.2.4 Email Trigger This trigger is used to send an email to auditor. Whenever an activity is performed by user suspiciously. However currently it is in development phase. Several problems are being encountered in developing this. docsity.com 41 First problem is that how to specify no. of peoples to send email. A stored procedure that is being used up till now in implementation just provides flexibility to one user. Second problem is that is for any new DML and DDl action should be send as an email to concerned auditor. This definitely adds penalty to overall system performance. The question is how to avoid this problem? We are working on these issues. Our major focus how to decide what should be emailed and what should not be emailed. Currently implemented code is provided in Appendix 2 8.2.5 DDL Trigger Implementing DDL triggers is a new feature in SQL Server 2005. DDL actions include  Create table  Alter table  Create schema  Alter schema  Drop table  Drop schema All these commands can be implemented both in one trigger as well as in separate trigger. Currently our focus is to implement this in one trigger. The trigger that has been currently implemented executes successfully but it is not providing correct results. For implementation of DDL triggers a separate table is being used. This table has several columns that give information like  Who perfume audit action  What was IP address from which query was sent?  What was the SQL Statement that was used?  What was time of operation? Currently implemented code requires @data variable that will store results in XML format. Details of action are monitored and used using EventData() function.This function on demand ca provides necessary information for Database level operations. docsity.com 42 Problems Encountered As our main focus to develop a Case Tool, so it requires to be generic. We are facing difficulties of how to implement generic trigger. This means that as triggers are table based so what methodology should be employed to make triggers generic Second major problem is what if a server has more than one database. The server if managed by just one auditor then it will also provide difficulties. Software is intended to be implemented on database level. This means for each database on server, separate installation would be needed. We are currently focusing upon this methodology. However if time allows us, server based implementation would be then considered. However currently it’s not in our focus. docsity.com 45  One button named “submit”. Figure 9. 3 Sign in page layout 9.2 Post Query page Datbase operations are performed through queries written in TSQL. users themselves can write down a query .Users can also call some operations that ultimately returns some results from the database.Auditor has some extra privilges as compared to that of a common user.They not only can perfom common database operations and can also perform activities requiring extra privileges.The result of this operation would be viewed in the explorer window . For perfoming this opertion auditor would just write up some query in post query label and results would be returned in solution explorer of database. Given below snapshot labelled (figure 9.4 Post query page layout)indicates a Post Qery Page. It has just three controls specific to this page. Controls are  One label directing user to input query  Textbox to take query as input.  A button named as “post” for submitting querties. docsity.com 46 Figure 9. 4 Post query page layout 9.3 Generate Audit Report Report generation is an important activity of . Report generation would be on regular basis as well on demand basis.All these options will be dependent on auditor choice.Auditor can instruct case tool to generate a report based on user activities. The report takes data from the audit logs stored by case tool.The intended format for report generation is in XML format. However this activity has not been implemented up till now. The snapshot of this page labelled (figure 9.5 Generate audit report) indicates two controls. One is a label control indicating that by clicking on button generate this page would return a report in XML format. docsity.com 47 Figure 9. 5 Generate audit report 9.4 Generate Email Email facility would be provided on suspicious actions only.Generation of Email after an unusual activity has been performed would be activity of case tool. By clicking on Input button labelled View , this page will navigate to a point and show the report generated.the snapshot given below labelled (Figure 9.6 Generate email) gives the respective page. docsity.com 50 Appendix A Word Definition Database A collection of related data stored together with controlled redundancy according to a scheme to serve one or more applications.[9] DBA Database administrator is the person who manages a database.[9] Authentication It is the process by which one verifies that one is who ones claimed. A type of interface that enables users to communicate with a program by manipulating graphical features, rather than by entering commands.[9] Internet Protocol An Internet address that is a unique number consisting of four parts separated by dots, sometimes called a dotted quad (for example, 123.45.67.8). Every Internet computer has an IP number.[9] Sniffer A program or device that monitors data traveling over the network CAPEX Money spent to acquire or upgrade physical asset such as buildings and machinery.[5] OPEX OPEX is an on-going cost for running a product, business, or system[4] Work Breakdown Structure (WBS) The planning framework; project decomposition into units of work from which cost, artifacts, and activities can be allocated and tracked.[9] Analysis The part of the software development process whose primary purpose is to formulate a model of the problem domain. Analysis focuses on what to do. [9] Analysis and Design Activities during which strategic and tactical decisions are made to meet the required functional and quality docsity.com 51 requirements of a system.[9] DDL Data Definition Language. It includes statements like Drop, Alter, create etc. DML Data modification language of TSQL. The language enable user to access or manipulate data as organized by appropriate data model. It includes insert, update and delete statements. Triggers These are special kind of stored procedures. sys.dm_exec_connections Returns information about the connections established to this instance of SQL Server and the details of each connection. docsity.com 52 Appendix B Table of Database TSQL for member table is given as below Create table member( memberId int primary key, lastName char(20), firstName char(20), phone char(20), handicap int, joinDate DateTIME , gender char(1), membertype char(20) foreign key references tourtype) TSQL for type table is given as below --Type Table tournamnet table create table tourtype (membertype char(20) primary key, fee int, tourName char(20) ) TSQL for entry table is given as below --entry Table create table entry (tourId int primary Key, memberId int foreign key references member, touryear int , tourmonth varchar(20), entry int ) docsity.com 55 memberId int primary key, lastName char(20), firstName char(20), phone char(20), handicap int, joinDate DateTIME , gender char(1), membertype char(20) foreign key references tourtype, audit_action varchar(100), audit_timestamp datetime, audit_IPAddress varchar(255)) - Capturing DDL Changes -- Databas Level Triggers -- Creating a Table to record database events CREATE TABLE ddl_log ( time_stamp datetime, DB_User nvarchar(100), Event nvarchar(100), TSQL nvarchar(2000), ip_address varchar(255)); -- Creating a DDL Trigger create trigger database_log on database for DDL_DATABASE_LEVEL_EVENTS as -- local variable that will later be used for storing results , it is an XML variable declare @data xml -- get details of executed statements , Referenced from MSDN set @data= EventData() insert ddl_log(op_time,op_name,db_user,T_SQL,ip_address) values docsity.com 56 ( getdate(), CONVERT(nvarchar(100), CURRENT_USER), @data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'), @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(2000)'), dbo.GetCurrentIP()) ; --Writing Email Trigger create trigger Email_Gen on member for update as declare @CustomerID varchar(10) declare @firstName char(20) declare @lastName char(20) declare phone char(20) declare handicap int declare joinDate datetime declare gender char(1) -- holds the body of the email declare @body varchar(2000) -- holds the new details declare @firstNew char(20) declare @lastNew char(20) docsity.com 57 -- gets the previous member first name that was deleted SELECT @memberId = memberId, @firstName = d.firstName @lastName=d.lastName @ FROM deleted d -- gets the new customer first name SELECT @firstNew = firstName FROM inserted -- gets the previous member first name that was deleted SELECT @memberId = memberId, @firstName = d.firstName FROM deleted d -- gets the new customer first name SELECT @firstNew = firstName FROM inserted docsity.com 60 [17] Jeffrey A.Hoffer, Mary B. Prescott, Fred R.McFadden, “Modern Database Management”, Pearson Education, India, 287-318 [18] Rob Pooley and Perdita Stervens, , Using UML Software Engineering with Objects and Components, Addison-Wisley,England146-148, 2001 [19] IEEE Recommended Practice for Software Requirements Specification. © 1998 IEEE docsity.com docsity.com 2 docsity.com