Use Statement Permissions

Bản Tuyên Bố Quyền sử dụng cơ sở dữ liệu SQL Server có thể trở nên khá lớn và phức tạp. Điều quan trọng là kiểm soát số lượng và loại đối tượng mà được thêm vào một cơ sở dữ liệu SQL Server để cấu trúc của nó chỉ chứa những đối tượng có thực sự cần thiết cho hoạt động của cơ sở dữ liệu
Nội dung trích xuất từ tài liệu:
Use Statement Permissions 11.9 Use Statement PermissionsSQL Server databases can become quite large and complex. It is important to control thenumber and type of objects that are added to a SQL Server database so that its structurecontains only those objects that are actually required for the databases operation.If object creation were not controlled, users could build temporary tables, views, storedprocedures, and other database objects that would become permanent additions to thedatabase. Theres no easy way to tell whether a particular database object is required bysome front-end applications, making it difficult to remove these objects.I want to control unwanted and unneeded object proliferation in my databases. Withoutsome kind of control, users might unnecessarily complicate the databases structure byadding unwarranted objects.TechniqueYou will employ statement permissions to permit or disallow users to execute SQLstatements that modify the database structure. There are also statement permissionscontrolling SQL statements that back up the database and its log file. These statementsinclude the following: • CREATE DATABASE. Creates a new database (applicable only in the master database). • CREATE DEFAULT. Establishes default values for columns in tables. • CREATE FUNCTION. Creates a user-defined function that is saved as a Transact- SQL routine. • CREATE PROCEDURE. Creates a stored procedure. • CREATE RULE. Adds a rule to a column in a table. A rule specifies the acceptable values for the column. • CREATE TABLE. Creates a new table within the database. • CREATE VIEW. Adds a view to the database. • BACKUP DATABASE. Backs up the entire database to removable media. • BACKUP LOG. Backs up the databases log file to removable media.Although this security task has been discussed only from the perspective of limiting theusers ability to create database objects, statement permissions are also a way to ensurethat users who really need to modify the database structure are able to do so.Statement permissions are applied at the database level. There are no global SQL Serverstatement permissions.StepsLogging in to SQL Server does not mean that a user is actually able to access and use thedata and other objects that are stored in SQL Server. Each user account has certainpermissions assigned to it that specify the accounts ability to use the database and itsobjects.Statement permissions are one category of database permissions. Statement permissionsspecify which types of SQL DDL (data definition language) statements a user is allowedto execute against the database. DLL statements are frequently used to create and modifytables, add indexes to tables, and perform other data structure operations on the database.Statement permissions limit a users ability to perform operations that could be dangerousto the database.After statement permissions have been established, the user is able to execute objectcreation statements (such as CREATE TABLE) only if the statements permission hasbeen granted.By default, SQL Server does not grant statement permissions. As the SQL Server systemadministrator, you should grant these permissions only to users who require objectcreation ability. 1. Open Enterprise Manager and expand the Northwind databases icon. 2. Right-click on the Northwind databases icon and select Properties from the shortcut menu. This action opens the databases Properties dialog box (see Figure 11.16). Figure 11.16. The database Properties dialog box includes settings for statement permissions. 3. Click on the Permissions tab. Notice that the leftmost column contains the roles and logins that you have created within the database. Select a role or login, and check the statement permissions you want to assign.CommentsEach statement permission has three different states: • Revoke. The user is not given the statement permission unless he is a member of a role that has been given the permission. The graphic to depict this state is blank. • Grant. The user is given permission to run the statement. The graphic to depict this state is the check. • Deny. The user is denied the statement permission and cannot run the SQL Statement. The red X depicts this state.Statement permissions are a great way to permit assistants the ability to add tables, views,stored procedures, and other objects as needed. Because the default setting is to denythese permissions, you do not have to worry about unwarranted object proliferation inyour databases.