Chapter 12. Database SecurityTable of contents Objectives Introduction The scope of database security– Overview– Threats to the database– Principles of database security Security models– Access control– Authentication and authorisation Authentication Authorisation– Access philosophies and management Database security issues– Access to key fields– Access to surrogate information– Problems with data extraction– Access control in SQL– Discretionary security in SQL– Schema level– Authentication Table level– SQL system tables– Mandatory security in SQL– Data protection Computer misuse Security plan Authentication and authorisation schematic Authentication and authorisation Access control activities– Overview– The problem– Activity 1 – Creating the database schema– Activity 2 – Populating the database– Activity 3 – Analysing the problem– Activity 4 – Executing the security script (if you have a DBMS thatpermits this)– Activity 5 – Testing the access control (if you have a DBMS thatpermits this)– Activity 6 – Conclusion– Activity 7 – Postscript1

ObjectivesAt the end of this chapter you should be able to: Understand and explain the place of database security in the context ofsecurity analysis and management. Understand, explain and apply the security concepts relevant to databasesystems. Understand, identify and find solutions to security problems in databasesystems. Understand the basic language of security mechanisms as applied todatabase systems. Analyse access control requirements and perform fairly simple implementations using SQL. Appreciate the limitations of security subsystems.IntroductionIn parallel with this chapter, you should read Chapter 19 of Thomas Connollyand Carolyn Begg, “Database Systems A Practical Approach to Design, Implementation, and Management”, (5th edn.).Security is a large subject and one that, because it touches every activity ofan information system, appears everywhere. In the main text you will startwith a thumbnail introduction to security, while the extension reading containsreferences for you to pursue when you wish.In earlier chapters in this module you have met concepts and techniques whichcan be regarded as security measures. For example, the process of recovery,whether from partial or total failure, should be considered as having a securitydimension. Nearly all the work on concurrency (see chapter 13) is directed atanother aspect of security. Again, a thumbnail introduction is given.The main work you do in this chapter, however, is directed to database securityrather than security in general, and to the principles of security theory andpractice as they relate to database security. These are technical aspects ofsecurity rather than the big picture.The chapter is organised into two parts. The first part covers security principles and models itself in two parts moving from the softer principles (settingthe universe of discourse) through to some specific technical issues of databasesecurity. The second part is about logical access control in SQL databases.The major practical area you will cover is the area of access control. After adiscussion of the principles, you will quickly be getting into some detail of access2

control in SQL databases. Extension reading, both textbooks and websites, isgiven for you to pursue the detail further.What is not covered in this chapter, but is covered elsewhere in the module, arethe subjects of database administration, transaction recovery and catastropherecovery. The first of these is directly related to management controls on operation and development. The second is directly related to database integrity andconsistency, thus being largely an internal matter. The third is easier to followas an extension of the first and second. But all three are security based.The scope of database securityOverviewAll systems have ASSETS and security is about protecting assets. The firstthing, then, is to know your assets and their value. In this chapter, concentrateon database objects (tables, views, rows), access to them, and the overall systemthat manages them. Note that not all data is sensitive, so not all requires greateffort at protection. All assets are under threat.The second thing to know is what THREATs are putting your assets at risk.These include things such as power failure and employee fraud. Note thatthreats are partly hypothetical, always changing and always imperfectly known.Security activity is directed at protecting the system from perceived threats.If a threat is potential, you must allow for it to become an actuality. Whenit becomes actual there is an IMPACT. Impact you can consider and plan for.But in the worst case, there will be a LOSS. Security activity here is directedat minimising the loss and recovering the database to minimise the loss as wellas further protecting from the same or similar threats.3

An outlined development mechanism is:1. Document assets (what they are, what their value is).2. Identify treats (what they are, how likely they are, what the impact is ifthey occur).3. Associate threats with each asset.4. Design mechanisms to protect each asset appropriate to its value and thecost of its protection, to detect a security breach against each asset, tominimise the losses incurred and to recover normal operation.Threats to the databaseYou will build your security skills from two directions. One is from the appreciation and awareness of changing threats, and the other from the technicalremedies to them. Threats include: Unauthorised modification: Changing data values for reasons of sabotage,crime or ignorance which may be enabled by inadequate security mechanisms, or sharing of passwords or password guessing, for example. Unauthorised disclosure: When information that should not have beendisclosed has been disclosed. A general issue of crucial importance, whichcan be accidental or deliberate.4

