What are schemes? What are the advantages of using it?
In what situations is its use recommended?
2 answers
What are schemes?
Are collections of objects within a given database, which organize various aspects and are important for security segmentation, facilitating the administration of objects and data.
Starting with SQL Server 2005, any and all database objects no longer belong to a user to belong to a Schema. Thus, the Schema is the association bridge between the user (or group of users) and one(or more) object (s) of database.
What are the advantages of using it?
The first advantage is the permission of users and groups. When a user is authorized in a Schema, they can be authorized in multiple databases at the same time whose objects are included within a Schema.
The second advantage is the physical grouping of the data. Some servers, such as the latest versions of SQL Server, allow objects from the same Schema, even if they are divided into multiple bases of data, be physically grouped for backup and load administration.
In what situations is its use recommended?
In databases with multiple databases (having multiple systems, for example) and it is necessary to authorize or revoke users and groups quickly.
In databases whose permissioning is an essential feature for Data Security (old client-server systems, for example, that rely heavily on the database for implementation of business rules).
In segmentation of data by user group. You can have two tables with the same name in the same database, each in a different Schema.
What are schemes?
Is a container that can contain multiple objects. They are used to manage and organize database objects. Can you logically separate procedures, views, triggers, sequences and etc. Objects become part of the schema, so permissions are applied to the schemas, so you can give permissions to users so that they only access the objects they have permission for in a more organized.
Organization
User X has access to all company-related objects, in this case you would have to apply permissions to all objects he has access to separately, example:
In the drawing below, user X would have access to the employees and departments Tables:
Using schema to organize, you would give permission to user X in schema Company, example:
Schemas provide the opportunity to simplify Security Administration, backup / Restore and database management by allowing database objects, or entities, to be logically grouped.
Source: SQL Server Best Practices-Implementation of Database Object Schemas