CODEWITHSUNDEEP
sqlpermissions
Aer
Aer

Reputation: 49

sql select permission with conditions

Is it possible to grant select permission on sql table with some conditions.

For example grant select where table.id = < 50

supplemented: i am talking about ms sql

Upvotes: 4

Views: 2214

Answers (2)

morb1d
morb1d

Reputation: 143

Aer, you haven't said anything about DB engine (which one you are using: Oracle / MS SQL Server / MySQL etc.).

In Oracle for instance you are Granting / Revoking privileges to specific object, and there is no way to limit the output result (with out of the box GRANT statement).

In SQL Server you are able to grant row-level permissions for individual users with some additional effort: https://msdn.microsoft.com/en-us/library/bb669076(v=vs.110).aspx

Example: 

CREATE SCHEMA Security  
GO  

CREATE FUNCTION Security.userAccessPredicate(@UserName sysname)  
    RETURNS TABLE  
    WITH SCHEMABINDING  
AS  
    RETURN SELECT 1 AS accessResult  
    WHERE @UserName = SUSER_SNAME()  
GO  

CREATE SECURITY POLICY Security.userAccessPolicy  
    ADD FILTER PREDICATE Security.userAccessPredicate(UserName) ON dbo.MyTable,  
    ADD BLOCK PREDICATE Security.userAccessPredicate(UserName) ON dbo.MyTable  
GO  
    enter code here

CREATE VIEW vw_MyTable  
AS  
    RETURN SELECT * FROM MyTable  
    WHERE UserName = SUSER_SNAME()  
GO

In general from the concept perspective if you want to limit users to only certain amount of rows you should do the following: The basic way would be :

  1. Prevent users (or group) from accessing the base table.
  2. Define a view on top of that table that shows only the rows these users are supposed to see.
  3. Give users SELECT permission on the view.

Upvotes: 0

Harshil Doshi
Harshil Doshi

Reputation: 3592

It's not possible directly.

Though to achieve this task, you can create a View initially with required conditions and then Grant select permission to that particular view.

For more details: mysql GRANT + WHERE

Upvotes: 6

Related Questions