Database Administration Aspects - Assignment Example

HIGHER COLLEGES OF TECHNOLOGY ABU DHABI WOMEN’S COLLEGE Assessment Cover Sheet June 11, 2018 Course Code and Name: ITEC N312 –Database Administration Time Allowed: 3 Weeks Assessment Number: AT1 –Take home assignment LO/Goals Covered: LO 01 Special Instructions: The project is to be completed in a group of maximum three students. Each team is to submit soft and hard copy of the report. Project presentation is assessed individually. % of Final Grade: 25% of the final grade Total Marks Available: 100 marks. Student Name: Noura Khalid (H00131666), Aysha Atiq (H00132471), Mona Bushra (200233058) Student Section: BUIS01 Result: / 100 HCT Academic Honesty Policy Academic dishonesty will not be tolerated within the HCT. Academic dishonesty includes cheating, plagiarism (copying) or any other attempt to gain an academic advantage in a dishonest or unfair manner. Breaches of the Academic Honesty Policy will result in dismissal from HCT Abstract The purpose of this report is to discuss different database administration aspects for a new IT system installed in Pick and Go Company. PICK and GO supermarket chain in the UAE that provides organic products only. The organization has numerous branches all over the UAE. The company wants to come up with a new IT system to allow effective sharing of information among the different branches. In order to sustain diverse business functions the new system will employ the oracle 11g database. Summary The report describes the major roles of a database administrator. The roles are divided into central and local DBA roles. The report discusses what database instances are and the importance of using them in a database. Database back up and recovery have been explained. There are various back up and recovery strategies recommended for the newly established PICK and GO supermarket. Databases are prone to threats by malicious individuals. The report includes the major database threats experienced and the recommendation on how to prevent them is provided. Lastly, the reasons for reviewing and monitoring database resources are provided and explained in detail. 1. Discuss the roles and responsibilities of a Database Administrator (DBA). Introduction Database is a system used to store data of all PICK and GO chain supermarket. All the branches databases will be networked and controlled by a Database administrator. Database Administrator role and responsibilities According to Institute for Career Research (2006), the Database Administrator main roles entails managing PICK and GO supermarket database will be responsible for resolving any problems and making sure that the program is functioning well. The DBA will be responsible for all backup and recovery processes that entail executing periodic backups and having full understanding of the restore process. DBA will observe database activity. The DBA should have knowledge of when transaction rollbacks occur, when the database runs out of system disk space. The main role of database administrator in PICK AND GO Company: Interact with a production database in a first level of support role Installation Oracle Software and Databases, configuration and upgrading of Oracle server software and related products. Evaluate Oracle features and Oracle related products. Take care of the Database design and implementation. Create new database users as required-each branch in the company will have different user, so each user will have a database. Administer all database objects, including tables, clusters, indexes, Views, sequences, packages and procedures Implement and maintain strong database security level (create and maintain users and roles, assign privileges). Establish and maintain sound backup and recovery policies and procedures. Database tuning, performance monitoring and proactive database tasks. Application tuning and performance monitoring. Setup and maintain documentation and standards for future usage. Plan growth and changes (capacity planning). Work as part of the IT department team and provide 7×24 supports when required. Do general technical troubleshooting regarding the databases, applications and development tools. and give consultation to development teams. Interface with Oracle for technical support. 2. Explain why DBAs need to create database instances. Database instance A database instance is defined as a collection of memory form managing database records. A database is a series of physical records saved on disk produced by the “CREATE DATABASE” statement. The instance manages data related to it and serves the client of the database. Every running oracle database is associated with at least one oracle database instance (Malcher, 2010). Database Instance Structure Significance of DBAS creating instances is because after an instance is started, Oracle Database assigns a memory area known as the “system global area” (SGA) and creates one or more background procedures. The SGA performs several processes that include preserving inner data structures that are retrieved by various procedures and threads simultaneously. SGA also caches information blocks interpreted from disk. The instance carries on functioning when server procedures end. The Instance and the Database One computer can allow numerous instances to run simultaneously, each retrieving its own physical database. In “clustered’ and “massively parallel” systems (MPS), “Real Application Clusters” allow numerous instances to mount one database (Konrad, Sven, 2010). Single machine can compose of so many CPUs and so much memory. The division between the memory structures and data records enables Oracle to scale past a single machine by enabling numerous instances—that is, “multiple separate Oracle memory structures”—on separate machines to retrieve the same database. This implies that instances can copy data from each branch of the PICK and Go supermarket and enable every single branch to retrieve ALL data. Each branch has unique data and cannot access other branches data, but instances allow sharing of data. Database administrator is the only one with the privilege of starting an instance and launching the database. After a database is launched, the database administrator is able to shut it down. Users cannot retrieve data when a database is closed. Database security when starting and shutting down is managed through connections to Oracle with administrator privileges. It is also called as environment in technical terms. You can better understand its workings with the help of an example. The example is of an organization that has an employee database. This database will have three instances, which are - Production that is used for storing live data - Pre-production which is used to test new functionality prior to release for production - Development which is used by database developers in order to create new functionality. (Konrad, Sven, 2010). 3. Explain possible database security threats that Pick and Go could face. As a DBA what can you do to protect the database against these threats? The database of Pick and Go will face many security threats and this will be a challenge for a database administrator. This will require a proactive procedure. So, security measures should be taken into consideration to stop those threats. Also, security policies should be Implemented and enforced for all database. This is an example of the possible database security threats and how a database administrator can face and how to deal with those threats: With the increase in usage of databases, the frequency of attacks against those databases has also increased. Here we look at some of the threats that database administrators actually can do something about. Database attacks are an increasing trend these days. What is the reason behind database attacks? One reason is the increase in access to data stored in databases. When the data is been accessed by many people, the chances of data theft increases. In the past, database attacks were prevalent, but were less in number as hackers hacked the network more to show it was possible to hack and not to sell proprietary information. Another reason for database attacks is to gain money selling sensitive information, which includes credit card numbers, Social Security Numbers, etc. We previously defined database security and talked about common database security concepts. Now let's look at the various types of threats that affect database security. Types of threats to database security 1. Privilege abuse: When database users are provided with privileges that exceeds their day-to-day job requirement, these privileges may be abused intentionally or unintentionally. Take, for instance, a database administrator in a financial institution. What will happen if he turns off audit trails or create bogus accounts? He will be able to transfer money from one account to another thereby abusing the excessive privilege intentionally. Having seen how privilege can be abused intentionally, let us see how privilege can be abused unintentionally. A company is providing a “work from home” option to its employees and the employee takes a backup of sensitive data to work on from his home. This not only violates the security policies of the organization, but also may result in data security breach if the system at home is compromised. 2. Operating System vulnerabilities: Vulnerabilities in underlying operating systems like Windows, UNIX, Linux, etc., and the services that are related to the databases could lead to unauthorized access. This may lead to a Denial of Service (DoS) attack. This could be prevented by updating the operating system related security patches as and when they become available. 3. Database rootkits: A database rootkit is a program or a procedure that is hidden inside the database and that provides administrator-level privileges to gain access to the data in the database. These rootkits may even turn off alerts triggered by Intrusion Prevention Systems (IPS). It is possible to install a rootkit only after compromising the underlying operating system. This can be avoided by periodical audit trails, else the presence of the database rootkit may go undetected. 4. Weak authentication: Weak authentication models allow attackers to employ strategies such as social engineering and brute force to obtain database login credentials and assume the identity of legitimate database users. 5. Weak audit trails: A weak audit logging mechanism in a database server represents a critical risk to an organization especially in retail, financial, healthcare, and other industries with stringent regulatory compliance. Regulations such as PCI, SOX, and HIPAA demand extensive logging of actions to reproduce an event at a later point of time in case of an incident. Logging of sensitive or unusual transactions happening in a database must be done in an automated manner for resolving incidents. Audit trails act as the last line of database defense. Audit trails can detect the existence of a violation that could help trace back the violation to a particular point of time and a particular user. 4- a) Define and explain the terms database backup and database recovery. Back Up and Recovery According to Oracle White Paper (2012), Database backup is described as the procedure of creating a complete secondary duplicate of a database or database server with the intention of recovering the database incase of data loss or data destruction. Data recovery can be described as a procedure of retrieving data from destroyed, corrupted, or inaccessible secondary storage means when it cannot be retrieved normally. Backups can be categorized into “physical backups” and “logical backups”. Physical backups can be described as backups of the physical files useful in storing and restoring database. Logical backups contain logical data, for instance, tables or stored procedures. According to Oracle White Paper (2012), “Oracle Secure Backup” (OSB) is a central tape backup control solution for the complete IT environment comprising file systems and Oracle Databases. “Oracle Recovery Manager” (RMAN) offers the native backing and restoring system in Oracle Database, allowing optimized information security in the Oracle Database. b) What backup and recovery strategies do you recommend to be used and Why? Disk Based Backups I recommend “Disk based back ups”. This is used if the database needs “Table space Point in Time Recovery” (TSPITR), changing to a copy or incremental combines as these alternatives is not provided with tape. Major benefits of using Disk supported backup strategy comprise: improved restoring times for data and logical corruptions, and capability for “Tablespace Point in Time”. For disk-supported backup strategy, I recommend the use of a “Fast Recovery Area” (FRA), initial execution of RMAN stage 0 backup, conducting RMAN incremental stage 1 backup daily (Oracle White Paper, 2012). Tape-Based Backup Strategy I also recommend Tape-based backup strategy. Major advantage of a tape-based backup method is that it offers rapid backup and restore rates. For a tape-supported backup strategy, I recommended the procedure of executing the following backups: Weekly RMAN stage 0 (complete) backups of the database, everyday collective RMAN incremental stage 1 backups of the database, and daily backing of the Oracle protected Backup catalog (Oracle White Paper, 2012). 5. Explain why the Pick and Go DBA needs to review and monitor their database resources Database monitoring According to Oracle8i Concepts (2012), the “Database Resource Manager” provides the database administrator with more control over the resource administration than would usually be possible through mere operating system resource administration. Increased resource administration allows enhanced operation and availability. Oracle8i Concepts (2012) notes that through the use of the “Database Resource Manager”, the database administrator can: Assure groups of clients a least amount of processing resources, without considering the load or number of clients in other groups on the system. Allocate existing processing resources by distributing percentages of CPU time to various clients and applications. For instance, in a data warehouse, a greater priority might be assigned to “ROLAP applications than to batch jobs”. Control the amount of parallelism that a group of users can utilize. Set an instance to use a specific plan for distributing resources. A database administrator can adjust the plan, for instance, from a daytime configuration to a nighttime configuration, with no need of shutting or restarting the instance. To employ the “Database Resource Manager” a database administrator outlines the following items: Resource consumer sets Groups of client sessions that have same processing and resource use requirements. Resource strategies Ways of distributing resources among the consumer groups Resource allocation techniques Strategies for assigning a specific resource. Resource allocation techniques are utilized by both plans and consumer sets. Resource strategy directives Database Monitoring will assist PICK and GO DBA monitor database performance and robustness. Database Monitoring enables DBA observe essential parameters, for instance, “Key Read/Write requests”, Table locks, and Transaction Rate. Evaluation of database resources is essential for PICK and GO firm due to developing significance of data integrity and privacy to clients and users. With this information you can do the following, and more: Forecast hardware requirements based on database usage patterns. Analyze the performance of individual applications or SQL queries. Track the usage of indexes and tables. Pinpoint the cause of poor system performance. Assess the impact of optimization activities (for instance, altering database manager configuration parameters, adding indexes, or modifying SQL queries). The “Automatic Database Diagnostic Monitor” (ADDM) is a self-monitoring mechanism installed into Oracle Database. ADDM establish possible performance troubles and the origin in the Database. ADDM also recognizes parts of the database for informational reasons where action is not needed. There are two primary tools with which you can access system monitor information, each serving a different purpose: the snapshot monitor and event monitors. The snapshot monitor enables you to capture a picture of the state of database activity at a particular point in time (the moment the snapshot is taken). Event monitors log data as specified database events occur. The system monitor provides multiple means of presenting monitor data to you. For both snapshot and event monitors you have the option of storing monitor information in files or SQL tables, viewing it on screen (directing it to standard-out), or processing it with a client application. Conclusion The database administrator major roles are making sure the database is performing correctly. The DBA carries out backup and recovery procedure. The DBA also provides technical assistance to database users. Database instances will assist the DBA to control present status of a database. A database is vulnerable to several threats but the major include; denial of service (DOS), weak authentication, and back up data exposure. It is important to review and monitor database resources so as to discover functioning problems likely to occur earlier. References: Top 5 On-line Identity Theft Attacks” Webinar retrieved from: http://www.imperva.com/company/webcasts.htm ( An Oracle White Paper, Backup and Recovery Best Practices for the Oracle Database Appliance. September 2012 Shelly, D. The ORACLE database administrator's guide: Belmont, Calif: Oracle Corp., 1986. Institute for Career Research. Career as a database administrator: Publisher: Chicago: Institute for Career Research, 2006. Rita, C. Secure computing: threats and safeguards: Publisher: New York: McGraw-Hill, 1997. Denny, C. Securing SQL server: protecting your database from attackers: Publisher: Burlington, MA: Syngress, 2011. Konrad, E; Sven, H. Minimal instances of database schemes with additive cardinality constraints: Publisher: Rostock Univ., Fachbereich Mathematik. 1997. Malcher, M. Oracle Database Administration for Microsoft SQL Server DBAs: Publisher: McGraw-Hill, 2010. ). Oracle8i Concepts Release 2: Retrieved from http://docs.oracle.com/cd/A87860_01/doc/server.817/a76965/dbresmgr.htm..., 2012. Read More