A prototype model for data warehouse security based on metadata

1、m-view: a prototype model ffor data warehouse security based oon metadatan. katic1 g. quirchmayr2 j. schiefer1 m. stolba1 a m. tjoa11institute of software technology (e188)vienna university of technologyresselgasse 3/188, a-1040 viennaaustriakatic, stolba, js, tjoa ifs.tuwien.ac.at fehler! textmarke

2、 nicht definiert.2institute of applied computer science and information systemsuniversity of viennaliebiggasse 4, a-1010 viennaaustriafehler! textmarke nicht definiert.- 15 -abstractthe aim of this paper is to give an overview of security relevant aspects of existing olap/data warehouse solutions, a

3、n area which has seen rather little interest from product developers and is only beginning to be discussed in the research community. following this description of the current situation, a metadata driven approach implemented as part of the www-eis-dwh project is presented in detail. the prototype f

4、ocuses on the technical realisation and is intended not to be open for use in different security policies.1. 1introductionabstractthe aim of this paper is to give an overview of security relevant aspects of existing olap/data warehouse solutions, an area which has seen rather little interest from pr

5、oduct developers and is only beginning to be discussed in the research community. following this description of the current situation, a metadata driven approach implemented as part of the www-eis-dwh project is presented in detail. the prototype focuses on the technical realisationrealisation and i

6、s intended not to be open for use in different security policies.2. 1introductionfor a wide range of companies, in both private and public sectors, competitiveness and effectiveness depend on the quality of decision making; so it is of no surprise that many are looking to improve the quality of thei

7、r decisions by learning from past business transactions and decisions. accumulated data represents a priceless business asset.providing analysts with wide access to the mass of corporate data in a data warehouse requires the organisationorganisation and integration of heterogeneous data in a heterog

8、eneous environment. furthermore the production of derived aggregated data of the data warehouse requires the continuous maintenance of integrity.these requirements imply several security issues to ensure that it is just authorised users who benefit from relevant data and that no unauthorised sources

9、 are used. under many jurisdictions it is illegal to merge personal data unless anonymity can be ensured and especially in europe stricter legislation has been proposed and is under review based on oecd and ec recommendations.the aim of this paper is to discuss requirements and impacts on the select

10、ion of an adequate security model for a data warehouse environment. this security model should support such features as controlled access to individual data items, selective encryption and patented security processes. for the right choice of the security model it is important to pay attention to the

11、 metadata of the data warehouse because it containswarehouse. they contain security information such as access rules, classifications of security objects or clearances of security subjects.this paper is composed of two parts: a theoretical part which deals with security in data warehouses in general

12、 and part 2, a description of an implementation of a security model prototype for a data warehouse environment based on metadata.3. 2data warehouse & security a data warehouse is a collection of integrated databases designed to support managerial decision-making and problem-solving functions. it con

13、tains both highly detailed and summarised historical data relating to various categories, subjects, or areas 4. all units of data are relevant to appropriate time horizons. the data warehouse is an integral part of the enterprise-wide decision support system and does not ordinarily involve data upda

14、ting. it empowers end-users to perform data access and analysis. it also gives an organisationorganisation certain competitive advantages, such as fostering a culture of information sharing, enabling employees to effectively and efficiently solve dynamic organisational problems, minimising operating

15、 costs and maximising revenue, attracting and maintaining market shares, and minimising the impact of employee turnovers. the security requirements of the data warehouse environment are not unlikesimilar to those of other distributed computing systems 3. thus, having an internal control mechanism to

16、 assureensure the confidentiality, integrity and availability of data in a distributed environment is of paramount importance 2. confidentiality denotes the protection of information from unauthorised disclosure either by direct retrieval or by indirect logical inference. integrity requires data to

17、be protected from malicious or accidental modification, including the insertion of false data, the contamination of data, and the destruction of data. availability is the characteristic that ensures data being available to authorised users when they need them. availability is closely related to inte

18、grity. it includes denial of service of a system, i. e. a system is not functioning in accordance with its intended purpose 2.3.1. 2.1security restrictionsa data warehouse by nature is an open, accessible system. the aim of a data warehouse generally is to make large amounts of data easily accessibl

19、e to users, thereby enabling them to extract information about the business as a whole. any security restrictions can be seen as obstacles to that goal, and they become constraints on the design of the warehouse.there may be sound business reasons for any security restrictions applied to the data wa

