Lecture 09 - Administration of DBMS PDF

5502CSQR Database Systems Lecture 9– Administration of DBMS/ Security of DBMS Dr. Ali Baydoun Class Times: [email protected] Monday: 9:00 AM till 12:00 AM– Lecture Tuesday: 12:00 AM till 2:00 PM - Lab  Outline In this lecture,...

5502CSQR Database Systems Lecture 9– Administration of DBMS/ Security of DBMS Dr. Ali Baydoun Class Times: [email protected] Monday: 9:00 AM till 12:00 AM– Lecture Tuesday: 12:00 AM till 2:00 PM - Lab  Outline In this lecture, we will address: Administration of DBMS/ Security of DBMS 2  Module Syllabus Week Date Session Topic (Week beginning) 1 M odule Overview & Introducing DBM S Reviewing 1 10/09 Data - WorkShop A - Review of L4 Data M odelling Modelling - WorkShop B - Review of L4 Data M odelling L4 2 17/09 2 SQL Components & DDL (for Tables) 3 DML (for SELECT) 3 24/09 4 DML (for JOINS & INSERT - UPDATE - DELETE) 5 Views & Indices (DDL & DML) Database 4 01/10 6 SPROCs & Triggers (DDL & DML) Development – SQL coding SPROCs & Triggers (DDL & DML), Java DB & 7 JDBC 5 08/10 8 Java DB & JDBC Semester 1 6 15/10 9 DBMS Administration, Security of DBMS DBMS 7 22/10 10 Security of DBMS, Query Optimization administrative 11 Concurrency 8 29/10 12 Alternate DBM S/ NoSQL 13 Berkeley DB 9 05/11 Alternative 14 M ongo DB DBMS - NoSQL 16 Data WareHousing & OLAP 10 12/11 - Revision for Exam 11 19/11 12 26/11 - Revision for Exam 3  Examination - Assessment Part 2 Examination Duration: 2 hours When: Beginning of December 2023 Weight: 50% Learning Outcome to be assessed: Perform database administration tasks. Evaluate the role of database systems in supporting business intelligence 4  Examination - Assessment Part 2. cont.. Lectures 09 onwards + tutorials + own research contribute towards final examination Tutorials (labs) are research driven with the exception of some practical DBA (Final Week) Final examination is individual + contributes 50% towards final mark Thus we will concentrate on Database administration Business intelligence Attend lectures, complete tutorial work and attend revision session at the end = success 5  Administration of DBMS This phase of the module looks at ‘Admin. of DBMS’. This is in contrast to every session of the module to date, which was looking at ‘Developing for DBMS’. Key Points of Interest: Overview of Administration. Security. Query Optimization. Concurrency. 6  DBAs (Database Administrators) DBA’s (DataBase Administrators): Database systems cannot operate without a human component and human administration Effective data administration requires both technical and managerial skills DA must set data administration goals DBA is focal point for data/user interaction Need for diverse mix of skills 7  DBAs Roles The role od DBA is a managerial & technical one, including : Planning & Resourcing. Installing & Configuring (DBMS). Designing, Developing & Migrating (DBs). Supporting & Liaising (e.g. Users, Developers). Monitoring (of Integrity, Security & Performance). Backing Up, Recovering & Debugging (DBs). 8  DBAs Roles.. cont. The role’s requisite knowledge & experience include : Database Theory & Design (e.g. Normalisation). Querying (e.g. SQL). Architectures (e.g. Client-Servers & Embedded). Specific DBMS (e.g. MySQL).... & Tools (e.g. MySQL WorkBench). Operating Systems (e.g. Windows & Linux). Networking & Storage (e.g. RAID). 9  DBAs Roles.. cont. The role’s responsibilities are : Too Many for PowerPoint. Refer to Database Administrator Responsibilities.docx on Canvas! 10  Tools for DBA Tools that allow the DBAs do their jobs: 1- System Schema 2- MySQL Events 3- MySQL WorkBench 4- Backup & Recovery 5-MySQL Shell 11  1- System Schema Typically, the system schema will include: Data Dictionary Tables. Storing info about the Objects (e.g. Tables, Columns, Constraints) in every database in MySQL using a transactional repository (since MySQL version 8.0). Grant Tables. Storing info about the Accounts, Roles & Privileges in MySQL, again, using a transactional repository. However, The sensitivity of the system schema requires that the tables of the data dictionary are not directly accessible! Meaning, we can’t directly query them using SQL! But, we may indirectly query them using the corresponding INFO_SCHEMA 12  INFO_SCHEMA Query ‘TABLES’ of the INFO_SCHEMA. https://dev.mysql.com/doc/refman/8.0/en/information-schema-tables-table.html SELECT * FROM INFORMATION_SCHEMA.TABLES Schema WHERE TABLE_SCHEMA = "banking"; Name -- It gives the database administrator information about a specific schema 13  2- MySQL Events MySQL Events are tasks that run on a schedule. i.e. Triggers where the triggering is the passage of time. https://dev.mysql.com/doc/refman/8.0/en/events-overview.html Such event scheduling is invaluable for many administrative tasks. There are no standardized statements (in SQL) for event scheduling, so each platform employs its own mechanism. CREATE EVENT cutBudget ON SCHEDULE EVERY 1 MINUTE DO UPDATE project SET budget = (budget * 2); 14 SHOW EVENTS;  3- MySQL WorkBench So far, we have been using MySQL Workbench's SQL authoring functionality to write SQL directly. Via the Schemas Tab of the Navigate Pane. But, there is more, as MySQL WorkBench also supports various administrative tasks. https://dev.mysql.com/doc/workbench/en/wb-admin.html Via the Administration Tab of the Navigate Pane. These include the management of Users & Privileges, Status & Sys. Variables & Data Importing / Exporting. 15  MySQL WorkBench.. cont Moving on, MySQL WorkBench also supports various performance tools. https://dev.mysql.com/doc/workbench/en/wb-performance.html Again, via the Administration Tab of the Navigate Pane. These include informative DashBoards & Query Statistics along with bespoke Schema Reporting. 16  4- BackUp & Recovery mysqldump[.exe] https://dev.mysql.com/doc/refman/8.0/en/mysqldump.html A client console app. for generating a logical backup of a given database. i.e. generate SQL statements representing the structure & data of the given database. e.g. It is a console application. It stores in the folder that workbench is installed in C:\Program Files\MySQL\MySQL Workbench 8.0 17  4- BackUp & Recovery From the command prompt: C:\> mysqldump -u root -p banking > bankingdbbackup.SQL Usernam Prompt me for the This is the Schema you Filename, where you e password want to backup want your backup 18  BackUp & Recovery.. cont To recover the database, we use the following file: mysql[.exe] When restoring a given database. C:\> mysql -u root -p banking < bankingdbbackup.SQL Guess What? MySQL WorkBench supports these tasks! https://dev.mysql.com/doc/workbench/en/wb- admin.html 19  Initiation of the Database Recovery process The Database recovery process is initiated when we confront the following critical events : 1- Hardware/software failures: hard disk failure, motherboard failure, memory Failure. This category also includes the application program or operating system errors that cause data to be overwritten, deleted, or lost. 2- Human-caused incidents: This type of event can be categorized as unintentional or intentional.  An unintentional failure: is caused by carelessness by end-users. Such errors include deleting the wrong rows from a table and pressing the wrong key on the keyboard.  Intentional events: this category includes the security threats caused by hackers trying to gain unauthorized access to data resources and virus attacks. 3- Natural disasters: This category includes fires, earthquakes, floods, and power failures. 20  5- MySQL Shell MySQL Shell is lightweight (yet functionality-rich) client console app. for issuing statements directly to MySQL. (what we start the semester with) https://dev.mysql.com/doc/mysql- shell/8.0/en Supports scripting in SQL, JavaScript & Python. / 21  Resource Materials MySQL Reference Manual. https://dev.mysql.com/doc/refman/8.0/en/index.html MySQL Server Administration. https://dev.mysql.com/doc/refman/8.0/en/server-administration.html MySQL WorkBench Manual. https://dev.mysql.com/doc/workbench/en/ / 22  Any Questions ? Next Lecture Security of DBMS 23