Loss of availability: Sometimes called denial of service. When the databaseis not available it incurs a loss (otherwise life is better without the system!). So any threat that gives rise to time offline, even to check whethersomething has occurred, is to be avoided.The rest of this section is an overview of the categories of specific regulatorythreats to database systems. Commercial sensitivity: Most financial losses through fraud arise fromemployees. Access controls provide both protection against criminal actsand evidence of attempts (successful or otherwise) to carry out acts detrimental to the organisation, whether fraud, extraction of sensitive data orloss of availability. Personal privacy and data protection: Internationally, personal datais normally subject to legislative controls. Personal data is data aboutan identifiable individual. Often the individual has to be alive but themethod of identification is not prescribed. So a postal code for a homemay in some cases identify an individual, if only one person is living atan address with the postal code. Such data needs careful handling andcontrol.For more information see Data Protection later in the chapter. The issuesare too extensive to be discussed here but the implications should be noted.Personal data needs to be identified as such. Controls must exist on theuse of that data (which may restrict ad-hoc queries). Audit trails of allaccess and disclosure of the information need to be retained as evidence. Computer misuse: There is also generally legislation on the misuse ofcomputers. Misuse includes the violation of access controls and attemptsto cause damage by changing the database state or introducing wormsand viruses to interfere with proper operation. These offences are oftenextraditable. So an unauthorised access in Hong Kong using computersin France to access databases in Germany which refer to databases inAmerica could lead to extradition to France or Germany or the USA. Audit requirements: These are operational constraints built around theneed to know who did what, who tried to do what, and where and wheneverything happened. They involve the detection of events (includingCONNECT and GRANT transactions), providing evidence for detection,assurance as well as either defence or prosecution. There are issues relatedto computer-generated evidence not covered here.In considerations of logical access to the database, it is easy to lose sight ofthe fact that all system access imposes risks. If there is access to operatingsystem utilities, it becomes possible to access the disk storage directly andcopy or damage the whole database or its components. A full considerationhas to take all such access into account. Most analysts would be looking tominimise communications (direct, network and telecommunications) and isolate5

the system from unnecessary threats. It is also likely that encryption would beused both on the data and the schema. Encryption is the process of convertingtext and data into a form that can only be read by the recipient of that data ortext, who has to know how to convert it back to a clear message.You will find it easier to consider security and auditing as issues separate fromthe main database functions, however they are implemented. Visualise thesecurity server and audit servers as separate functional modules.Principles of database securityTo structure thoughts on security, you need a model of security. These come invarious forms that depend on roles, degree of detail and purpose. The majorcategories are areas of interest (threats, impact and loss) as well as the actionsinvolved in dealing with them.Security risks are to be seen in terms of the loss of assets. These assets include: Hardware Software Data Data quality Credibility Availability Business benefitHere we are primarily concerned with threats to the data and data quality but,of course, a threat to one asset has consequential impact on other assets. What isalways important is that you are very clear on just what asset needs protection.So as a summary:You need to accept that security can never be perfect. There always remains anelement of risk, so arrangements must be made to deal with the worst eventuality- which means steps to minimise impact and recover effectively from loss ordamage to assets. Points to bear in mind:6

1. Appropriate security - you do not want to spend more on security thanthe asset is worth.2. You do not want security measures to interfere unnecessarily with theproper functioning of the system.Security modelsA security model establishes the external criteria for the examination of securityissues in general, and provides the context for database considerations, including implementation and operation. Specific DBMSs have their own securitymodels which are highly important in systems design and operation. Refer tothe SeaView model for an example.You will realise that security models explain the features available in the DBMSwhich need to be used to develop and operate the actual security systems. Theyembody concepts, implement policies and provide servers for such functions.Any faults in the security model will translate either into insecure operation orclumsy systems.Access controlThe purpose of access control must always be clear. Access control is expensivein terms of analysis, design and operational costs. It is applied to known situations, to known standards, to achieve known purposes. Do not apply controlswithout all the above knowledge. Control always has to be appropriate to thesituation. The main issues are introduced below.Authentication and authorisationWe are all familiar as users with the log-in requirement of most systems. Accessto IT resources generally requires a log-in process that is trusted to be secure.This topic is about access to database management systems, and is an overviewof the process from the DBA perspective. Most of what follows is directlyabout Relational client-server systems. Other system models differ to a greateror lesser extent, though the underlying principles remain true.For a simple schematic, see Authorisation and Authentication Schematic.Among the main principles for database systems are authentication and authorisation.Authentication7