20、rehouse, but it is worth noting that they may lead to a potential loss of information. if analysts have restricted access to data in the data warehouse it may be impossible for them to get a complete picture of the trends within the analysed area. checking security restrictions will of course have i

21、ts price by effectingaffecting the performance of the data warehouse environment, because further security checks require additional cpu cycles and time to perform.3.2. 2.2security requirementssecurity requirements describe all security conditions that have to be considered in the data warehouse env

22、ironment.it is important to determine in an early stage any security requirements that will be enforced in the data warehouse, because they can seriously impair the organisation and design of the warehouse. it is very difficult to add security restrictions after a data warehouse has gone live. so it

23、 is important to capture the ultimate security requirements at the beginning and make them part of the system design. the first step for the definition of security requirements is to classify the security objects and security subjects of the data warehouse environment. security objects can be classi

24、fied in different ways. which solution is suitable depends on the security level which should be achieved. qualified classifications would be classification by sensitivity (public, confidential, top secret) or according to job functions (accounting data, personnel data). as with security objects, th

25、ere is a number of ways in which security subjects can be classified. we can follow a top-down company view, with users classified by department, section, group, and so on. another possible classification is role based, with people grouped across departments based on their role. this approach would

26、classify all analysts as one group, irrespective of their department. if each department genuinely accesses different data, it is probably better to design the security access for each department separately. it might even be necessary to set up security at the individual level if every analyst has c

27、ompletely different requirements. in case it may be worth considering the use of departmental data marts. the data marts can be separated from the data warehouse, and the security restrictions can be enforced separately on each data mart . 2.2.1legal requirements. 3.2.2. it is vital to consid

28、er all legal requirements on the data being stored in the data warehouse. if individual customer data areis being held, such as account details in a banking data warehouse, it may be required by law to enforce certain restrictions. in this context the following issues are to be clarified: which arra

29、ngements have to be made for being allowed to hold legally sensitive data? which data areis subjected to legal restrictions? which separate handling does this data require concerning storage, access and maintenance? which analyses may be performed on this data? if data held online is used for trend

30、analysis, and is therefore held in summarised rather than detailed form, do any legal restrictions apply? which data may be used only for the companies for the companies own purposes and which data may be passed on third parties? can the analysis of legally sensitive data be limited in a way that no

31、 legal restrictions apply? these issues explain why the administrator of a data warehouse must have special know-how about the legal and business field in order to identify legally sensitive data and to accordingly limit the access to this data.3.2.3. 2.2.2audit requirements. s3.2.4. resulting audit

32、 information is the basis for further reviews and examinations in order to test the adequacy of system controls and to recommend any changes in the security policy.auditing is a security feature that is often mandated by organisation. given the high volume of data involved in a data warehouse, audit

33、ing can cause an extremely heavy overhead on the system. to make up for this overhead more hardware will be needed. basically the following activities are interesting for auditing: connections disconnections access to data change of data deletion of datafor each of these activities it may be necessa

34、ry to audit success, failure or both. for security reasons the auditing of failures can be particularly important, since it can highlight any attempted unauthorised or fraudulent access.if data access is to be audited, it has to be established whether each access is to be audited separately, or whet

35、her it is sufficient to audit the fact that a user has accessed specific tables during a session. this has impacts on the audit information that needs to be held and implicitly avoids both space and i/o overhead.if data changes are being audited, it has to be determined whether it is sufficient to a

36、udit the fact that a change occurred, or whether it is required to capture the actual change that was made. 3.2.5. 2.2.3network requirements. 3.2.6. network requirements are a further important part of security requirements. for the transfer of data from the source system (usually an operational sys

37、tem) into a data warehouse they must mostly be transmitted over a network. for such a data transfer precautions must be taken, in order to retain the confidentiality and integrity of the data. it must be clarified and proofedproved whether data have to be encoded before transmission into the data wa

38、rehouse to prevent a manipulation during the transfer. if the data areis transmitted for example over a public network, a secure connection between source system and data warehouse has to be constructed to transmit data in encoded form. the expenditure for data encryption and decryption can be very

39、high regarding processing speed and delay. particularly with large quantities of data this factor can affect the system performance of the source system as well as of the data warehouse system negatively.a further substantial fact is the reliability of the data communication. it should be guaranteed

