The Database Engine uses schemas in its security model to simplify the relationship between users and objects, and thus schemas have a very big impact on how you interact with the Database Engine. This section describes the role of schemas in Database Engine security.
The first subsection describes the relationship between schemas and users; the second subsection discusses all three Transact-SQL statements related to schema creation and modification.
1. User-Schema Separation
A schema is a collection of database objects that is owned by a single person. The main purpose of a schema is to group logically related objects of a large database in different subunits. The Database Engine supports named schemas using the notion of a principal. A principal can be either of the following:
- An indivisible principal
- A group principal
An indivisible principal represents a single user, such as a login or user account. A group principal can be a group of users, such as a role or Windows group. Principals are ownerships of schemas, but the ownership of a schema can be transferred easily to another principal and without changing the schema name.
The separation of database users from schemas provides significant benefits, such as:
- One principal can own several schemas.
- Several indivisible principals can own a single schema via membership in roles or Windows groups.
- Dropping a database user does not require the renaming of objects contained by that user’s schema.
Each database has a default schema, which is used to resolve the names of objects that are referred to without their fully qualified names. The default schema specifies the first schema that will be searched by the database server when it resolves the names of objects. The default schema can be set and changed using the DEFAULT_SCHEMA option of the CREATE USER or ALTER USER statement. If DEFAULT_SCHEMA is left undefined, the database user will have dbo as its default schema. (All default schemas are described in detail in the section “Default Database Schemas” later in this chapter.)
2. DDL Schema-Related Statements
There are three Transact-SQL schema-related statements:
- CREATE SCHEMA
- ALTER SCHEMA
- DROP SCHEMA
The following subsections describe in detail these statements.
2.1. CREATE SCHEMA
Example 12.10 shows how schemas can be created and used to control database security.
NOTE Before you start Example 12.10, you have to create database users peter and mary. For this reason, first execute the Transact-SQL statements in Example 12.12, located in the section “Managing Database Security Using Transact-SQL Statements.”
Example 12.10
USE sample;
GO
CREATE SCHEMA my_schema AUTHORIZATION peter
GO
CREATE TABLE product
(product_no CHAR(10) NOT NULL UNIQUE,
product_name CHAR(20) NULL,
price MONEY NULL);
GO
CREATE VIEW product_info
AS SELECT product_no, product_name
FROM product;
GO
GRANT SELECT TO mary;
DENY UPDATE TO mary;
Example 12.10 creates the my_schema schema, which comprises the product table and the product_info view. The database user called peter is the database-level principal that owns the schema. (You use the AUTHORIZATION option to define the principal of a schema. The principal may own other schemas and may not use the current schema as his or her default schema.)
NOTE The two other statements concerning permissions of database objects, GRANT and DENY, are discussed in detail later in this chapter. In Example 12.10, GRANT grants the SELECT permissions for all objects created in the schema, while DENY denies the UPDATE permissions for all objects of the schema.
The CREATE SCHEMA statement can create a schema; create the tables and views it contains; and grant, revoke, or deny permissions on a securable in a single statement. As you already know, securables are resources to which the system regulates access. There are three main securable scopes: server, database, and schema, which contain other securables, such as logins, database users, tables, and stored procedures.
The CREATE SCHEMA statement is atomic. In other words, if any error occurs during the execution of a CREATE SCHEMA statement, none of the Transact-SQL statements specified in the schema will be executed.
Database objects that are created in a CREATE SCHEMA statement can be specified in any order, with one exception: a view that references another view must be specified after the referenced view.
A database-level principal could be a user, role, or application role. (Roles and application roles are discussed in the “Roles” section later in the chapter.) The principal that is specified in the AUTHORIZATION clause of the CREATE SCHEMA statement is the owner of all objects created within the schema. Ownership of schema-contained objects can be transferred to any other database-level principal using the ALTER AUTHORIZATION statement.
The user needs the CREATE SCHEMA permission on the database to execute the CREATE SCHEMA statement. Also, to create the objects specified within the CREATE SCHEMA statement, the user needs the corresponding CREATE permissions.
2.2. ALTER SCHEMA
The ALTER SCHEMA statement transfers an object between different schemas of the same database. The syntax of the ALTER SCHEMA statement is as follows:
ALTER SCHEMA schema_name TRANSFER object_name
Example 12.11 shows the use of the ALTER SCHEMA statement.
Example 12.11
USE AdventureWorks;
ALTER SCHEMA HumanResources TRANSFER Person.ContactType;
Example 12.11 alters the schema called HumanResources of the AdventureWorks database by transferring into it the ContactType table from the Person schema of the same database.
The ALTER SCHEMA statement can only be used to transfer objects between different schemas in the same database. (Single objects within a schema can be altered using the ALTER TABLE statement or the ALTER VIEW statement.)
2.3. DROP SCHEMA
The DROP SCHEMA statement removes a schema from the database. You can successfully execute the DROP SCHEMA statement for a schema only if the schema does not contain any objects. If the schema contains any objects, the DROP SCHEMA statement will be rejected by the system.
As previously stated, the system allows you to change the ownership of a schema by using the ALTER AUTHORIZATION statement. This statement modifies the ownership of an entity.
NOTE The Transact-SQL language does not support the CREATE AUTHORIZATION and DROP AUTHORIZATION statements. You specify the ownership of an entity by using the CREATE SCHEMA statement.
3. Default Database Schemas
Each database within the system has the following default database schemas:
- guest
- dbo
- INFORMATION_SCHEMA
- sys
The Database Engine allows users without user accounts to access a database using the guest schema. (After creation, each database contains this schema.) You can apply permissions to the guest schema in the same way as you apply them to any other schema. Also, you can drop and add the guest schema from any database except the master and tempdb system databases.
Each database object belongs to one and only one schema, which is the default schema for that object. The default schema can be defined explicitly or implicitly. If the default schema isn’t defined explicitly during the creation of an object, that object belongs to the dbo schema. Also, the login that is the owner of a database always has the special username dbo when using the database it owns.
The INFORMATION_SCHEMA schema contains all information schema views (see Chapter 11). The sys schema, as you may have already guessed, contains system objects, such as catalog views.
Source: Petkovic Dusan (2020), Microsoft SQL Server 2019: A Beginner’s Guide, Seventh Edition-McGraw-Hill Education.