Database Security Architecture and Database Files PDF

Database Management & Security Security Architecture and Database Files Mr. Marwan Lardhi  Introduction Security violations and attacks are increasing globally at an annual average rate of 20%. You serve as a database administrator (DBA) to enforce securi...

Database Management & Security Security Architecture and Database Files Mr. Marwan Lardhi  Introduction Security violations and attacks are increasing globally at an annual average rate of 20%. You serve as a database administrator (DBA) to enforce security policies. Responsibilities can be: – Design and implement a new DB security policy. – Enforce a stringent security policy. – Implement functional specification of a module, i.e. encrypt the stored data, replace sensitive data using the data masking pack. Security measures: – Prevent physical access to the servers where the data resided. – Operating systems require authentication of the identity of computer users. – Implement security models that enforce security measures. – Perform regular database backups. 2 Database Security Level 3 SQL Server Databases (Types & Files) It is essential that the DBA understand the types of databases in SQL Server. There are two broad categories that exist: I. System Databases II.User Databases 4  System Databases System databases: are databases which are created and managed by the SQL Server itself called System databases. SQL Server has four system databases as shown in the below image. 5  System Databases i.The master database stores all the system-level information of an SQL Server instance, which includes: – Server configuration settings – Logon accounts – Linked servers information – Startup stored procedure – File locations of user databases If the master database is corrupt or unavailable, SQL Server cannot be available for users. 6 6  System Databases (cont.) ii.The model database is used as a template for new databases. – Any objects created within this database, or database options that you set within it, will also exist on every new database that you later create. – For example, if you set the Recovery Model to be Full on the Model database, then all new user databases will automatically be configured in the same way. – The model database is required and cannot be deleted, because it is used by tempdb every time the SQL Server service starts. 7  System Databases (cont.) iii. The msdb database is used by the SQL Server Agent for scheduling jobs and alerts. Also, it stores the history of the SQL Agent jobs. – The msdb supports the following:  Jobs & alerts  Database Mail  Service Broker  And the backup & restore history for the databases 8  System Databases (cont.) iv. The tempdb Database is used to hold temporary objects created by users and temporary objects needed by the database engine. – SQL Server uses the temporary database (TempDB) for a number of things that are mostly invisible to us, including:  Temporary tables, table variables, triggers, table sorting, index creation, user- defined functions, and many more.  It is created each time you restart SQL Server. There is only one TempDB per instance.  It is used automatically by the SQL Server database engine to process large queries and data modifications that cannot be handled entirely in memory.  It may also be used by programmers. 9  User Databases II. User Databases are created and managed by the user. – These databases are used for storing business-related information such as employee details, Customer Details, Student details, Product Details, Salary details, etc. – In SQL Server, the user databases can be created, altered and dropped in two ways:  Graphically using SQL Server Management Studio (SSMS)  Using a Query 10  User Databases (cont.) At a minimum, every SQL Server database has two operating system files: a data file and a log file. A.Data files: contain data and objects such as tables, indexes, stored procedures, and views. There are two types of data files; primary and secondary. B.Log files: contain the information that is required to recover all transactions in the database. Data files (primary and secondary) can be grouped together in filegroups for allocation and administration purposes. 11  File groups of SQL Databases Filegroups are a logical grouping of data files that hold all data and database objects defined for the database. A database always consists of at least one filegroup which contains a minimum of one data file. The first file in the database is known as the primary file. This file is given an.mdf file extension by default. – Primary file can be used to store data, but it is also used to store metadata that provides database startup information and pointers to other files within the database. – Every database has one and only one primary file. 12  File & File groups of SQL Databases If additional files are created by user within the database, they are known as secondary files and are given the.ndf extension by default. The secondary files can be created in the primary filegroup or in a new filegroup (secondary filegroup). Secondary files and filegroups are optional, but they can prove very useful to database administrators. 13 Managing File & File groups of SQL Databases Add data files in filegroups Rename the filegroup Change default filegroup Add data files in a filegroup – We can use ALTER DATABASE ADD FILE TO FILEGRAOUP statement. – The syntax is following: ALTER DATABASE ADD FILE (NAME= , FILENAME =, SIZE=, FILEGROWTH= ) To FILEGROUP 14 Managing File & File groups of SQL Databases In the syntax db_name: specify the name of the database in which you want to add a data file. The db_name must be specified after ALTER DATABASE keyword. logical_file_name: specify the logical name of the secondary data file. file_location: specify the path of the data file. file_size: specify the initial size of the data file. The unit of the Size parameter can be KB/MB/GB. datafile_growth: specify the growth of the data file. The unit of the FILEGROWTH parameter can be KB/MB/GB file_group_name: specify the name of the filegroup in which you want to add a data file. 15 Managing File & File groups of SQL Databases Rename existing filegroup You can use ALTER DATABASE MODIFY FILEGROUP statement. Following is the syntax: ALTER DATABASE MODIFY FILEGROUP NAME= In the syntax – db_name: specify the database name in which the filegroup has been created. The database name must be specified after ALTER DATABASE statement. – file_group_name: specify the filegroup name that you want to rename. – file_group_new_name: specify the new name of the filegroup. 16 Managing File & File groups of SQL Databases Change default filegroup To change the default filegroup, we can use ALTER DATABASE MODIFY FILEGROUP statement. Following is the syntax: ALTER DATABASE MODIFY FILEGROUP DEFAULT In the syntax, – db_name: specify the database name in which the filegroup has been created. The database name must be specified after ALTER DATABASE statement. – File_group_name: specify the filegroup name that you want to make as a default filegroup. The filegroup name must be specified after MODIFY FILEGROUP keyword. – DEFAULT: the DEFAULT keyword must be specified after the filegroup name. 17 Q&A What are the categories of databases that exist in SQL Server? What are system databases? And list them. What is the role of the master database? What is the job of msdb database? What are user databases? What are the operating system files required for every SQL Server database? What are the two types of data files in SQL Server? What is the purpose of log files? Describe the relationship between the master database and user databases. 18