40、 that data areis transferred error free from the source system into the data warehouse. connection interruptions should be prevented as far as possible, since incomplete transfers threaten the integrity of data in the data warehouse. therefore measures must be taken, which make possible a complete r

41、ollback of the entire transfer process in case of an incomplete data transmission.2.2.4encryption requirements 3.2.7. encrypting sensitive data in the data warehouse environment can be done at the table, column, or row level. encrypting columns of a table containing sensitive data is the most common

42、 and straightforward approach. few examples of columns that are usually encrypted include social security numbers, salaries, birth dates, performance evaluation ratings, confidential bank information, and credit card numbers. locating individual records in a table through a standard search command w

43、ill be exceedingly difficult if any of the encrypted columns serve as keys to the table.encrypting only selected rows of data is not commonly used, but can be useful in some unique cases. for instance, a single encryption algorithm can be used to encrypt the age of some employees who insist on non-d

44、isclosure of their age for privacy reasons. multiple encryption algorithms can also be used to encrypt rows of data reflecting sensitive transactions for different sites so that geographically distributed users of the same data warehouse can only view/search transactions (rows) related to their resp

45、ective sites. if not carefully planned, mixing separate rows of encrypted and unencrypted data and managing multiple encryption algorithms in the same data warehouse environment can introduce chaos, including flawed search results.encrypting a table (all columns/rows) is very rarely used because it

46、essentially renders the data useless in the data warehouse environment. the procedures required to decrypt the encrypted keys before accessing the records in a useful format are very cumbersome and cost-prohibitive.the encryption algorithm selected for the data warehouse environment should be able t

47、o preserve field type and field length characteristics. it should also work co-operatively with the access and analysis software package in the data warehouse environment. specifically, the data decryption sequence must be executed before it reaches the software package handling the standard query.

48、otherwise, the package could prevent decryption of the encrypted data - rendering the data useless.performing data encryption and decryption on the data warehouse server consumes significant cpu processing cycles. this results in excessive overhead costs and degraded system performance. also, perfor

49、ming decryption on the data warehouse server before transmitting the decrypted data to the client (end-users workstation) exposes the data to unauthorised access during the transmission. these problems can be minimised if the encryption and decryption functions are effectively deployed to the workst

50、ation level with greater cpu cycles available for processing. in addition, improperly used encryption (e.g. weak encryption algorithm) can give users a false sense of security. encrypted data in the data warehouse must be decrypted before the standard query operations can be performed. this increase

51、s the time to process a query which can irritate the end-users and force them to be belligerent toward encryption mechanism. 3.2.8. 2.2.5authorisations on data items, characteristic numbers, dimensions and functions3.2.9. the authorisation concept of the data warehouse should contain the following s

52、ecurity objects: data items: data items are data of the data warehouse to which a user can have access. data items are thus surface elements, which can be used for either the simple browsing or for further analysis. characteristic numbers: characteristic numbers are summarised, aggregated or derived

53、 numbers of the data in the data warehouse. a user may see or not see a certain characteristic number, according to his access authorisation. dimensions: there necessarily is an authorisation on dimensions, best in the form of sub-trees of dimensions. the manager of a certain head department should

54、be able to see the data which areis assigned to its head department in the organisation hierarchy. from the sub-trees, for which a user has the authorisation to access, it can be derived, for which subset of values of a certain characteristic number he is access authorised; that are all combinations

55、 of dimension items, for which he (she) has an authorisation 5. functions: a data warehouse environment provides functions for the manipulation of its data. however, certain functions are reserved for certain users, e.g. update functions are reserved for administrators. 3.2.10. 2.2.6increasing comfo

56、rtability of use through access authorisation. 3.2.11. the primary goal of security restrictions and access authorisation in data warehouses is the prevention of unauthorised data accesses.disclosure of protected data.however, authorisations in data warehouses have in contrast to operational systems

57、 a second important function: users should and want to see primarymainly only “their“ (relevant) data of the information system, which areis based on a data warehouse, since they then penetrate more directly to the information important for their daily work. the work with the data warehouse system t

58、hus becomes more comfortable by the fact that only data areis offered, which areis important for a certain user.3.3. 2.3factors influencing securitythe security in a data warehouse is affected by technical and non-technical factors that have to be taken into account by the decision of adequate security measures. these influence factors mainly describe the vulnerabilities associated with the data warehouse environment 3. they have to be identified and documented. in the following some common technical and non-technical vulnerabilities of data warehouse are introduced.