Grant Permission to a table with restriction to data
-
Is it possible to grant access to specific columns in a table with restriction to the data with a where clause? Similar to building a view but doing it through Grant.
-
Jan Sorenson (10/19/2012)
Is it possible to grant access to specific columns in a table with restriction to the data with a where clause? Similar to building a view but doing it through Grant.Are you asking if you can use permissions to prevent somebody from viewing a column based on the contents? Like allow somebody to view some rows but not others because the contents in the row are not allowed? Or do you mean to allow someone to see all the rows but only the contents of some columns based on the contents?
Either way, there is a way to do that, you identified it already, a view.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
I want to only allow a user to view certain columns in a table from SSMS, and well as restrict them to only those row with a certian department code. I know I can do this through a view, but was hoping to not have to create a view because I would then have to name the view something different than the table name. I am guessing that you cannot do this through the GRANT function?
-
how can we restricted a view to a particular user only?
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/ -
kapil_kk (10/22/2012)
how can we restricted a view to a particular user only?GRANT select on SomeView to SomeUser
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
kapil_kk (10/22/2012)
how can we restricted a view to a particular user only?you can do that either by checking the suser_name(), or comparing it agasint a table of permissions you create.
a basic examples:
SELECT
'ColumnList',
OBJECT_ID,
CASE
WHEN suser_name() IN( 'disney\lizaguirre','ClarkKent','Bob' )
THEN ''
ELSE name
END As name from sys.columns
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
I think we can create a view than we can
assign permission to a particular user for that view
GRANT SELECT ON view-name TO user-name
GRANT VIEW DEFINITION ON view_name TO user-name
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply