Database, role, user, security

Log on to SQL Server 2008 and use Windows authentication or SQL Server Authentication.

They all have Super User Permissions, which is obviously not safe. A reasonable practice is: For a database, users with different permissions should

Operations and Management.

This blog will use an example to explain how to create a database and create users with different permissions for the database.

1. log on to the database management system (first, enable the SQL Server database service)

1. Log On with Windows Authentication

2. Log On with SQL Server Authentication. The default logon name is Sa. If the password is forgotten, log on with windows and enter

Change. Security-> login name-> double-click Sa-> set password.

 

2. Create a database Library

1. Database-> Create a database

2. Enter the Database Name

 

3. create basic tables for the database Library: Student, book, and borrow. And import a certain amount of data.

 

4. Create a user.

For the sake of simplicity, only two users-student_user and administrator user (admin_user) are created ). Learn

The user can only query (select), while the administrator can query (select), update, insert, and delete ).

1. Create a login name without specifying a user

① Student user

 
Use librarygocreate login Stu with Password = '000000'; Use librarygocreate user student_user for login Stu;
 

② Administrator user

 
Use librarygocreate login Stu with Password = '000000'; Use librarygocreate user student_user for login Stu;
 

2. role creation is not authorized

① Student role

 
Create role student_role; grant select on book to student_role; grant select on borrow to student_role; grant select on student to student_role;
 

② Administrator role

 
Create role admin_role; grant select, update, delete, insert on book to admin_role; grant select, update, delete, insert on borrow to admin_role; grant select, update, delete, insert on student to admin_role;
 

 

3. Grant the role to the user. Student_role is granted to student_user, and admin_role is granted to admin_user.

① Student_role grant sthdent_user

 
Exec sp_addrolemember @ rolename = 'student _ role', @ membername = 'student _ user ';
 

② Admin_role is granted to admin_user

Exec sp_addrolemember @ rolename = 'admin _ role', @ membername = 'admin _ user ';
 

5. Log On with Stu and Admin respectively. You can find that after logging on to Stu, you can only query the basic table.

You can also insert, delete, and update data. As described in the blog post, users with different permissions perform different operations

Make the database more secure.

 

Vi. References

Http://wenku.baidu.com/view/c5bf5c3eee06eff9aef80787.html

Http://blog.163.com/jackie_howe/blog/static/19949134720122261121214/

Http://hi.baidu.com/qcomdd/item/b141a7e0d2e2a1f12a09a4a9
Http://www.verydemo.com/demo_c288_i24021.html