Download database security and authorization and more Lecture notes Computer Science in PDF only on Docsity!
DATABASE SECURITY AND
AUTHORISATION
As the use of Internet technology is growing for both
the Intranet and the Internet, information security is
becoming exceedingly crucial for organizations. The
World Wide Web provides a convenient, cheap,
easily accessible and instantaneous way of
information dissemination. It makes the
dissemination of very easy but, it is equally
important to ensure that information should only be
accessible information to rightful users who have
access rights to it.
With many organizations using database based
dynamic web pages, corporate information security
has become extremely important. Earlier, strict
database access or specialized client software was
required for viewing the data, but today a simple
web browser is sufficient to view data in a database
that is not properly protected.
Thus, information security is at a vulnerable stage.
Hence, the more a computing firm moves from the
mainframe of the client/server to the Internet the
more possibilities of security penetration. Security
database specialists have to rely on network
administrators for implementing firewalls or other
mechanisms to protect local data since the nature of
Intranet/
Internet information access is such; however, the
database administrator (DBA) has to perform many
security function. This unit will examine the primary
security areas that fall within the domain of the
DBA, who then has to create database orient
solutions.
DATABASE SECURITY: THE SCENARIO
You must realize that security is a journey, and not
the final destination. We cannot assume a
product/technique is absolutely secure, we may not
be aware of fresh/new attacks on that
product/technique. Many security vulnerabilities are
not even published as attackers want to delay a fix,
and manufacturers do not want the negative
publicity. There is an ongoing and unresolved
discussion over whether highlighting security
vulnerabilities in the public domain encourages or
helps prevention of further attacks. The most secure
database you can think of must be found in a most
securely locked bank, or nuclear-proof bunker,
installed on a standalone computer without an
Internet or network connections, and under guard for
emphasis on safeguarding and controlling access to
this proprietary database information.
LEVELS OF DATABASE SECURITY
Securing the database is a fundamental tenet for any
security personnel while developing his or her
security plan. The database is a collection of useful
data and can be treated as the most essential
component of an organisation and its economic
growth. Therefore, any security effort must keep in
mind that they need to provide the strongest level of
control for the database.
As is true for any other technology, the security of
database management systems depends on many
other systems. These primarily include the operating
system, the applications that use the DBMS, services
that interact with the DBMS, the web server that
makes the application available to end users, etc.
However, please note that most importantly, DBMS
security depends on us, the-users.
Common Database Security Failures
Database security is of paramount importance for an
organization, but many organizations do not take this
fact into consideration, till an eventual problem
occurs. The common pitfalls that threaten database
security are:
Weak User Account Settings: Many of the
database user accounts do not contain the user
settings that may be found in operating system
environments. For example, the user accounts name
and passwords, which are commonly known, are not
disabled or modified to prevent access. The user
account settings allow limited capabilities for
security, without password
controls on dictionary checks or account controls
supporting expiration of user account.
Insufficient Segregation of Duties: No established
security administrator role is defined in the database
management of the organisation. This results in
database administrators (DBAs) performing both the
functions of the administrator (for user’s accounts),
as well as the performance and operations expert.
This may result in management inefficiencies.
Inadequate Audit Trails: The auditing capabilities
of databases since it requires keeping track of
additional requirements, are often ignored for
enhanced performance or disk space. Inadequate
auditing results in reduced accountability. It also
reduces the effectiveness of data history analysis.
Server security is the process of controlling access to
the database server. This is the most important
aspect of security and should be carefully planned.
The basic idea here is “You cannot access what you
do not see”. For security purposes, you should never
let your database server be visible to the world. If a
database server is supplying information to a web
server then it should be configured in such a manner
that it is allowed connections from that web server
only.
Such a connection would require a trusted IP
address.
Trusted IP Addresses
To connect to a server through a client machine, you would need to configure the server to allow access to only trusted IP addresses. You should know exactly who should be allowed to access your database server. For example, if it is the back end of a web server, then only that web server address should be allowed access to the database server. If the database server is the back end of a local application that is running on the internal network, then it should only talk to addresses from within the internal network. Database Connections With the ever-increasing number of Dynamic Applications, an application may allow immediate unauthenticated updates to some database. If you are going to allow users make updates to some database via a web page, please ensure that you validate all such updates. This will ensure that all updates are desirable and safe. For example, you may remove any possible SQL code from a user-supplied input. If a normal user does not input SQL code, then we need not allow such data to be submitted. Table Access Control Table access control is probably one of the most overlooked but one of the very strong forms of database security because of the difficulty in applying it. Using a table access control properly would require the collaboration of both the system administrator as well as the database developer. In practice, however such “collaboration” is relatively difficult to find. Restricting Database Access By now we have defined some of the basic issues of database security, let us now look into the specifics of server security, from the point of view of network access of the system. Internet based databases have been the most recent targets of security attacks. All web-enabled applications listen to a number of ports. Cyber criminals often perform a simple “port scan” to
look for ports that are open from the popular default ports used by database systems. How can we address this problem? We can address this problem “by default”, that is, we can change the default ports a database service would listen into. Thus, this is a very simple way to protect the DBMS from such criminals. There are many ways of preventing open access from the Internet. Each DBMS and OS has its own set of unique features for the same. Let us discuss some general methods of preventing open access from the Internet.
- Trusted IP addresses: UNIX servers are configured to answer ping requests only from a list of trusted hosts. In UNIX, this can be accomplished by configuring the hosts file. Thus, it restricts server access to a list of specific users only.
- Server Account Disabling: It may be a good idea to suspend the server ID after three failed password attempts. This may thwart attackers. If such a scheme is not implemented, then an attacker can run a brute force program that generates millions of passwords. Such a program ultimately would break the combination of the user ID and password.
- Special Tools: Some customized tools, for example, Real Secure, send an alert when an external server is attempting to breach your system security. There are many such similar products available for the protecting of the DBMS from unauthorized Internet access. ACCESS CONTROL All relational database management systems provide some sort of intrinsic security mechanisms that are designed to minimize security threats as stated in the previous section. These mechanisms range from the simple password protection offered in Microsoft Access to the complex user/role structure supported by advanced relational databases like Oracle and Microsoft SQL Server. But can we define access control for all these DBMS using a single mechanism? SQL provides that interface for access control. Let us discuss the security mechanisms common to all databases using the Structured Query Language (SQL). An excellent practice is to create individual user accounts for each database user. Although, sharing of user accounts among various users is possible or even one user account can be created for each type of user, however, such a practice should be discouraged. Why? It could be because of the following reasons: It will eliminate individual accountability : If any one of the users make a change in the database, we will not be able to trace it back to a specific individual even after going through audit logs. Imagine what would happen when a specific user leaves the organisation and his or her access from the database is to be removed? It will require change in the password and this will cause inconvenience to other users. Thus, it is important that we provide separate user accounts for separate users. Does this mechanism have any drawbacks? If the expected numbers of database users are small, then it is all right to give them individual user name and passwords and all the database access privileges that they need to have on the database items. However, consider a situation when there are a large number of users. Specification of access rights to all these users individually will take a long time. That is still manageable as it may be a onetime effort, however, the problem will be compounded if we need to change the access right for a particular type of users. Such an activity would require a huge maintenance cost. This cost can be minimized if we use a specific concept called “Roles”. A database may have hundreds of users but their access rights may be categorized in specific roles for example, teachers, student in a university database. Such roles would require specification of access rights only once for the role. The users then can be assigned username, password and specific role. Thus, the
to users and roles as necessary. We will now look at the methods for removing permissions from users. Removing Permissions Once we have granted permissions, it may be necessary to revoke them at a later date. SQL provides us with the REVOKE command to remove granted permissions. The following is the syntax of this command: REVOKE [GRANT OPTION FOR] ON FROM <user/role> Please notice that the syntax of this command is almost similar to that of the GRANT command. Please also note that the WITH GRANT OPTION is specified on the REVOKE command line and not at the end of the command as was the case in GRANT. As an example, let us imagine we want to revoke a previously granted permission to the user Usha, such that she is not able to remove records from the STUDENT database. The following commands will solve the problem: REVOKE DELETE ON STUDENT FROM Usha There is one additional mechanism supported by some commercial DBMS that is worth discussing − the DENY command. This command can be used to explicitly deny permission to a user that s/he might otherwise have received because of his/her membership of a role. Here is the syntax: DENY ON TO <user/role> Consider the last problem again, let us imagine that Usha was also a member of the Teachers role that also had access to the STUDENT table. The previous REVOKE statement would not be sufficient to deny her access to the table. It will remove the permission granted to her through a GRANT statement, but would not affect the permissions gained through her membership in the Teachers role. However, if we use a DENY statement it will block permission for any role. Here is the command: DENY DELETE ON STUDENT TO Usha Thus DENY command creates a “NOT PERMITTED” statement in the database access controls. If we later want to give Usha permission to remove student records again from the STUDENT table, we cannot simply use the GRANT command. This is because of the fact that the GRANT command permission to DELETE record would be overridden by the existing DENY. Thus, first we use the REVOKE command to remove the DENY Not permission as: REVOKE DELETE ON STUDENT FROM Usha Please notice that this command is exactly the same as the REVOKE used to remove a granted permission. Thus, the DENY and GRANT commands both work in a similar fashion -- they both create permissions in the database access control mechanism. The REVOKE command removes all such permissions for the specified user. Once this command has been issued, Usha will be able to delete student records from the table if she is a member of a role that possesses that permission. You can also issue a GRANT command to provide the DELETE permission to Usha’s account. The access control mechanisms supported by the Standard Query Language is a good starting point, but you must look into the DBMS documentation to locate the enhanced security measures supported by your system. You will find that many DBMS support more advanced access control mechanisms, such as granting permissions on specific attributes. STATISTICAL DATABASE SECURITY By now we have discussed the basic security measures in both DBMS and SQL commands that provide necessary permissions to the users. However, in practice there are many database systems where information can be determined without having the access rights to do so. This is a breach of data confidentiality. In the subsequent subsection we discuss this problem and the way to resolve it. Types of Disclosures Data of an organisation is a very sensitive resource. Even the characteristics of data are quite sensitive. For example, existence of a piece of data such as “use of health related drugs” is sensitive information and is a form of disclosure. The various type of disclosure may be:
- Exact Data: It may be defined as the determination of the value of an item by using a sequence of complex queries.
- Bounds: It is defined as finding the value of data item between two values. The bounds in such cases may be lowered using binary search. This may lead to a very narrow range.
- Negative Result: Sometimes it may be possible to determine a negative result. This is also a form of disclosure.
- Existence: The existence of data value in itself is a sensitive piece of information, regardless of the actual value. For example, existence of a record regarding defense expenses may be a disclosure. Please note that such disclosure of data can be obtained without making a direct query to the database but rather a set of queries. We will explain it with the help of an example in the next sub-section. Please remember “A good security scheme needs to protect data using access control while avoiding such indirect disclosures”.
Security vs. Decisions
Disclosure of data as indicated in the previous section is a major problem as disclosure may result in breach of security in some form or the other, and thus, is not acceptable. Thus, the first step in this direction would be to reject any query that directly asks for sensitive information that is hidden. But, how about a sequence of queries that are raised for the purpose of statistics (management information)? For example, we may be able to determine the average marks obtained in a class of 50 students, but if only 2 students have opted for a subject then the first student who knows his/her marks can find the marks of the other student by issuing the average marks query. Thus, statistical queries should be permitted only when some minimum number of records satisfies a condition. Thus, the overall objectives are to make sure that security is not compromised. Let us discuss some of the queries that may result in the disclosure of sensitive data. Consider the relation in the following Table : Enrolment No. Name Gender Grade Fee paid Age Hostel 0601 Ani l M C 10000 25 Ganga
ii) The queries that result in limited value can be checked by either rejecting a query. If the result set size is too small, it is also called ‘suppression’ or if the information that is to be displayed as a result of the query is changed slightly so that it does not match the actual value it is also called concealing. MULTILEVEL SECURITY In certain applications data items can be classified under various levels of security. Some such security levels may be Secret, Classified, etc. The database system that supports such security features are known as Multilevel Sensitive Databases. Such systems may have the following three security features: Security of different objects may be different from the other attribute values of that tuple or security may be different from the other values of the attributes. Thus, every individual element is the micro item for security. In addition, security against statistical queries may also need to be provided. A number of security level needs to be defined for such security. But how do we enforce the multilevel security? There are many techniques to support multi-level security. Two important methods of these are: Partitioning In this approach the original database is divided into partitions. Each of the partitions has a different level of security. However, the major drawback of this approach is that the database looses the advantage of a relation. Encryption Encryption of critical information may help in maintaining security as a user who accidentally receives them cannot interpret the data. Such a scheme may be used when a user password file is implemented in the database design. However, the simple encryption algorithms are not secure, also since data is available in the database it may also be obtained in response to a query. There are many more schemes to handle such multi-level database security. These references are given in the further reading. AUDIT TRAILS IN THE DATABASES One of the key issues to consider while procuring a database security solution is making sure you have a secure audit-trail. An audit trail tracks and reports activities around confidential data. Many companies have not realized the potential amount of risk associated with sensitive information within databases unless they run an internal audit which details who has access to sensitive data and have assessed it. Consider the situation that a DBA who has complete control of database information may conduct a security breach, with respect to business details and financial information. This will cause tremendous loss to the company. In such a situation database audit helps in locating the source of the problem. The database audit process involves a review of log files to find and examine all reads and writes to database items during a specific time period, to ascertain mischief if any, banking database is one such database which contains very critical data and should have the security feature of auditing. An audit trail is a log that is used for the purpose of security auditing Database auditing is one of the essential requirements for security especially, for companies in possession of critical data. Such companies should define their auditing strategy based on their knowledge of the application or database activity. Auditing need not be of the type “all or nothing”. One must do intelligent auditing to save time
and reduce performance concerns. This also limits the volume of logs and also causes more critical security events to be highlighted. More often then not, it is the insiders who makes database intrusions as they often have network authorization, knowledge of database access codes and the idea about the value of data they want to exploit. Sometimes despite having all the access rights and policies in place, database files may be directly accessible (either on the server or from backup media) to such users. Most of the database applications, store information in ‘form text’ that is completely unprotected and viewable. As huge amounts are at stake, incidents of security breaches will increase and continue to be widespread. For example, a large global investment bank conducted an audit of its proprietary banking data. It was revealed that more than ten DBAs had unrestricted access to their key sensitive databases and over hundred employees had administrative access to the operating systems. The security policy that was in place was that proprietary information in the database should be denied to employees who did not require access to such information to perform their duties. Further, the bank’s database internal audit also reported that the backup data (which is taken once every day) was also cause for concern as tapes could get stolen. Thus the risk to the database was high and real and that the bank needed to protect its data. However, a word of caution, while considering ways to protect sensitive database information, please ensure that the privacy protection process should not prevent authorized personnel from obtaining the right data at the right time. The credit card information is the single, most common financially traded information that is desired by database attackers. The positive news is that database misuse or unauthorized access can be prevented with currently available database security products and audit procedures. DISTRIBUTED DATABASES Relational Database Management Systems are the backbone of most of the commercial database management systems. With the client-server architecture in place they can even be used in local and global networks. However, client-server systems are primarily centralized in nature-that is, the data is under the control of one single DBMS. What about applications that are distributed, where large number of transactions may be related to a local site, and where there are also a few global transactions? In such situations, it may be advisable to use a distributed database system. In this unit, we will discuss the classification of distributed databases, its functions and architecture, distributed query design and the commit protocol. Towards the end of the unit we will also introduce the concept of replication servers. CENTRALISED Vs. NON-CENTRALISED DATABASES If the data of an organisation is stored at a single location under the strict control of the DBMS, then, it is termed as a centralized database system. A centralized system has the advantage of controlling data under a central authority, so, the chances of integrity loss, inaccuracy are very low. In such a system it is easier to enforce business rules and the system can also be made very secure. However, consider the situation when the organisation is geographically dispersed and the data is created and used at many distributed offices of the same organisation. In such a case the centralized database may act, as a bottleneck as all accesses to data has to be from the central site. The reliability and availability of such a system will not only be dependent on the centralized system but also on the communication links. The load on the central system may also
is available in the non-database system. This action is to be supported transparently, such that the users need not know the details.
- Common interface for Procedural Access: A heterogeneous DDMS must support a common interface for any procedural access like messaging and queuing systems. One such implementation interface may be the use of Host language-SQL remote procedure calls.
- Support for Data Dictionary translation: One of the key support needed for DDBMS is to homogenize the data dictionaries of the systems as well as the non-database systems. One of the basic requirements here would be to translate a reference to a data dictionary or, a DBMS into a reference and then, to a no system’s data dictionary.
- Heterogeneous DDBMS should allow accessing data of non-systems directly without the use of SQL; on the other hand, it should also allow access to stored procedures using remote procedure calls of the host languages.
- It should have support for the various different kinds of character sets that are used across non- systems and DBMS.
- It should support the use of distributed Multi-Threaded Agents. The Multithreaded agents help in reducing the number of required server processes. It should also provide a graphic interface to access those agents. Homogeneous Distributed Databases The homogeneous distributed database systems contain identical DBMS across cooperating sites. These sites are aware of each other and need to compromise a little on their individual autonomy. In this unit, we have discussed details regarding these types of distributed database systems. Functions of a DDBMS A DDBMS primarily is built on a centralized relational database system, thus, it has the basic functionality of a centralized DBMS along with several other functionalities such as:
- It allows access to remote sites using standard SQL interfaces, thus allowing transfer of queries or components of queries as well as data of the database over a network.
- It has complete control over the system catalogue that stores the details of data distribution. Please note that this catalogue itself may however, be distributed.
- It performs distributed query processing, optimization and evaluation. Please note that distributed query processing involves even access to remote data over the network. One of the major functions of the distributed database system is the control of distributed concurrent transactions to maintain consistency of the data that may be replicated across various sites.
- Access to the databases under the replicated environment should be controlled. Thus, a distributed database management system should follow proper authorization/access privileges to the distributed data.
- Distributed recovery mechanisms in addition to dealing with the failure of participating sites may also need to deal with the failure of communication links. Before discussing some specific details of the DBMS, let us first look at the reference architecture of DDBMS. REFERENCE ARCHITECTURE OF DDBMS Distributed database systems are very diverse in nature; hence, it may be a good idea to define reference architecture for such database systems. For the DDBMS reference architecture may be looked at as, an extension of ANSI SPARC three level architecture. The ANSI SPARC
architecture has been defined for the centralized system and can be extended for the distributed database systems with various levels defined for data distribution as well. Reference architecture may also define the following additional schema for distributed database systems:
- A set of global schemas that would define application interface. Thus, a global schema can be further divided into:
- Global external schema, and
- Global conceptual schema.
- Fragmentation and allocation schema will determine various fragments and their allocation to various distributed database sites.
- Finally, it would require basic schemas as proposed by ANSI/SPARC architecture for local databases. However, some modification would be. Let us, explain the various schemas of reference architecture in more detail. Global External Schema The basic objective of providing a global external schema is to define the external application interface for a distributed database application. This level is similar to the external schema as defined for the ANSI SPARC architecture. Thus, this interface must support the concept of data independence. However, in this case, it is logical data independence that is provided to a database application from the distributed data environment. Global Conceptual Schema A Global conceptual schema defines logical database application as if there is, no distribution of data. Conceptually, this schema is almost similar to, the conceptual level of ANSI/SPARC architecture. Thus, this schema defines the entities, relationships, and constraints including security and integrity constraints for global database application. This level is responsible for ensuring physical data independence between an application and the data distribution environment where it is to be implemented. Fragmentation and Allocation Schema Fragmentation and allocation schema is required to describe the data fragments that have been created for the distributed database, along with their replicas that have been allocated to various database sites. This schema, thus, basically is for the identification of data distribution among the distributed sites. Local schemas The local schema at each site can consists of conceptual and internal schemas. However, to map the fragmentation and allocation schema to local external objects may require local mapping schema. It is this schema (that is local mapping schema), that hides the details of various DBMSs, thus, providing the DBMS independence. This schema mapping, however, would be minimal for a homogeneous distributed database system. Figure 1 shows the reference architecture of DDBMS.
the Figure 2 the transactions that are shown occurring on Site 1 will have direct connection to Site 1, but they will have an indirect communication to Site 2, to access the local sales data of that site. Thus, a network is necessary for a distributed database system. Component architecture of DDBMS Thus, we have clients, servers and the network in the distributed databases. Now the question is how to use names in this network of a DDBMS sites? The names on a DDBMS may be provided using a global directory service. To allow easy access to the configuration of data, a networks
name server can be used to provide information on the repositories of the databases and their data distribution. Global System Catalogue: One of the ways of keeping information about data distribution is through the system catalogue of the DBMSs. In a DDBMS we need to keep a global system catalogue. This catalogue itself might be distributed in implementation. In addition to basic information about the databases it should keep information on data fragmentation and replication schema. Database Links : A database in a distributed database is distinct from the other databases in the system. Each of these distributed databases may have their own global database name. All these database components would also have certain local names. Now, the questions are: How reach out from one database in a distributed database to another database and how does one resolve the names of various schema elements in the distributed databases that are available across the network. Figure 3 represents a hierarchical arrangement of databases components that are available on a network. It shows the hierarchical arrangement of a hypothetical distributed database of some Universities. As far as the first question is concerned, an application can travel from one database system to another by using database links, which is a unidirectional path from one database to another. Such links must be transparent from a user point of view. Figure 3: Network directories and global database naming Network directories and global database naming Now, as far as naming issues are concerned, the names of the database may be resolved by specifying the path. For example, in the Figure 3 , the name for the result database in IGNOU – SRE may be RESULT, may also be the same for the JNU, which database for storing the result of the student. However, these databases will be referred to as: RESULT.SRE.IGNOU.EDU_INST.EDU (Please note that EDU_INST represents “Educational Institutes” block in Figure 3 ) and RESULT.SCHOOL.JNU.EDU_INST.EDU
