Database Management System Login Name, role, database user, role and architecture

Overview

I wrote about the Server login name, server role, database user, database role relationship, and theoretical knowledge in a previous blog. You can refer to this blog: Login Name, server role, user Name and database role. This blog focuses on operations.

Database architecture

In specific operations, we should first popularize the knowledge of the database architecture. Note that the database architecture mentioned here is not for the database topology structure, but for the database management system, A function for databases.

For example, for the advantages of architecture and architecture, if the database is a building, the architecture is a first floor, and the house on the first floor is a table, the stored procedure generated by the table, trigger and so on all belong to this architecture. The first floor is rented to many people, and these people are tossing in the building. However, after these people leave, they do not need to change their tossing things, later, people can continue to make the effort on the basis of this architecture, because all the things that the predecessors have learned belong to this architecture, and things belong to the architecture rather than people. Therefore, after people leave, things do not need to be changed.

Framework: A container for storing database objects. Objects in the framework must be unique. Objects of different frameworks can have the same name. A database has multiple frameworks. In the previous (SQL 2000) database object access, the server name is used. database Name. user name. table name. The following database access method is used: server name. database Name. framework name. table name. The benefit of this operation is that if a user does not resign, I can directly Delete the user. However, it is not possible before, because database objects are searched by user names, such a modification is very troublesome. Of course, the role of the framework is not just that. For example, the framework limits the user's access to other database framework tables. Well, the theory is over now. Next we will start to practice.

Create Database System Login Name

Create three system login names, Student, Teacher, and Person. The specific operations are as follows:

Create a database

create database DataBaseNamego

Create database architecture

Create three architectures named Teacher_KJ, Student_KJ, and Person_KJ.

Create a database role

Create two database roles. The role names are Teacher_JS and Student_JS, respectively. The architecture names above are displayed. For example, Teacher_JS corresponds to Teacher_KJ. The specific operations are as follows:

Create a database user

Create three users: Teacher_User, Student_User, and Person_User. Teacher_User and Student_User belong to the role and have no specific architecture. Person_User does not belong to the created role and has Person_KJ. The specific operations are as follows:

Create tables in the corresponding Architecture

use DataBaseNamegocreate table [Teacher_KJ].TableName1(id int primary key,name varchar not null)gocreate table [Student_KJ].TableName1(id int primary key,name varchar not null)gocreate table [Person_KJ].TableName1(id int primary key,name varchar not null)gocreate table TableName1(id int primary key,name varchar(20) not null)

Final Result

A graph has a truth, which is the final result graph.

The following is a summary image, which is not drawn by myself, but is very classic.

SQL Server is very powerful. For example, you can use the SQL Agent service, that is, the proxy Server, to perform many other operations, such as jobs, maintenance plans, and sending emails. Of course, before performing such operations, it is best to back up and restore the database (full backup, differential backup, and log backup), because one function of the SQL Agent service is to automate backup and restoration.