仅创建过程权限(Create Procedure Permission ONLY)

仅创建过程权限(Create Procedure Permission ONLY)

我在开发数据库中有SQL Server 2008的需求

只有DBA（数据库所有者）可以创建，更改表。 开发人员不应该创建或更改表格。 开发人员可以创建/更改dbo模式中的存储过程/用户定义函数，并可以执行SP / UDF。 开发人员应该在表上使用SELECT，INSERT，DELETE，UPDATE（dbo schema中的表）

如何使用GRANT语句来实现这一点

---

从Google找到一个样本解决方案，但仍然存在问题

CREATE LOGIN testdev WITH PASSWORD = 'sldkjlkjlkj 987kj//' CREATE USER testdev GRANT ALTER ON SCHEMA::dbo TO testdev GRANT CREATE PROCEDURE TO testdev GRANT SELECT, INSERT, UPDATE, DELETE ON SCHEMA::dbo TO testdev CREATE TABLE mysig (a int NOT NULL) EXECUTE AS USER = 'testdev' go CREATE PROCEDURE slaskis AS PRINT 12 go CREATE TABLE hoppsan(a int NOT NULL) -- FAILS! go INSERT mysig (a) VALUES(123) go REVERT go DROP PROCEDURE slaskis DROP TABLE mysig DROP USER testdev DROP LOGIN testdev

上面的语法能够阻止开发人员创建表，但不能阻止开发人员使用SSMS设计并更改表。

谢谢。

I have a requirement in SQL Server 2008 in development database

Only DBA's ( who are database owners ) can create, alter tables . Developer's should not create or alter tables . Developers can create/alter Stored Procedure/User Defined functions in dbo schema and can execute SP/UDF. Developers should have SELECT,INSERT,DELETE,UPDATE on tables ( tables in dbo schema

How to achieve this using GRANT statement

---

Found a sample solution from Google, but still have issue

CREATE LOGIN testdev WITH PASSWORD = 'sldkjlkjlkj 987kj//' CREATE USER testdev GRANT ALTER ON SCHEMA::dbo TO testdev GRANT CREATE PROCEDURE TO testdev GRANT SELECT, INSERT, UPDATE, DELETE ON SCHEMA::dbo TO testdev CREATE TABLE mysig (a int NOT NULL) EXECUTE AS USER = 'testdev' go CREATE PROCEDURE slaskis AS PRINT 12 go CREATE TABLE hoppsan(a int NOT NULL) -- FAILS! go INSERT mysig (a) VALUES(123) go REVERT go DROP PROCEDURE slaskis DROP TABLE mysig DROP USER testdev DROP LOGIN testdev

The syntax above able to block developer to create table but cant block developer to use SSMS design and alter the table.

Thanks.

最满意答案

首先，我会使用角色，而不是直接授予用户访问权限。 你可能已经这样做了，但我想我会提到它。

好的，这里的问题是授予ALTER到Schema意味着被授予者拥有对模式中所有对象类型的ALTER访问权限。 不幸的是，据我所知，没有办法授予特定对象类型的权限，因此它是全部或全部。 相反，您不能将ALTER授予所有对象，然后拒绝ALTER到特定的对象类型。

我发现要做到这一点的唯一方法是将ALTER授予模式，然后使用DDL触发器来控制角色可以执行的操作。

以下是您的示例演示原理的更新版本：

--** Create a Developer Role CREATE ROLE [Developer] AUTHORIZATION db_securityadmin; GO --** Grant view and execute on all SPs to Devloper --GRANT VIEW DEFINITION ON SCHEMA::dbo TO [Developer]; GRANT CREATE PROCEDURE TO [Developer]; GRANT SELECT, INSERT, UPDATE, DELETE, ALTER, EXECUTE, VIEW DEFINITION ON SCHEMA::dbo TO [Developer] --** Create user and login for testdev and add to the Developer role CREATE LOGIN testdev WITH PASSWORD = 'sldkjlkjlkj987kj' CREATE USER testdev EXEC sp_addrolemember @rolename = 'Developer', @membername = 'testdev'; GO --** Create DDL trigger to deny drop and alter to the Developer role CREATE TRIGGER tr_db_DenyDropAlterTable_Dev ON DATABASE FOR DROP_TABLE, ALTER_TABLE AS BEGIN IF IS_MEMBER('Developer') = 1 BEGIN PRINT 'You are not authorized to alter or drop a table.'; ROLLBACK TRAN; END; END; GO --** Testing CREATE TABLE mysig (a int NOT NULL) ; EXECUTE AS USER = 'testdev'; GO CREATE PROCEDURE slaskis AS PRINT 12; GO CREATE TABLE hoppsan(a int NOT NULL); -- FAILS! GO INSERT mysig (a) VALUES(123); GO ALTER TABLE mysig ADD test INT; --** This will fail too GO REVERT; GO DROP PROCEDURE slaskis ; DROP TABLE mysig ; DROP USER testdev; DROP LOGIN testdev; DROP ROLE [Developer]; DROP TRIGGER tr_db_DenyDropAlterTable_Dev on DATABASE;

First of all I would use Roles instead of granting access directly to users. You may be already doing this, but I thought I would mention it.

Okay, the problem here is granting ALTER to the Schema means that the grantee has ALTER access to all object types in the schema. Unfortunately, as far as I know, there is no way to grant permissions to specific object types so it is all or nothing. Conversely, you cannot grant ALTER to all objects and then deny ALTER to specific object types.

The only way I have found to do this is to grant ALTER to the schema and then used a DDL Trigger to control what the role can do.

Here is an updated version of your example demonstrating the principle:

--** Create a Developer Role CREATE ROLE [Developer] AUTHORIZATION db_securityadmin; GO --** Grant view and execute on all SPs to Devloper --GRANT VIEW DEFINITION ON SCHEMA::dbo TO [Developer]; GRANT CREATE PROCEDURE TO [Developer]; GRANT SELECT, INSERT, UPDATE, DELETE, ALTER, EXECUTE, VIEW DEFINITION ON SCHEMA::dbo TO [Developer] --** Create user and login for testdev and add to the Developer role CREATE LOGIN testdev WITH PASSWORD = 'sldkjlkjlkj987kj' CREATE USER testdev EXEC sp_addrolemember @rolename = 'Developer', @membername = 'testdev'; GO --** Create DDL trigger to deny drop and alter to the Developer role CREATE TRIGGER tr_db_DenyDropAlterTable_Dev ON DATABASE FOR DROP_TABLE, ALTER_TABLE AS BEGIN IF IS_MEMBER('Developer') = 1 BEGIN PRINT 'You are not authorized to alter or drop a table.'; ROLLBACK TRAN; END; END; GO --** Testing CREATE TABLE mysig (a int NOT NULL) ; EXECUTE AS USER = 'testdev'; GO CREATE PROCEDURE slaskis AS PRINT 12; GO CREATE TABLE hoppsan(a int NOT NULL); -- FAILS! GO INSERT mysig (a) VALUES(123); GO ALTER TABLE mysig ADD test INT; --** This will fail too GO REVERT; GO DROP PROCEDURE slaskis ; DROP TABLE mysig ; DROP USER testdev; DROP LOGIN testdev; DROP ROLE [Developer]; DROP TRIGGER tr_db_DenyDropAlterTable_Dev on DATABASE;