دستور DENY در SQL Server: تفاوت GRANT، DENY و REVOKE با مثال

در SQL Server سه دستور برای مدیریت مجوزها وجود دارد: GRANT، DENY و REVOKE. بیشتر مبتدی‌ها با GRANT آشنا هستند، اما DENY و تفاوتش با REVOKE باعث سردرگمی (و گاهی خطای امنیتی) می‌شود. این مقاله سه دستور را با یک سناریوی واقعی توضیح می‌دهد.

هر سه دستور در یک نگاه

آماده‌سازی سناریو

یک پایگاه‌داده‌ی منابع انسانی داریم. کاربر ali_app باید بتواند اطلاعات کارمندان را بخواند، اما ستون حقوق (Salary) را نبیند:

CREATE LOGIN ali_app WITH PASSWORD = 'Str0ng!Pass#2026';
GO
USE HrDB;
CREATE USER ali_app FOR LOGIN ali_app;

CREATE TABLE dbo.Employees (
  EmployeeId INT PRIMARY KEY,
  FullName   NVARCHAR(100),
  Department NVARCHAR(50),
  Salary     DECIMAL(12,2)
);

GRANT: دادن اجازه

GRANT SELECT ON dbo.Employees TO ali_app;

حالا ali_app می‌تواند همه‌ی ستون‌های جدول را بخواند. مجوز را می‌توان روی سطح‌های مختلف داد: ستون، جدول، Schema یا کل پایگاه‌داده:

GRANT SELECT ON SCHEMA::dbo TO ali_app;   -- همه‌ی جدول‌های schema
GRANT EXECUTE ON dbo.GetReport TO ali_app;  -- یک Stored Procedure
GRANT INSERT, UPDATE ON dbo.Orders TO ali_app;  -- چند مجوز با هم

DENY: ممنوع کردن

برای پنهان کردن ستون حقوق:

DENY SELECT ON dbo.Employees(Salary) TO ali_app;

حالا اگر ali_app اجرا کند SELECT * FROM dbo.Employees، خطای زیر را می‌گیرد، چون ستون ممنوع در * هم هست:

Msg 230, Level 14
The SELECT permission was denied on the column 'Salary'
of the object 'Employees', database 'HrDB', schema 'dbo'.

ولی اگر ستون‌های مجاز را صریح بنویسد، کار می‌کند:

SELECT EmployeeId, FullName, Department FROM dbo.Employees;  -- موفق

REVOKE: برداشتن مجوز صریح

REVOKE یک GRANT یا DENY قبلی را پاک می‌کند. برای برداشتن همان ممنوعیت ستون:

REVOKE SELECT ON dbo.Employees(Salary) FROM ali_app;

بعد از این، ستون Salary دیگر «ممنوع صریح» نیست و چون هنوز GRANT SELECT سطح جدول وجود دارد، کاربر دوباره حقوق را می‌بیند. اگر آن GRANT هم نبود، کاربر دسترسی نداشت.

تفاوت REVOKE و DENY با یک مثال Role

فرض کن ali_app عضو نقش db_datareader است (که SELECT روی همه‌چیز می‌دهد):

ALTER ROLE db_datareader ADD MEMBER ali_app;

REVOKE SELECT ON dbo.Employees FROM ali_app;  -- فقط مجوز صریح خود کاربر برداشته می‌شود
-- ali_app هنوز از طریق db_datareader می‌تواند Employees را بخواند!

DENY SELECT ON dbo.Employees TO ali_app;      -- حالا واقعاً ممنوع است

آزمایش مجوزها بدون ورود با رمز کاربر

با EXECUTE AS می‌توانی موقتاً هویت یک User را بپذیری و نتیجه را ببینی:

EXECUTE AS USER = 'ali_app';
  SELECT * FROM dbo.Employees;              -- خطا (Salary ممنوع)
  SELECT FullName FROM dbo.Employees;       -- موفق
REVERT;

دیدن مجوزهای یک User

-- مجوزهای صریح (GRANT / DENY) ثبت‌شده برای کاربر
SELECT pr.name AS UserName, pe.permission_name, pe.state_desc,
       OBJECT_NAME(pe.major_id) AS ObjectName
FROM sys.database_permissions pe
JOIN sys.database_principals pr ON pr.principal_id = pe.grantee_principal_id
WHERE pr.name = 'ali_app';

-- مجوزهای مؤثر کاربر فعلی روی یک شیء
SELECT * FROM fn_my_permissions('dbo.Employees', 'OBJECT');

نکته‌های مهم

• DENY روی اعضای sysadmin و مالک پایگاه‌داده (dbo) اثر ندارد.
• DENY را با احتیاط روی نقش public بزن؛ همه‌ی کاربران عضو آن هستند.
• ساده‌ترین طراحی: به‌جای DENY زیاد، فقط حداقل GRANTهای لازم بده (اصل حداقل دسترسی).
• GRANT ... WITH GRANT OPTION به کاربر اجازه می‌دهد همان مجوز را به دیگران بدهد؛ جز در موارد خاص استفاده نکن.
• CASCADE در REVOKE مجوزهایی را که کاربر به دیگران داده هم برمی‌دارد.

جمع‌بندی

GRANT اجازه می‌دهد، DENY قطعاً ممنوع می‌کند و REVOKE مجوز صریح را پاک می‌کند. اگر فقط می‌خواهی دسترسی‌ای را ببندی که از Role می‌آید، DENY لازم است. ترکیب «Role برای گروه‌های کاری + چند DENY برای استثناهای حساس» مدل ساده و قابل‌نگهداری امنیت است. برای ادامه، مقاله‌ی دادن دسترسی به یک Database و درس Permissions را بخوان.