The client has to establish the identity of the server and the server has to establish the identity of the client. This is done often by means of shared secrets(either a password/user-id combination, or shared biographic and/or biometric data). It can also be achieved by a system of higher authority which haspreviously established authentication. In client-server systems where data (notnecessarily the database) is distributed, the authentication may be acceptablefrom a peer system. Note that authentication may be transmissible from systemto system.The result, as far as the DBMS is concerned, is an authorisation-identifier. Authentication does not give any privileges for particular tasks. It only establishesthat the DBMS trusts that the user is who he/she claimed to be and that theuser trusts that the DBMS is also the intended system.Authentication is a prerequisite for authorisation.AuthorisationAuthorisation relates to the permissions granted to an authorised user to carryout particular transactions, and hence to change the state of the database (writeitem transactions) and/or receive data from the database (read-item transactions). The result of authorisation, which needs to be on a transactional basis,is a vector: Authorisation (item, auth-id, operation). A vector is a sequence ofdata values at a known location in the system.How this is put into effect is down to the DBMS functionality. At a logical level,the system structure needs an authorisation server, which needs to co-operatewith an auditing server. There is an issue of server-to-server security and aproblem with amplification as the authorisation is transmitted from system tosystem. Amplification here means that the security issues become larger as alarger number of DBMS servers are involved in the transaction.Audit requirements are frequently implemented poorly. To be safe, you needto log all accesses and log all authorisation details with transaction identifiers.There is a need to audit regularly and maintain an audit trail, often for a longperiod.Access philosophies and managementDiscretionary control is where specific privileges are assigned on the basis ofspecific assets, which authorised users are allowed to use in a particular way.The security DBMS has to construct an access matrix including objects likerelations, records, views and operations for each user - each entry separatingcreate, read, insert and update privileges. This matrix becomes very intricateas authorisations will vary from object to object. The matrix can also becomevery large, hence its implementation frequently requires the kinds of physical8

implementation associated with sparse matrices. It may not be possible to storethe matrix in the computer’s main memory.At its simplest, the matrix can be viewed as a two-dimensional table:When you read a little more on this subject, you will find several other rightsthat also need to be recorded, notably the owners’ rights and the grant right.Mandatory control is authorisation by level or role. A typical mandatory schemeis the four-level government classification of open, secret, most secret and topsecret. The related concept is to apply security controls not to individuals butto roles - so the pay clerk has privileges because of the job role and not becauseof personal factors.The database implication is that each data item is assigned a classification forread, create, update and delete (or a subset of these), with a similar classificationattached to each authorised user. An algorithm will allow access to objects onthe basis of less than or equal to the assigned level of clearance - so a user withclearance level 3 to read items will also have access to items of level 0, 1 and 2.In principle, a much simpler scheme.The Bell-LaPadula model (2005) defines a mandatory scheme which is widelyquoted: A subject (whether user, account or program) is forbidden to read anobject (relation, tuple or view) unless the security classification of thesubject is greater or equal to that of the object. A subject is forbidden to write an object unless the security classificationof the subject is less than or equal to that of the object.Note that a high level of clearance to read implies a low level of clearance towrite - otherwise information flows from high to low levels. This is, in highlysecure systems, not permitted.Mandatory security schemes are relatively easy to understand and, therefore,relatively easy to manage and audit. Discretionary security is difficult to controland therefore mistakes and oversights are easy to make and difficult to detect.You can translate this difficulty into costs.There are perhaps two other key principles in security. One is disclosure, whichis often only on a need-to-know basis. This fits in better with discretionary9

security than mandatory, as it implies neither any prior existing level nor theneed for specific assignment.The other principle is to divide responsibilities. The DBA responsible for security management is him/herself a security risk. Management approaches thatinvolve one person or a group of people that have connections in their workrepresent a similar risk. This emphasises the importance of security auditingand the importance of related procedure design.Database security issuesThis section reviews some of the issues that arise in determining the securityspecification and implementation of a database system.Access to key fieldsSuppose you have a user role with access rights to table A and to table C butnot to table B. The problem is that the foreign key in C includes columns fromB. The following questions arise:Do you have access to the foreign key in C?If you do, you know at least that a tuple exists in B and you know some information about B that is restricted from you.10

