home | non-tech | cs | ee | about
Abstract New

Database vs Schema in database systems - a look at SQL Server and MySQL

Developers are often confused about the distinction between a schema and a database in various database management systems. In MySQL, for instance, there is no distinction between a schema and a database. They mean one and the same thing. However, a schema in SQL Server is more akin to a namespace that enables a logical grouping of tables in the database.

Schemas were introduced in SQL Server 2005 as containers to store database objects. Each database object is contained within a schema. Permissions could be applied to schemas to manage access rights. Permissions to the schema can be controlled on a per-user basis. Users can be granted different levels of access to multiple schemas. So why is this distinction important?

An explanation of Schemas in SQL Server - Time for a contrived example

Consider the following tables in a database

employeedetails - containing the employee details
customerdetails - containing the customer details
invoicedetails - containing the invoices raised

Assume that the organization has an HR login to SQL Server for human resources to access employee information. However, the HR login should not be able to view/edit the customer or the invoice data. To enable this security measure, you can group the employee table (and other tables say containing information about salary/incentives for the employees) under an employee schema and the customer and invoice tables under a customer schema. You can then setup permissions to enable the HR login access only tables under the employee schema.

Creating Schemas in SQL Server

Create a employee schema and create the table employeedetails under the employee schema


create database testdb;
use testdb;
create schema employee;

create table employee.employeedetails
(
  EmployeeID INT IDENTITY(1,1) NOT NULL,
  FirstName varchar(256) NOT NULL,
  LastName varchar(max) NOT NULL
);


Create a customer schema and create the tables customerdetails and invoicedetails under the customer schema


create schema customer;
create table customer.customerdetails
(
  CustomerID INT IDENTITY(1,1) NOT NULL,
  FirstName varchar(256) NOT NULL,
  LastName varchar(max) NOT NULL
);


create table customer.invoicedetails
(
  InvoiceID INT IDENTITY(1,1) NOT NULL,
  Description varchar(max),
  TotalAmount decimal(19,4)  
);

Create the HR user and login



-- Create a login 
CREATE LOGIN HR
    WITH PASSWORD = 'abcd1234';
GO

-- Creates a database user for the login created above.
CREATE USER HR FOR LOGIN HR;
GO


Note: If you do not create an object explicitly under the schema it will fall under the default dbo schema.

Setup permissions so that the HR can view and perform select operations ONLY on the tables under the employee schema.


GRANT SELECT ON SCHEMA :: Employee TO HR ;

An user logged in as the admin will see all the tables (see Fig. 1), whereas an user logged in with the HR login credentials will only see the employeedetails table (see Fig. 2).

Admin Login - Fig. 1

Restricted HR Login - Fig. 2
Further, the statement insert into employee.employeedetails values ('John', 'Doe'); will error out with the following message - The INSERT permission was denied on the object 'employeedetails', database 'testdb', schema 'employee'.

Why is a schema important?

  • Schemas allow for a descriptive grouping of related tables, thereby enhancing the understanding of the data architecture. Looking at the database in the above example, it becomes clear that customers and employees are two important actors in the business ecosystem. In addition, it gives us an idea of the responsibilities of the HR role.
  • Schemas allow us to regulate user access to the database tables. Take in the above scenario an user created for an HR role. The billdetails and customerdetails table are not relevant for an HR. You can restrict the HR user to access just the employeesalary and the employeedetail tables. Similarly other users can be restricted from accessing the table in which employee salaries are being stored. Schema permissions should be implemented after careful consideration as it does add a layer of complexity .

The Adventureworks database is a good example of how schemas can be used to group related database objects together.

Migrating to MySQL from SQL Server with schemas

When migrating MySQL using the Workbench migration wizard we will be given three options
  • Keep schemas as they are - there will be separate schemas/databases created. Remember - MySQL does not distinguish between a schema and a database.
  • Only one schema - This will assign the default database name as the schema name.
  • Keep current schema as a prefix - This is just an option so the naming conventions. However, they will be part of the same database

Hope that clarifies the concept of a Schema in SQL Server.

1 comment:

Mikel said...

Thanks for the post, I find here all that I needed

Post a Comment

© 2014 - 2015 abstract new. All rights reserved.