Use Object Permissions

Quyền sử dụng đối tượng lúc này, bạn đã thêm SQL Server và đăng nhập tài khoản người dùng thành lập ở mỗi cơ sở dữ liệu trong SQL Server. Trong hầu hết trường hợp, bạn muốn hạn chế truy cập của người dùng để bàn,
Nội dung trích xuất từ tài liệu:
Use Object Permissions 11.10 Use Object PermissionsAt this point, you have added SQL Server logins and established user accounts in eachdatabase within SQL Server. In most situations, you want to restrict the users access totables, views, stored procedures, and other objects within this database. This is done bysetting permissions on these database objects.I do not want every user to have read, write, and update permissions on every table withinthe database. Otherwise, unauthorized users are able to view data that they are notpermitted to see. Often, only certain users are permitted to add new records or to deleteexisting records in a database table. Without object permissions, I have no way ofcontrolling individual user access to the data within a database.TechniqueYoull use the Enterprise Manager dialog boxes to assign permissions on the objectswithin a database. SQL Server provides the following object permissions for tables,views, and stored procedures: • Select. Permission to issue SELECT statements against a table or view to retrieve data. • Insert. Permission that allows the user to execute the INSERT statement to add new records to a table or view. • Update. Permission that allows the UPDATE statement to run, changing the data in a row of a table or view. • Delete. Permission to run the DELETE statement and remove rows from a table or view. • Execute. Permission that allows the user to run stored procedures and functions within the database.StepsA SQL Server database contains a wide variety of database objects, such as tables, views,and stored procedures. A user account can be assigned specific permissions on eachobject in a SQL Server database. These permissions direct SQL Server to allow anaccount to run stored procedures, view and update data that is contained in tables, andperform other database operations.As you click on the individual object permissions, the check box changes from empty to agreen check mark to a red X, as mentioned in the previous How-To. 1. Open Enterprise Manager and expand the Northwind databases icon.2. Expand the Northwind databases Tables icon to display all the tables in Enterprise Managers right pane.3. Right-click on a table in the Tables list, and select Properties from the shortcut menu to open the Table Properties dialog box (see Figure 11.17). Figure 11.17. The Table Properties dialog box displays important information about the table.4. Select the Permissions button in the upper-right corner of the Table Properties dialog box to displaythe Permissions tab (see Figure 11.18). Figure 11.18. The Permissions tab contains all the object permission settings for the table. 5. Select a user or role from the leftmost column, and then click on the check box in any of the Permissions columns.CommentsEach permission on an object has three levels of access: • Grant. SQL Server permits all operations whose permission is set to Grant. • Revoke. The user is unable to perform the operation unless hes been implicitly granted permission through membership in some role (discussed in How-To 11.11) or through a group. Revoke is the default setting for all permissions. • Deny. The user cannot perform a denied operation, even if permission is implicitly granted by role or group membership.Most often, unless the user has a specific need to be granted or denied permission on anobject, youll leave the permission set to Revoke. This means that the permission is notprovided unless the user is given permission through a database role (discussed in thenext section). Generally speaking, it is better to provide too little access to databaseobjects than to grant too much access that could lead to confidentiality or data integrityproblems. This is why the default permission on SQL Server objects is set to Revoke bydefault.