Can you update the foreign key columns?If so, it must cascade, generating an update to B for which no privileges havebeen given.These problems do not directly arise where the database is implemented byinternal pointers - as a user, you need have no knowledge of the relationshipsbetween the data you are accessing. They arise because relationships are datavalues. Often, knowing the foreign key will not be sensitive in itself. If it is,then the definition of a view may solve the problem.Access to surrogate informationIt is not difficult to conceive of cases where the view of the data provided to auser role extends to the external world.An example should make the problem clear.In a retail environment, there are frequent problems with pilferage. To deal withthese, private detectives work undercover. They are to all intents and purposesemployees of the business and assigned to normal business activities as othermembers of staff. They get pay checks or slips at the same time as everyoneelse, they appear in management information (such as the salary analysis) in thesame manner. They have a job title and participate in the system as someonethey are not. The store manager is unaware of the situation, as is everybodyelse except the corporate security manager. When the store manager accessesthe database, the detective should look like a normal employee. Queries mightinclude:“What leave is due to ?”The security staff have different queries:“Do we have someone in ?”You can probably envisage all kinds of complications. The detective shouldreceive a pay slip with everyone else, but should not actually be paid (or perhapshe/she should be paid something different from the normal pay for the job).You may want to handle these situations on separate databases. As a solutionit may be appropriate, but the larger the problem the more scope there isfor confusion. One suggested solution is the polyinstantiation of tuples - oneindividual is represented by more than one tuple. The data retrieved will dependon the security classification of the user. Tuples will have the same apparentprimary key but different actual primary keys, and all applications will need tobe carefully integrated with the security system.11

Problems with data extractionWhere data access is visualised directly, the problem can be seen clearly enough:it is to ensure that authenticated users can access only data items which theyare authorised to use for the purpose required. When the focus shifts from thedata to the implications that can be drawn from that data, more problems arise.Again, an example should make things clear.You want to know the pay of the chief executive. You have access rights to thetable, except for the MONTHLY-PAY field in this tuple. So you issue an SQLquery SUM (MONTHLY-PAY) across the whole table. You then create a viewSELECT MONTHLY-PAY and issue a SUM on this view. Should you getthe same answer in both cases?If not, you can achieve your objective by subtracting the two sums. If you listedthe monthly pay for all, what would you expect to see - all the tuples exceptthe one restricted? Would you expect to be notified by asterisks that data wasmissing which you were not allowed to see?Another example.You are trying to trace an individual but have limited information. You feedyour limited information into the statistical database (e.g. male, age over 40,white, red car, lives in North London) and retrieve the tuples for all that meetthese categories. As you get more information, the number of tuples reducesuntil only one is left. It is possible to deduce personal information from astatistical database if you have a little information about the structure, evenif no conventional personal identifiers are available (i.e. no date of birth, socialsecurity number or name).Some solutions to this security problem are to prevent access to small numbersof tuples and/or to produce inaccurate data (not very inaccurate but sufficientlyinaccurate to prevent inferences being drawn).Access control in SQLThis section is about the implementation of security within SQL. The basics aregiven in SQL-92 but, as you will realise, much security is DBMS- and hardwarespecific. Where necessary, any specifics are given in the SQL of Oracle. Forsome ideas on Object database management systems (ODBMS) as distinct fromRelational, refer to the later chapter on Object databases.Your first objective is to learn the specifics. The access requirements specification will be implemented using these statements. Your second objective isto extend your understanding of the problem through to the management andaudit functions of an operating system.12

The basic statements come first, and the management functions are discussedsecond. In the first part you will learn the SQL needed to manage a user; in thesecond you will learn a little of the SQL to manage a system.Discretionary security in SQLThis section introduces the SQL statements needed to implement access control.You should aim at having sufficient knowledge of this area of SQL to translatea simple specification into an SQL script. You should also be conscious of thelimitations implicit in this script which hardwires passwords into text.The basics of SQL are inherently discretionary. Privileges to use a databaseresource are assigned and removed individually.The first issue is who is allowed to do what with the security subsystem. Youneed to have a high level of privilege to be able to apply security measures.Unfortunately, such roles are not within the SQL standard and vary from DBMSto DBMS. A role is defined as a collection of privileges.As an example, the supplied roles in Oracle include (among others): SYSOPER: Start and stop the DBMS. DBA: Authority to create users and to manage the database and existingusers. SYSDBA: All the DBA’s authority plus the authority to create, start,stop and recover.The role of the DBA has been covered in other chapters. The point here is thatyou realise there are a large number of predefined roles with different privilegesand they need to be controlled. It is important to be certain that the SQLdefaults do not act in ways you do not anticipate.Schema levelThe first security-related task is to create the schema. In the example below,the authorisation is established with the schema. The authorisation is optionaland will default to the current user if it is not specified.Only the owner of the schema is allowed to manipulate it. Below is an examplewhere a user is given the right to create tables. The creator of the table retainsprivileges for the tables so created. Similarly, synonyms are only valid for thecreator of that synonym.CREATE SCHEMA student database AUTHORISATION U1;13

