2009年12月8日 星期二

MS SQL Role and User security

SQL server 兩種登入方式(二選一) :
1. Window 網域帳號登入,歸Window 作業系統
2. SQL server 帳號登入,SQL server 管理(Server roles are usually reserved for database and server administrators.)
3. Windows Authentication is the recommended security model when using SQL Server.

安全分等:
  • Login security—Connecting to the server (登入 Server)
  • Database security—Getting access to the database (存取DB)
  • Database objects—Getting access to individual database objects and data(存取DB的某一個object/data)

Role types:
1.Server Role -DBA 維護,管理整台server
2.Database Role - 管理單一的database
3.public role 只要一加入 SQL server,都有public role

Database Role(Predefine):
  • db_owner: Members have full access.
  • db_accessadmin: Members can manage Windows groups and SQL Server logins.
  • db_datareader: Members can read all data.
  • db_datawriter: Members can add, delete, or modify data in the tables.
  • db_ddladmin: Members can run dynamic-link library (DLL) statements.
  • db_securityadmin: Members can modify role membership and manage permissions.
  • db_bckupoperator: Members can back up the database.
  • db_denydatareader: Members can’t view data within the database.
  • db_denydatawriter: Members can’t change or delete data in tables or views.

Server Role(Predefine ):
  • SysAdmin: Any member can perform any action on the server.
  • ServerAdmin: Any member can set configuration options on the server.
  • SetupAdmin: Any member can manage linked servers and SQL Server startup options and tasks.
  • Security Admin: Any member can manage server security.
  • ProcessAdmin: Any member can kill processes running on SQL Server.
  • DbCreator: Any member can create, alter, drop, and restore databases.
  • DiskAdmin: Any member can manage SQL Server disk files.
  • BulkAdmin: Any member can run the bulk insert command.





沒有留言: