Re: Table Permissions & Row Level Security w/ Views

From: Dan Guzman (guzmanda_at_nospam-online.sbcglobal.net)
Date: 09/08/04 

Date: Wed, 8 Sep 2004 08:42:51 -0500

I suggest you create your views as owned by 'dbo' so that you can use
ownership chains to disallow direct access to the underlying tables. For
example:

CREATE VIEW dbo.Tom_invoices
WITH VIEW_METADATA
AS
SELECT * FROM dbo.invoices WHERE CustID = 35
GO

GRANT SELECT ON dbo.Tom_invoices TO Tom
GO

Note that Tom has not been granted direct permissions on the dbo.invoices in
this example. 

-- 
Hope this helps.
Dan Guzman
SQL Server MVP
"Gerald S" <no@spam.com> wrote in message 
news:MPG.1ba8c3f1e495c178989681@msnews.microsoft.com...
> Hello,
>
> We are adding an Ad-Hoc report writer to our application (Crystal), and
> need to provide row-level security for users using the report writer.
>
> This example references Northwind database in SQL2k.
>
> I need to give user Tom access to table dbo.invoices, for his invoices
> so I create a view:  "Create view tom.invoices as Select * from
> dbo.invoices where CustID = 35".  Tom has a CustID of 35.
>
> Tom owns tom.invoices, so he has permisisons to it for select (and any
> others that we need).  But unless Tom as permissions to table
> dbo.invoices, view tom.invoices does not work.
>
> If Tom has access to table dbo.invoices, then using Crystal (or any ad-
> hoc SQL or report writer) Tom can view all invoices with "Select * from
> dbo.invoices", and not just his invoices.
>
> We use the model of assigning view names the same as the table name,
> qualified by the user:  table dbo.invoices, has views as tom.invoices,
> jim.invoices, etc.  So when we create a report based on "Select * from
> invoices", when Tom is running the report tom.invoices is automatically
> accessed, and when Jim runs is he accesses jim.invoices.  The allows us
> to right 1 report, for all users to run...and is limited to that users
> data.
>
> How can I give Tom access to his invoices through view tom.invoices, but
> all invoices via table dbo.invoices.
>
> Thanks for any help!
> Gerald

Relevant Pages

  • Table Permissions & Row Level Security w/ Views
    ... need to provide row-level security for users using the report writer. ... I need to give user Tom access to table dbo.invoices, ... Tom has a CustID of 35. ...
    (microsoft.public.sqlserver.security)
  • Re: Table Permissions & Row Level Security w/ Views
    ... We are adding an Ad-Hoc report writer to our application, and need to provide row-level security for users using the report writer. ... Tom has a CustID of 35. ... If Tom has access to table dbo.invoices, then using Crystal Tom can view all invoices with "Select * from dbo.invoices", and not just his invoices. ... How can I give Tom access to his invoices through view tom.invoices, but all invoices via table dbo.invoices. ...
    (microsoft.public.sqlserver.security)
  • RE: Question on using ReportToPDF converter
    ... Larry ... "tom" wrote: ... One of the reports I want to convert our invoices. ...
    (microsoft.public.access.reports)
Loading