The U1 refers to the authorisation identifier of the user concerned, who has tohave the right to create database objects of this type – in this case, the schemafor a new database.Provided the authorisation is correct, then the right to access the database usingthe schema can be granted to others. So to allow the creation of a table:GRANT CREATETAB TO U1 ;The topic of schema modifications will not be taken up here.AuthenticationUsing the client/server model (see chapter 15), it is necessary first to connect tothe database management system, effectively establishing both authenticationand the complex layers of communication between the local (client DBMS) andthe server.GRANT CONNECT TO student database AS U1,U2,U3 IDENTIFIED BYP1,P2,P3;U1,U2,U3 are user names, P1,P2,P3 are passwords and student database is thedatabase name.GRANT CONNECT TO student database AS U4/P4 ;Connect rights give no permission for any table within the database. U4/P4 arethe identifiers known to this database security services.NoteUsers, roles and privilege levels can be confusing. The following are the keydistinctions: A user is a real person (with a real password and user account). A role, or a user-role, is a named collection of privileges that can be easilyassigned to a given or new user. A privilege is a permission to performsome act on a database object. A privilege level refers to the extent of those privileges, usually in connection with a database-defined role such as database administrator.Table levelThe authority level establishes some basic rights. The SYSDBA account has fullrights and can change everything. Rights to access tables have to be GRANTedseparately by the DBA or SYSADM.The following example assigns a read privilege to a named table (note only aread privilege). The privilege extends to creating a read-only view on the table:14

GRANT SELECT ON TABLE1 TO U1;And that which may be given can be removed. REVOKE is used generally toremove any specific privilege.REVOKE SELECT ON TABLE1 FROM U1;The main part of this aspect of security, though, is providing access to the data.In a Relational database we have only one data structure to consider, so if wecan control access to one table we can control access to all. And as tables aretwo dimensional, if we can control access to rows and columns, we can deal withany request for data – including schema data. We still have to know what isallowed and what is not but, given the details, the implementation is not initself a problem.Remember that a VIEW is created by an SQL SELECT, and that a view isonly a virtual table. Although not part of the base tables, it is processed andappears to be maintained by the DBMS as if it were.To provide privileges at the level of the row, the column or by values, it isnecessary to grant rights to a view. This means a certain amount of effort butgives a considerable range of control. First create the view:‘the first statement creates the view’CREATE VIEW VIEW1AS SELECT A1, A2, A3FROM TABLE1WHERE A1 20000;‘and the privilege is now assigned’GRANT SELECT ON VIEW1 TO U1WITH GRANT OPTION;The optional “with grant option” allows the user to assign privileges to otherusers. This might seem like a security weakness and is a loss of DBA control.On the other hand, the need for temporary privileges can be very frequent andit may be better that a user assign temporary privileges to cover for an officeabsence, than divulge a confidential password and user-id with a much higherlevel of privilege.The rights to change data are granted separately:GRANT INSERT ON TABLE1 TO U2, U3;GRANT DELETE ON TABLE1 TO U2, U3;GRANT UPDATE ON TABLE1(salary) TO U5;GRANT INSERT, DELETE ON TABLE1 TO U2, U3;15

Notice in the update, that the attributes that can be modified are specifiedby column name. The final form is a means of combining privileges in oneexpression.To provide general access:GRANT ALL TO PUBLIC;SQL system tablesThe DBMS will maintain tables to record all security information. An SQLdatabase is created and managed by the use of system tables. These comprise arelational database using the same structure and access mechanism as the maindatabase. Examples below show the kind of attributes in some of the tablesinvolving access control. The key declarations have been removed.The examples are from Oracle database.16


Mandatory security in SQLThe topic was introduced above. First, classify the subjects (users and theiragents) and the database objects concerned. Classify the means by which eachhas to be assigned a number indicating the security level, as it will be enforcedby the applied rules (e.g. the Bell-LaPadula model (2005)).The classification has to apply by table, by tuple, by attribute and by attributevalue as appropriate to the requirement. Separate classifications may be neededto deal with create (INSERT), read (SELECT), UPDATE and DELETE permissions - though this will depend on the rules to be applied. The rules themselvesmay relate these options as they do in the model quoted.Additional controls may be needed to deal with statistical security - these mightrestrict the number of tuples that can be retrieved, or add inaccuracies to theretrieved data, or provide controls on allowed query sequences (to identify andr

The main work you do in this chapter, however, is directed to database security rather than security in general, and to the principles of security theory and practice as they relate to database security. These are technical aspects of security rather than the big picture. The chapter is organised into two parts. The first part covers security .File Size: 238KB