Use Fixed Database Roles

Sử dụng cơ sở dữ liệu cố định vai trò tài khoản cơ sở dữ liệu Tạo và quyền phân công rõ ràng là rất nhiều công việc. Khi bạn xem xét rằng SQL Server cài đặt dịch vụ nhất hàng chục đến hàng trăm người sử dụng
Nội dung trích xuất từ tài liệu:
Use Fixed Database Roles 11.11 Use Fixed Database RolesCreating database accounts and assigning permissions is obviously a lot of work. Whenyou consider that most SQL Server installations service dozens to hundreds of users, itseasy to see that a considerable amount of time and effort goes into administering databasesecurity. All in all, creating and managing a database security scheme is one of the mosttime- and effort-intensive tasks of any database administrator.Fortunately, SQL Server provides a tool to considerably lighten this administrative load.As youll see in this section, SQL Server makes it easy to assign predefined permissionsto groups of users rather than individually handling each user.Setting up database object permissions for individual users is a hassle. Every time a useraccount is added to SQL Server, the permissions on database objects must be set for theuser. Id like to be able to minimize the amount of time spent designing individual objectpermissions for my users.TechniqueSQL Server defines many built-in fixed database roles that grant or deny permissions ondatabase objects. Each fixed database role adds or subtracts permissions on all the tables,stored procedures, or other database objects within the database. A user who is added to afixed database role inherits all the permissions specified by the role. A person can belongto multiple roles, if necessary.StepsA fixed database role is similar in some ways to the fixed server roles discussed earlier inthis chapter. The difference is that fixed database roles determine permissions to performoperations on objects within a single database, whereas fixed server roles specify theadministrative operations that are permitted on all SQL Server databases.SQL Server defines 10 different fixed database roles: • db_owner. As owners of the database, members of this role can perform any task that is granted to the other fixed database roles. The db_owner role includes all administrative, design, and data access permissions. • db_accessadmin. The db_accessadmin role manages the creation of new logins and accounts. These logins and accounts include individual users as well as groups of users. • db_datareader. This role is able to view all data from all tables in the database. • db_datawriter. The db_datawriter role is able to add, update, or delete data from all the tables in the database. • db_ddladmin. This role can modify objects within the database. This means that db_ddladmin users can add or delete tables or modify the design of existing tables. • db_securityadmin. Members of the db_securityadmin role manage security on the database. This means they can add new roles and manage statement and object permissions within the database. • db_backupoperator. This role is responsible for backing up the database. • db_denydatareader. Members of this role are unable to view data in the database. This role is useful for data entry clerks whose job is inputting new data without viewing existing records. • db_denydatawriter. Use this role to prevent users from changing data in the database. This is useful, for instance, for clerical and management staff who are supposed to be able to read, but not update, data. • public. This role is for all users of the database that dont have specifically defined roles or permissions in the database. You can edit the permissions of the public role, but be careful.The db_prefix on each of these roles is significant. Its there to help distinguish betweenfixed server roles (explained earlier in this How-To and discussed in the followingsection) from the fixed database roles explained in this section. 1. Open Enterprise Manager and expand the Northwind databases icon. 2. Locate and expand the Northwind databases Roles icon to display the fixed database roles in Enterprise Managers right pane (see Figure 11.19). Figure 11.19. SQL Server 2000 defines 10 different fixed database roles.3. Right-click on a role (such as db_securityadmin) in the Roles list, and select Properties from the shortcut menu. Youll see the Database Role Properties dialog box (shown in Figure 11.20) open in response. Figure 11.20. Use the Database Role Properties dialog box to assign login accounts to a SQL Servers fixed database role.4. Click on the Add button to open the Add Role Members dialog box (see Figure 11.21). This dialog shows all database accounts that are not currently assigned to the selected role. Figure 11.21. The Add Role Members dialog box shows everyone who is not currently assigned to the selected role. 5. Click on any members youd like to add to the selected role. The list box in the Add Role Members dialog box allows you to select multiple logins at one time. 6. When you are satisfied with your selections, click the OK button to close the Add Role Members dialog box; then close the Database Role Properties dialog box by clicking on its OK button.CommentsThe fixed database roles are not to be confused with the similar fixed server roles. Eachfixed database role applies only to a single database. The members you add to a role areonly able to operate with the role inside of the selected database. Fixed server roles, onthe other hand, affect all databases within SQL Server as well as SQL Server.Therefore, fixed database role security is the ideal way to assign specific permissions ona single database. This can be useful to allow departmental groups within a company tomanage their own databases. Because relatively small amounts of data are influenced byfixed database roles (this depends, of course, on the type and size of the database), it isn ...