Row-Level Security?

I was wondering if anyone had links, best practices, anything... on using RLS (Row-Level Security) with SSAS.

I currently use logged in user to do RLS in relational reporting.

Thanks.

Hi Billg,

There really isn't such a thing as RLS in Analysis Services, its Dimension Security, where you limit what Dimension Members a user can see, or Cell Level Security where you limit the individual Cells someone can see. There are several books for SSAS 2005 that describ the security really well. Also search on Dynamic Security and you will get hits on how to setup security in SSAS. Here is a webcast from my friends in support, its old but still applies to 2005.

http://support.microsoft.com/kb/828343

Hope this helps,

David

Row-level Security: Permissions required on base table?

I'm implementing row-level security in a SQL Server database that uses Microsoft Access for the front end. I'm using a UDF (a view behaves the same way) to restrict access to specific rows of a base table based on membership in a role. According to the reading I've done, if the base table has DENY ALL permissions for the role, and the UDF has GRANT ALL, members of the role should be able to update records in the base table via the UDF, without having direct access to the base table. However, I find that unless I grant appropriate permissions on the base table, the user is unable to update the table via the UDF.

Is this expected behavior? Nothing I've read suggests I should have to grant permissions on the columns of the base table.

Yes, that is expected behavior.

Permissions in SQL Server have three values: GRANT, DENY, or 'unsaid'.

If you have been GRANTed permission for a table, obviously you have permission.

If your permission is 'unsaid', then you 'may' still have permission due to permission having been granted to another role that includes you.

But IF you have been explicited DENY(ied), that 'trumps' all.

Think of it this way.

Children have a knack of knowing how to 'scope out' their parents. Perhaps a son wants to go out with friends. He may approach Mom and 'feel her out' to find out if she 'might' say yes WITHOUT directly asking her. He knows that if he asks her and she says 'No', his plans are shot because he cannot then go and ask Dad (DENY). So he will attempt to find out if it is 'safe' to ask her. If it seems safe, he will ask and he's 'home free' (GRANT).

However, if he feels that she would probably say No, then without having asked, he is now free to ask Dad. So Mom was 'unsaid', if permission can be had by another route, it will work for him.

So anytime permission is an explicit DENY, there is no route around it.

Often, in a strong SQL Server security model, TABLE permissions are left 'unsaid', and access is GRANTed through VIEWS, Functions, and Stored Procedures. Users are also added to the db_DenyDataReader and db_DenyDataWriter roles to prohibit them having direct table access.

|||

Thanks for responding.

Ah, yes, that's what I thought. But if I leave the permissions on the base table "unsaid", and grant all on the UDF, Access tells me that the recordset is not updatable (maybe because it can't "see" the PK column?). So I'm back to having to grant permissions in the base table, which is unacceptable.

I read through the good whitepaper on row-level security by Rask, Rubin and Neumann. The architecture they propose is great, but overkill for what I need to do. Nevertheless, I set up a test using their methodology: DENY ALL on the base table, GRANT ALL on a view, and put an INSTEAD OF trigger on the view to verify appropriate access and perform an update. But because Access thinks the recordset isn't updatable, the trigger never fires. If I grant SELECT permissions to just the PK column of the base table, Access thinks the recordset is updatable, but I still can't get the trigger to fire because Access wants at least SELECT permissions on the other base table columns before it will even try to perform the update.

|||

I would suggest the following topics from BOL:

· CREATE VIEW (http://msdn2.microsoft.com/en-us/library/ms187956.aspx), got to the section Updatable Views

· Modifying Data Through a View (http://msdn2.microsoft.com/en-us/library/ms180800.aspx)

I hope this information helps,

-Raul Garcia

SDE/T

SQL Server Engine

|||

In addition to Raul's suggestions, I offer the following insight.

Access will allow you to UPDATE or DELETE a row without the table having a primary key (or some unique identifier.)

SQL Server does NOT allow UPDATES or DELETES unless there is a unambiguous way to be certain what row is being addressed. If the VIEW does not include a PK, or unique identifier, it would not be updatable.

|||Thanks for the references, I'll read through them and see where they lead. I note that these are from SQL Server 2005 BOL, and the server that must host the application I'm working with is SQL Server 2000 SP4. I'm just wondering whether you know whether the support for updatable views changed between SQL 2000 and 2005?|||

As far as I understand, updatable views should be supported in SQL Server 2000 SP4, but I am not 100% sure if all the documentation in the links I included may apply to SQL Server 2000 SP4 as well.

I would recommend trying to find the same topic in BOL fro SQL Server 2000 and giving it a try; if you have any further questions please let us know, we will be glad to help.

Thanks,

-Raul Garcia

SDE/T

SQL Server Engine

|||

Thanks to both you and Arnie for responding to this inquiry.

The problem turns out to have been an interaction between SQL Server and Access. In order for Access to update a SQL Server view, the view must be declared the WITH VIEW_METADATA option. If designing the view in Access, this is accomplished by checking the view option "Update using view rules" on the properties page. Once I did this, I was able to DENY ALL on the base table and GRANT ALL on the view, and the view was updatable via Access with appropriate security.

I ran a SQL Profiler trace to see what Access was sending to SQL Server when the option was properly set--it showed that the UPDATE statement Access generated was against the view, not the base tables. Without using the VIEW_METADATA option, Access does not consider the recordset updatable (hence my original question), so it doesn't generate an UPDATE statement. So I couldn't run a trace to see what happens in that case. I expect that if I could, the UPDATE would be going against the base table rather than the view.

Again, thanks for your help.

Row-level Security: Permissions required on base table?

I'm implementing row-level security in a SQL Server database that uses Microsoft Access for the front end. I'm using a UDF (a view behaves the same way) to restrict access to specific rows of a base table based on membership in a role. According to the reading I've done, if the base table has DENY ALL permissions for the role, and the UDF has GRANT ALL, members of the role should be able to update records in the base table via the UDF, without having direct access to the base table. However, I find that unless I grant appropriate permissions on the base table, the user is unable to update the table via the UDF.

Is this expected behavior? Nothing I've read suggests I should have to grant permissions on the columns of the base table.

Yes, that is expected behavior.

Permissions in SQL Server have three values: GRANT, DENY, or 'unsaid'.

If you have been GRANTed permission for a table, obviously you have permission.

If your permission is 'unsaid', then you 'may' still have permission due to permission having been granted to another role that includes you.

But IF you have been explicited DENY(ied), that 'trumps' all.

Think of it this way.

Children have a knack of knowing how to 'scope out' their parents. Perhaps a son wants to go out with friends. He may approach Mom and 'feel her out' to find out if she 'might' say yes WITHOUT directly asking her. He knows that if he asks her and she says 'No', his plans are shot because he cannot then go and ask Dad (DENY). So he will attempt to find out if it is 'safe' to ask her. If it seems safe, he will ask and he's 'home free' (GRANT).

However, if he feels that she would probably say No, then without having asked, he is now free to ask Dad. So Mom was 'unsaid', if permission can be had by another route, it will work for him.

So anytime permission is an explicit DENY, there is no route around it.

Often, in a strong SQL Server security model, TABLE permissions are left 'unsaid', and access is GRANTed through VIEWS, Functions, and Stored Procedures. Users are also added to the db_DenyDataReader and db_DenyDataWriter roles to prohibit them having direct table access.

|||

Thanks for responding.

Ah, yes, that's what I thought. But if I leave the permissions on the base table "unsaid", and grant all on the UDF, Access tells me that the recordset is not updatable (maybe because it can't "see" the PK column?). So I'm back to having to grant permissions in the base table, which is unacceptable.

I read through the good whitepaper on row-level security by Rask, Rubin and Neumann. The architecture they propose is great, but overkill for what I need to do. Nevertheless, I set up a test using their methodology: DENY ALL on the base table, GRANT ALL on a view, and put an INSTEAD OF trigger on the view to verify appropriate access and perform an update. But because Access thinks the recordset isn't updatable, the trigger never fires. If I grant SELECT permissions to just the PK column of the base table, Access thinks the recordset is updatable, but I still can't get the trigger to fire because Access wants at least SELECT permissions on the other base table columns before it will even try to perform the update.

|||

I would suggest the following topics from BOL:

· CREATE VIEW (http://msdn2.microsoft.com/en-us/library/ms187956.aspx), got to the section Updatable Views

· Modifying Data Through a View (http://msdn2.microsoft.com/en-us/library/ms180800.aspx)

I hope this information helps,

-Raul Garcia

SDE/T

SQL Server Engine

|||

In addition to Raul's suggestions, I offer the following insight.

Access will allow you to UPDATE or DELETE a row without the table having a primary key (or some unique identifier.)

SQL Server does NOT allow UPDATES or DELETES unless there is a unambiguous way to be certain what row is being addressed. If the VIEW does not include a PK, or unique identifier, it would not be updatable.

|||Thanks for the references, I'll read through them and see where they lead. I note that these are from SQL Server 2005 BOL, and the server that must host the application I'm working with is SQL Server 2000 SP4. I'm just wondering whether you know whether the support for updatable views changed between SQL 2000 and 2005?|||

As far as I understand, updatable views should be supported in SQL Server 2000 SP4, but I am not 100% sure if all the documentation in the links I included may apply to SQL Server 2000 SP4 as well.

I would recommend trying to find the same topic in BOL fro SQL Server 2000 and giving it a try; if you have any further questions please let us know, we will be glad to help.

Thanks,

-Raul Garcia

SDE/T

SQL Server Engine

|||

Thanks to both you and Arnie for responding to this inquiry.

The problem turns out to have been an interaction between SQL Server and Access. In order for Access to update a SQL Server view, the view must be declared the WITH VIEW_METADATA option. If designing the view in Access, this is accomplished by checking the view option "Update using view rules" on the properties page. Once I did this, I was able to DENY ALL on the base table and GRANT ALL on the view, and the view was updatable via Access with appropriate security.

I ran a SQL Profiler trace to see what Access was sending to SQL Server when the option was properly set--it showed that the UPDATE statement Access generated was against the view, not the base tables. Without using the VIEW_METADATA option, Access does not consider the recordset updatable (hence my original question), so it doesn't generate an UPDATE statement. So I couldn't run a trace to see what happens in that case. I expect that if I could, the UPDATE would be going against the base table rather than the view.

Again, thanks for your help.

sql

row level security?

Hi,
I need some kind of row level security.
I currently have this by implementing a complete proprietary way and now I
want to migrate this to as much standard functionality as possible - simply
to have less code to test maintain ;)
I'm working with vb6 and vb.net on sql server 2000 running at a windows 2003
server inside an NT4 domain.
I'm planning to use one DACL per row - but I guess in SQL-Server a row has
no ACL that is checked by the server automatically. Our databases caontain a
table "securerows" which contains 1000 to 1000000 rows (depending on the
usage of our products). Access to these rows need to be checked against
domain user accounts on a per row basis (each query result contains only one
row). I want to use DACLs, because I need Groups with inheritance.
Does anybody have some expirience with such a kind of security in sql
server?
Thanks for each reply ;)
SvenHi
You may want to check out:
http://tinyurl.com/jr1p
John
"Sven Erik Matzen" <sven.matzen@.dontspamme.com> wrote in message
news:uTMoEvLYDHA.2256@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I need some kind of row level security.
> I currently have this by implementing a complete proprietary way and now I
> want to migrate this to as much standard functionality as possible -
simply
> to have less code to test maintain ;)
> I'm working with vb6 and vb.net on sql server 2000 running at a windows
2003
> server inside an NT4 domain.
> I'm planning to use one DACL per row - but I guess in SQL-Server a row has
> no ACL that is checked by the server automatically. Our databases caontain
a
> table "securerows" which contains 1000 to 1000000 rows (depending on the
> usage of our products). Access to these rows need to be checked against
> domain user accounts on a per row basis (each query result contains only
one
> row). I want to use DACLs, because I need Groups with inheritance.
> Does anybody have some expirience with such a kind of security in sql
> server?
> Thanks for each reply ;)
> Sven
>|||<<
> I currently have this by implementing a complete proprietary way and now I
> want to migrate this to as much standard functionality as possible -
simply
Unfortunately... that won't be an easy task. SQL Server provides no support
for row level secrity as you've seen. There really isn't an good way to do
it. When it's all said and done... you'll need to use a completely
proprietaty approach such as the one that you've outlined.
--
Brian
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:3f38d3cf$0$18492$ed9e5944@.reading.news.pipex.net...
> Hi
> You may want to check out:
> http://tinyurl.com/jr1p
> John
> "Sven Erik Matzen" <sven.matzen@.dontspamme.com> wrote in message
> news:uTMoEvLYDHA.2256@.TK2MSFTNGP10.phx.gbl...
> > Hi,
> >
> > I need some kind of row level security.
> > I currently have this by implementing a complete proprietary way and now
I
> > want to migrate this to as much standard functionality as possible -
> simply
> > to have less code to test maintain ;)
> > I'm working with vb6 and vb.net on sql server 2000 running at a windows
> 2003
> > server inside an NT4 domain.
> > I'm planning to use one DACL per row - but I guess in SQL-Server a row
has
> > no ACL that is checked by the server automatically. Our databases
caontain
> a
> > table "securerows" which contains 1000 to 1000000 rows (depending on the
> > usage of our products). Access to these rows need to be checked against
> > domain user accounts on a per row basis (each query result contains only
> one
> > row). I want to use DACLs, because I need Groups with inheritance.
> >
> > Does anybody have some expirience with such a kind of security in sql
> > server?
> >
> > Thanks for each reply ;)
> >
> > Sven
> >
> >
>|||Hi John,
Thanks for the URL. I think I will continue to build my own DACL based
approach, because this already provides a GUI and User/Group inheritance.
I'm a little bit surprised that MS is not implementing DACL support for
MS-SQL-Server, but I think it's the same story that caused Enterprise
Manager and also some other MS-Products (like SourceSafe) do not respect MS
guidelines.
May be DACL support is a nice feature suggestion for the next version -
would make the security administration a lot easier, because you are not
forced to learn another security model/gui.
Sven
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:3f38d3cf$0$18492$ed9e5944@.reading.news.pipex.net...
> Hi
> You may want to check out:
> http://tinyurl.com/jr1p
> John
> "Sven Erik Matzen" <sven.matzen@.dontspamme.com> wrote in message
> news:uTMoEvLYDHA.2256@.TK2MSFTNGP10.phx.gbl...
> > Hi,
> >
> > I need some kind of row level security.
> > I currently have this by implementing a complete proprietary way and now
I
> > want to migrate this to as much standard functionality as possible -
> simply
> > to have less code to test maintain ;)
> > I'm working with vb6 and vb.net on sql server 2000 running at a windows
> 2003
> > server inside an NT4 domain.
> > I'm planning to use one DACL per row - but I guess in SQL-Server a row
has
> > no ACL that is checked by the server automatically. Our databases
caontain
> a
> > table "securerows" which contains 1000 to 1000000 rows (depending on the
> > usage of our products). Access to these rows need to be checked against
> > domain user accounts on a per row basis (each query result contains only
> one
> > row). I want to use DACLs, because I need Groups with inheritance.
> >
> > Does anybody have some expirience with such a kind of security in sql
> > server?
> >
> > Thanks for each reply ;)
> >
> > Sven
> >
> >
>|||Hi
Requests for new features can be sent to
SQL Server Wish: sqlwish@.microsoft.com
John
"Sven Erik Matzen" <sven.matzen@.dontspamme.com> wrote in message
news:%232OPTfWYDHA.1004@.TK2MSFTNGP12.phx.gbl...
> Hi John,
> Thanks for the URL. I think I will continue to build my own DACL based
> approach, because this already provides a GUI and User/Group inheritance.
> I'm a little bit surprised that MS is not implementing DACL support for
> MS-SQL-Server, but I think it's the same story that caused Enterprise
> Manager and also some other MS-Products (like SourceSafe) do not respect
MS
> guidelines.
> May be DACL support is a nice feature suggestion for the next version -
> would make the security administration a lot easier, because you are not
> forced to learn another security model/gui.
> Sven
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:3f38d3cf$0$18492$ed9e5944@.reading.news.pipex.net...
> > Hi
> >
> > You may want to check out:
> >
> > http://tinyurl.com/jr1p
> >
> > John
> >
> > "Sven Erik Matzen" <sven.matzen@.dontspamme.com> wrote in message
> > news:uTMoEvLYDHA.2256@.TK2MSFTNGP10.phx.gbl...
> > > Hi,
> > >
> > > I need some kind of row level security.
> > > I currently have this by implementing a complete proprietary way and
now
> I
> > > want to migrate this to as much standard functionality as possible -
> > simply
> > > to have less code to test maintain ;)
> > > I'm working with vb6 and vb.net on sql server 2000 running at a
windows
> > 2003
> > > server inside an NT4 domain.
> > > I'm planning to use one DACL per row - but I guess in SQL-Server a row
> has
> > > no ACL that is checked by the server automatically. Our databases
> caontain
> > a
> > > table "securerows" which contains 1000 to 1000000 rows (depending on
the
> > > usage of our products). Access to these rows need to be checked
against
> > > domain user accounts on a per row basis (each query result contains
only
> > one
> > > row). I want to use DACLs, because I need Groups with inheritance.
> > >
> > > Does anybody have some expirience with such a kind of security in sql
> > > server?
> > >
> > > Thanks for each reply ;)
> > >
> > > Sven
> > >
> > >
> >
> >
>

row level security without views

I want to implement row level security in SQL Server 2005 without
implementing views. The desired effect is firing a query on a table
like "select * from <tablename>" should return only rows which are
accessible to the logged in user. Which rows are accessible will be
decided using some data in another table. This table will have user
login name and one or more IDs associated with it. These IDs will be FK
in the table on which we fire select (or update/delete) query. I need
to device a solution such that the select query will return all rows
from the table which contain the ID that is assigned to the logged in
user. Is it possible in SQL Server 2005 without implmenting views on
each table?
Thanks in advance.
Nikhil.Hi
http://www.microsoft.com/technet/pr...5/multisec.mspx
"Nikhil" <nikhilukidwe@.gmail.com> wrote in message
news:1159253428.573628.105420@.d34g2000cwd.googlegroups.com...
>I want to implement row level security in SQL Server 2005 without
> implementing views. The desired effect is firing a query on a table
> like "select * from <tablename>" should return only rows which are
> accessible to the logged in user. Which rows are accessible will be
> decided using some data in another table. This table will have user
> login name and one or more IDs associated with it. These IDs will be FK
> in the table on which we fire select (or update/delete) query. I need
> to device a solution such that the select query will return all rows
> from the table which contain the ID that is assigned to the logged in
> user. Is it possible in SQL Server 2005 without implmenting views on
> each table?
> Thanks in advance.
> Nikhil.
>|||Thanks Uri,
I have already seen this article. This article explains the
"implementation" of row-level security using existing constructs like
tables,views and roles. I wanted to know if SQL Server 2005 has any
built-in support for the row level security and I think it is not
there.
Please correct me if I am wrong.
Nikhil.
Uri Dimant wrote:[vbcol=seagreen]
> Hi
> http://www.microsoft.com/technet/pr...5/multisec.mspx
>
> "Nikhil" <nikhilukidwe@.gmail.com> wrote in message
> news:1159253428.573628.105420@.d34g2000cwd.googlegroups.com...|||> tables,views and roles. I wanted to know if SQL Server 2005 has any
> built-in support for the row level security and I think it is not
> there.
> Please correct me if I am wrong.
Not , that I 'm aware
"Nikhil" <nikhilukidwe@.gmail.com> wrote in message
news:1159335152.141657.291800@.m7g2000cwm.googlegroups.com...
> Thanks Uri,
> I have already seen this article. This article explains the
> "implementation" of row-level security using existing constructs like
> tables,views and roles. I wanted to know if SQL Server 2005 has any
> built-in support for the row level security and I think it is not
> there.
> Please correct me if I am wrong.
> Nikhil.
> Uri Dimant wrote:
>

row level security in SQL

Hi Gurus, I am looking for row level security in sql
server. I was told that sql enterprise edtion is required
to implement this. can anyone give me some direction of
how this can be done? thanks.This is a multi-part message in MIME format.
--=_NextPart_000_00B3_01C37844.E0892760
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
You don't need EE. Rather, you can create views and grant access to the =views. For example, if you wanted to restrict people's access to just =the rows they added - and you were tracking that information - then you =could have:
create view MyView
as
select
*
from
MyTable
where
CreateUser =3D CURRENT_USER
go
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"yl" <hubrian@.hotmail.com> wrote in message =news:16c601c37865$143b3380$a601280a@.phx.gbl...
Hi Gurus, I am looking for row level security in sql server. I was told that sql enterprise edtion is required to implement this. can anyone give me some direction of how this can be done? thanks.
--=_NextPart_000_00B3_01C37844.E0892760
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

You don't need EE. Rather, you =can create views and grant access to the views. For example, if you wanted to =restrict people's access to just the rows they added - and you were =tracking that information - then you could have:
create view MyView
as
select
=*
from
=MyTable
where
CreateUser ==3D CURRENT_USER
go
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"yl" wrote in message news:16c601c37865$14=3b3380$a601280a@.phx.gbl...Hi Gurus, I am looking for row level security in sql server. I was told =that sql enterprise edtion is required to implement this. can anyone give =me some direction of how this can be done? thanks.

---

--=_NextPart_000_00B3_01C37844.E0892760--|||thank you all very much.
>--Original Message--
>Hi Gurus, I am looking for row level security in sql
>server. I was told that sql enterprise edtion is
required
>to implement this. can anyone give me some direction of
>how this can be done? thanks.
>.
>

Row Level Security Expressions in Yukon

Has row level security expressions made it into Yukon or has it been dropped
? I see its in the white paper but was wondering if it looks like it will ma
ke it at this point in the beta? No I wasn't invited to the beta so I can't
post to that newsgroup. Tha
nks!I can't really discuss what features are in or out of Yukon.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.

row level security and Integration services bulk inserts don't work

Hi i followed Microsofts "Implementing Row-and-Cell-Level Security in Classified Databases Using SQL Server 2005"

this works fine when i insert delete data on a normal script (mangement studio)

my project runs in a SSIS package, different users. i cannot do a bulk insert using OLEDB data Destination i get the following error

An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Conflicting locking hints are specified for table "dbo.tblUniqueLabelMarking". This may be caused by a conflicting hint specified for a view.".
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Conflicting locking hints are specified for table "dbo.tblUniqueLabelMarking". This may be caused by a conflicting hint specified for a view.".
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Conflicting locking hints are specified for table "dbo.tblUniqueLabel". This may be caused by a conflicting hint specified for a view.".
Error: 0xC0209029 at Data Flow Task, OLE DB Destination 1 [1741]: The "input "OLE DB Destination Input" (1754)" failed because error code 0xC020907B occurred, and the error row disposition on "input "OLE DB Destination Input" (1754)" specifies failure on error. An error occurred on the specified object of the specified component.

Are you using the "fast load" data access mode with the "table lock" option checked?|||

Hi Duane.

Access Mode = OpenRowset Using FastLoad

Fast Load Options = TABLOCK,CHECK_CONSTRAINTS

these are the default values in the OLE DB Destination. is this correct?

|||

ajedi2k wrote:

Hi Duane.

Access Mode = OpenRowset Using FastLoad

Fast Load Options = TABLOCK,CHECK_CONSTRAINTS

these are the default values in the OLE DB Destination. is this correct?

Yes, those are the default values.

What type of connection manager are you using in the OLE DB destination?

|||

I'm using a OLE DB connection to an SQL server 2005 DB.

is this what you mean?

|||

my bad,

i was actually inserting to a view, and view had triggers.

must set FastLoadOptions = FIRE_TRIGGERS

works fine

row level security and Integration services bulk inserts don't work

Hi i followed Microsofts "Implementing Row-and-Cell-Level Security in Classified Databases Using SQL Server 2005"

this works fine when i insert delete data on a normal script (mangement studio)

my project runs in a SSIS package, different users. i cannot do a bulk insert using OLEDB data Destination i get the following error

An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Conflicting locking hints are specified for table "dbo.tblUniqueLabelMarking". This may be caused by a conflicting hint specified for a view.".
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Conflicting locking hints are specified for table "dbo.tblUniqueLabelMarking". This may be caused by a conflicting hint specified for a view.".
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Conflicting locking hints are specified for table "dbo.tblUniqueLabel". This may be caused by a conflicting hint specified for a view.".
Error: 0xC0209029 at Data Flow Task, OLE DB Destination 1 [1741]: The "input "OLE DB Destination Input" (1754)" failed because error code 0xC020907B occurred, and the error row disposition on "input "OLE DB Destination Input" (1754)" specifies failure on error. An error occurred on the specified object of the specified component.

Are you using the "fast load" data access mode with the "table lock" option checked?|||

Hi Duane.

Access Mode = OpenRowset Using FastLoad

Fast Load Options = TABLOCK,CHECK_CONSTRAINTS

these are the default values in the OLE DB Destination. is this correct?

|||

ajedi2k wrote:

Hi Duane.

Access Mode = OpenRowset Using FastLoad

Fast Load Options = TABLOCK,CHECK_CONSTRAINTS

these are the default values in the OLE DB Destination. is this correct?

Yes, those are the default values.

What type of connection manager are you using in the OLE DB destination?

|||

I'm using a OLE DB connection to an SQL server 2005 DB.

is this what you mean?

|||

my bad,

i was actually inserting to a view, and view had triggers.

must set FastLoadOptions = FIRE_TRIGGERS

works fine

Row level security - View for all?

Hi there,
I am implementing row level security on a large database (at least I think i
t is large). It is enforced by adding which company submitted the row and w
hich company they are subitting to. The security is enforced by using views
to only return the rows th
e current user is allowed to see according to there user name. What they ca
n do with what they see is determined by which role they are assigned to.
What I am wondering is if I need a view for every table in the database? I
think to be completely secure that I do. But then I think that it is redun
dent as you can't really find anything in some tables without starting from
another. i.e. to find cert
ain attributes of an object you need to fuind the object first.
Any thoughts here would be appreciated,
DenisI am implementating row level security as well and made the decision to have
a view for every table for 2 reasons - simplicity and security. No one will
have direct access to any table - only through a view or stored proc. If y
ou establish a view for eve
ry table, there will be no confusion as to wether to reference a table or vi
ew - always refer to the view.
How are you doing the filtering of data on a per user basis in your view?
"Denis Crotty" wrote:

> Hi there,
> I am implementing row level security on a large database (at least I think it is l
arge). It is enforced by adding which company submitted the row and which company t
hey are subitting to. The security is enforced by using views to only return the ro
ws
the current user is allowed to see according to there user name. What they can do with what
they see is determined by which role they are assigned to.
> What I am wondering is if I need a view for every table in the database? I think
to be completely secure that I do. But then I think that it is redundent as you ca
n't really find anything in some tables without starting from another. i.e. to find
ce
rtain attributes of an object you need to fuind the object first.
> Any thoughts here would be appreciated,
> Denis|||That was my feeling as well for using a view for every table. I just was ba
lking as there are 40+ tables.
I filter by checking SUSER_SNAME() and then using the result in a look up ta
ble for what company they are with.
Denis
"Scott Shearer" wrote:

> I am implementating row level security as well and made the decision to have a vie
w for every table for 2 reasons - simplicity and security. No one will have direct a
ccess to any table - only through a view or stored proc. If you establish a view fo
r e
very table, there will be no confusion as to wether to reference a table or view - always re
fer to the view.[vbcol=seagreen]
> How are you doing the filtering of data on a per user basis in your view?
> "Denis Crotty" wrote:
>
s the current user is allowed to see according to there user name. What they can do with wh
at they see is determined by which role they are assigned to.[vbcol=seagreen]
certain attributes of an object you need to fuind the object first.[vbcol=seagreen]

Row Level Security

Posting again in hopes that someone has a solution..

I've set up a sales report that is by territory. Two tables one of which has

sales detail records and another table with Sales Rep info, including territory and

login.. The two tables are joined by state. What I need to be able to do is schedule

this report to run on Reporting services(Already setup) and only allow the reps

to view a snapshot, don't want anyone executing the report again. Additionally,

I need them to only see the territory that they are responsible for. Does anyone

have a solution for this.

Thx again

Hello,

If I'm understanding correctly, you want to create one master snapshot report, then have each rep only access a certain subset of the data in the snapshot. Is this correct?

Since the snapshots are static, you won't be able to limit down the results further after the snapshot is created. I think what you'll need to do is setup multiple subscriptions, one for each rep. You can specify the rep as a parameter to the report. Then you can send the snapshot of the report to the specific rep.

Hope this helps.

Jarret

|||

Thanks for the suggestion. I think that your suggestion will work, but with over 300 reps, I would rather not create that many subscriptions. There has got to be away. Cognos powerplan allows the view to be created upfront and then when A report goes into that view it further filters the results. You would think I should be able to do that. What about not using the snapshot?

row level security

I am looking for a way to implement row level security on my SQL Server 2005 Express database. Thanks in advance for any input.

Row Level Security Defined

Why do I need row level security?

As an architect, developer, or operator of a computer based information system, you mustprotect the sensitive data that you manage. To protect the data, you must ensure that the users of your system have the authority toperform thefunctions they attempt in your system to view, update, delete, insert, and grant privileges onyour system's data.

Depending on the approach you take, this requirementfor data access securitycan dramatically increase the time and expense associated with building and operating your information system.

Row level security (for purposes of this discussion) is a scheme that enforcesdata access security for the rows in a relational database table via a set ofpermission grants(both read and write)assigned to auser or to agroup of users.

The identity ofa user is established(user authentication)when the user makes a connection to the database. The databasealreadynatively supports access control at the object level (table, view, etc.) and even the table column level, but not at the table row level, making row level security an additional scheme implemented on top of the base database product.

When the row level security scheme is in place and functioning, the userwillonly see the rows to which they have at least read level access. They are onlyable to updateand deletedata ina row if they have write level access to the row. They are only able to insert data in a table if they have been granted insert privileges on the table.

From a development cost perspective, selection of atransparent row level security schemeis extremely important – i.e. theSQLSELECT, INSERT, UPDATE and DELETE statements that run against the tables prior to implementing the row level security scheme continue towork without being modified when the row level security scheme is in place and functioning.

A significant reduction in administrative overhead can be achieved if the row level security scheme supports apermission inheritance model where the rows in a child table automatically inherit the permission grants to the parent row in the parent table. This pattern can be repeated for grand children, great grandchildren, etc. This feature makes it possible to create a grant to a single row in a parent table and automatically grant access to all of the appropriate rows in child tables, grandchild tables, etc.

This type of permission control is referred to asjoin-based security because the permissions for a particular row in a table are determined using relational join criteria to locate and inherit permissions from related rows in another table.

Maintainingand operating the information system is greatly simplified ifdata access securityis enforced by definingpermissiongrants to agroups of users (instead of directly assigning the grants to individual users). This separatespermissions configuration andmembership configuration into two distinct tasks.

You can look athttp://www.datanomad.com orhttp://www.technicalmedia.com for more information on row level security in SQL Server 2005.

Row level security

Hi,
is there a way by which we can provide rowlevel security in sql server2000.
If the above feature is not available can anyone tell me any Work around solutions
thanks
VyasDOn't think there's any built-in support for this.

Use a view?

Row Level Security

I am looking into row level security and found a good article:
Title: Implementing Row- and Cell-Level Security in Classified
Databases Using SQL Server 2005
URL: http://www.microsoft.com/technet/prodtechnol/sql/2005/multisec.mspx
Is this still a good approach to take for a secure database? If not,
what would you recommend?
Are you aware of any sample project and DB that implements the above
approach?
ThanksHi,
as there is no row-level security bya default in SQL Server you will always
have to implement it for yourself. Implementation which I have seen
implemeted row level security (using the SUser_Name) with functions like
suser_Name() up to solution with passing hashes or certificates to SQL
Server. Row Level security is still an appropiate solution for making a
granular access to your data.
HTH, Jens K. Suessmeyer.
--
http://www.sqlserver2005.de
--
<hufaunder@.yahoo.com> wrote in message
news:1173046321.597432.77820@.n33g2000cwc.googlegroups.com...
>I am looking into row level security and found a good article:
> Title: Implementing Row- and Cell-Level Security in Classified
> Databases Using SQL Server 2005
> URL: http://www.microsoft.com/technet/prodtechnol/sql/2005/multisec.mspx
> Is this still a good approach to take for a secure database? If not,
> what would you recommend?
> Are you aware of any sample project and DB that implements the above
> approach?
> Thanks
>

ROW LEVEL SECURITY

How can I implement "Row Level Security" in SQL Server 2000?

Thanks alot.Generally speaking, you can create views to horizontally partition data
based on your security requirements. The views filter data so that only
authorized users can access data. Do not allow direct access to the
underlying tables and grant user permissions only on views.

The script below illustrates this technique to implement security so that
users can only access employee data for those departments they are allowed
to see, based on their login and entries in the SecurityByDepartment table.
This approach can be extended to include application-defined roles in order
to reduce security administration.

SET NOCOUNT ON
GO

CREATE TABLE Employees
(
EmployeeId int NOT NULL
CONSTRAINT PK_Employee
PRIMARY KEY NONCLUSTERED,
DepartmentId int NOT NULL,
SomeData varchar(30)
)
CREATE CLUSTERED INDEX Employees_cdx ON Employees(DepartmentId)
GO

CREATE TABLE SecurityByDepartment
(
UserName sysname NOT NULL,
DepartmentId int NOT NULL,
CONSTRAINT PK_SecurityByDepartment
PRIMARY KEY (UserName, DepartmentId)
)
GO

CREATE VIEW MyEmployees
AS
SELECT
e.EmployeeId,
e.DepartmentId,
e.SomeData
FROM Employees e
JOIN SecurityByDepartment sbd ON
sbd.UserName = SUSER_SNAME() AND
sbd.DepartmentId = e.DepartmentId
GO

EXEC sp_addrole 'MyRole'
GRANT SELECT ON MyEmployees TO MyRole
GO

INSERT INTO Employees VALUES(1, 1, 'some data 1')
INSERT INTO Employees VALUES(2, 1, 'some data 2')
INSERT INTO Employees VALUES(3, 1, 'some data 3')
INSERT INTO Employees VALUES(4, 2, 'some data 4')
INSERT INTO Employees VALUES(5, 2, 'some data 5')
GO

INSERT INTO SecurityByDepartment VALUES('Login1' ,1)
INSERT INTO SecurityByDepartment VALUES('Login1' ,2)
INSERT INTO SecurityByDepartment VALUES('Login2' ,1)
INSERT INTO SecurityByDepartment VALUES('Login3' ,2)
GO

EXEC sp_addlogin 'Login1'
EXEC sp_grantdbaccess 'Login1'
EXEC sp_addrolemember 'MyRole', 'Login1'
EXEC sp_addlogin 'Login2'
EXEC sp_grantdbaccess 'Login2'
EXEC sp_addrolemember 'MyRole', 'Login2'
EXEC sp_addlogin 'Login3'
EXEC sp_grantdbaccess 'Login3'
EXEC sp_addrolemember 'MyRole', 'Login3'
GO

PRINT SUSER_SNAME()
SELECT * FROM MyEmployees
GO

SETUSER 'Login1'
PRINT SUSER_SNAME()
SELECT * FROM MyEmployees
GO

SETUSER
SETUSER 'Login2'
PRINT SUSER_SNAME()
SELECT * FROM MyEmployees
GO

SETUSER
SETUSER 'Login3'
PRINT SUSER_SNAME()
SELECT * FROM MyEmployees
GO

SETUSER
GO

DROP VIEW MyEmployees
DROP TABLE Employees
DROP TABLE SecurityByDepartment
EXEC sp_revokedbaccess 'Login1'
EXEC sp_droplogin 'Login1'
EXEC sp_revokedbaccess 'Login2'
EXEC sp_droplogin 'Login2'
EXEC sp_revokedbaccess 'Login3'
EXEC sp_droplogin 'Login3'
EXEC sp_droprole 'MyRole'
GO

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Elham.Ghoddousi" <elham_gh@.yahoo.com> wrote in message
news:4c3908a6.0409040503.43a24c3d@.posting.google.c om...
> How can I implement "Row Level Security" in SQL Server 2000?
> Thanks alot.

Row Level Security

I am looking into row level security and found a good article:
Title: Implementing Row- and Cell-Level Security in Classified
Databases Using SQL Server 2005
URL: http://www.microsoft.com/technet/pr...5/multisec.mspx
Is this still a good approach to take for a secure database? If not,
what would you recommend?
Are you aware of any sample project and DB that implements the above
approach?
ThanksHi,
as there is no row-level security bya default in SQL Server you will always
have to implement it for yourself. Implementation which I have seen
implemeted row level security (using the SUser_Name) with functions like
suser_Name() up to solution with passing hashes or certificates to SQL
Server. Row Level security is still an appropiate solution for making a
granular access to your data.
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
--
<hufaunder@.yahoo.com> wrote in message
news:1173046321.597432.77820@.n33g2000cwc.googlegroups.com...
>I am looking into row level security and found a good article:
> Title: Implementing Row- and Cell-Level Security in Classified
> Databases Using SQL Server 2005
> URL: http://www.microsoft.com/technet/pr...5/multisec.mspx
> Is this still a good approach to take for a secure database? If not,
> what would you recommend?
> Are you aware of any sample project and DB that implements the above
> approach?
> Thanks
>

Row Level Security

Hello,
I want to implement row level security using sql server reporting
services.
I noticed another post asking the same question and the same subject
line, but it recieved no proper answer.
My situation is the same as his - I need to seperate data according to
the client user name, from the same table.
The internet system i am building, will need to handle many users,
which can't see each other's records, but their data is stored at the
same table.
Here is a link to the other post on the same subject, i find it not
relevant to post the same thing twice.
http://groups-beta.google.com/group/microsoft.public.sqlserver.reportingsvcs/messages/110300aa490910ed,de31f976623b6c89,3c78bd588c6aba03,f67dcd82d3646a3a,4761bb89bab82df9,448882c85246ac99?thread_id=f623781fb429616f&mode=thread&noheader=1&q=row+security#doc_110300aa490910ed
Thanks in advance,
ShlomoCan you use User!UserID.Value as a join key instead of Suser_Sname() as you
might do in T-SQL
I haven't test User! so I do not know whose id it is but I beleive it is
intended to be the report requestor (when using trusted security)
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
<shlomoid@.gmail.com> wrote in message
news:1108300806.467922.236190@.o13g2000cwo.googlegroups.com...
> Hello,
> I want to implement row level security using sql server reporting
> services.
> I noticed another post asking the same question and the same subject
> line, but it recieved no proper answer.
> My situation is the same as his - I need to seperate data according to
> the client user name, from the same table.
> The internet system i am building, will need to handle many users,
> which can't see each other's records, but their data is stored at the
> same table.
> Here is a link to the other post on the same subject, i find it not
> relevant to post the same thing twice.
> http://groups-beta.google.com/group/microsoft.public.sqlserver.reportingsvcs/messages/110300aa490910ed,de31f976623b6c89,3c78bd588c6aba03,f67dcd82d3646a3a,4761bb89bab82df9,448882c85246ac99?thread_id=f623781fb429616f&mode=thread&noheader=1&q=row+security#doc_110300aa490910ed
> Thanks in advance,
> Shlomo
>|||Thanks alot for the answer, this really does seem like something that
will prove to be usefull.
I have but another extension to this question:
The reporting services server does not provide authentication, only
authorization (as the rs books online state).
That means, that i need to create a user on the machine, a real windows
user, for each internet user i want to recieve some sort of
authorization.
This is of course a source of many problems, such as kinds of security
issues.
What is the common practice in this scenario?
How can i implement my own authentication mechanizm? And should I?
Thanks again,
Shlomo

Row based security

Hi,
Are there any new feature for restricting users access to particular rows in
SQL Server 2005?
Thanks in advance,
LeilaLeila
I have not used it by myself if I remember well there is an option that
allows you to encrypt a row or a column.
"Leila" <Leilas@.hotpop.com> wrote in message
news:e7Nw6uFDGHA.2704@.TK2MSFTNGP11.phx.gbl...
> Hi,
> Are there any new feature for restricting users access to particular rows
> in SQL Server 2005?
> Thanks in advance,
> Leila
>|||Go to:
http://support.microsoft.com/search/?adv=1
and for "Search Product" select "SQL Server". In the "For" box enter
"row-level security". In the "Categories" select and unselect the options
according to your needs and such.
Does that help?
"Leila" <Leilas@.hotpop.com> wrote in message
news:e7Nw6uFDGHA.2704@.TK2MSFTNGP11.phx.gbl...
> Hi,
> Are there any new feature for restricting users access to particular rows
> in SQL Server 2005?
> Thanks in advance,
> Leila|||Consider adding a user name column to the table and then using the
SYSTEM_USER function in a view of the table. Only allow users to access the
view, not the table.
E.g.:
Table definition:
Col1 : Col2 : ... : UserName (default=system_user)
View definition:
select Col1, Col2, ...
from <table>
where (UserName = system_user)
This way each user only sees his/her own rows.
ML
http://milambda.blogspot.com/|||Use VIEWS that have a WITH CHECK OPTION.|||Please see http://www.technicalmedia.com for row level security extension to
SQL 2005 (free eval, $100 dev license, no runtimes)
Row-Level Security for Microsoft SQL Server 2005
========================================
=====
Data Nomad? is an affordable set of developer tools that extend the
Microsoft SQL Server 2005 platform to provide row-level security and remote
access features allowing developers to accurately and efficiently create and
manage powerful distributed applications that insure access to information i
s
protected.
Developers of .NET 1.1 and .NET 2.0 smart client and web applications can
now easily add row-level security to database applications through the Data
Nomad? developer tools. Existing databases are easily configured by
identifying the tables to be protected and by creating row-level permission
grants.
The same (unmodified) SQL statements work against the Nomad database
extensions. The extended database appears to only contain the rows to which
the user has at least read permissions. Database updates and deletes only
succeed against rows to which the user has owner permissions.
This type of seamless integration is achieved by leveraging two powerful new
features of Microsoft SQL Server 2005: the schema (a collection of database
objects that form a single namespace) and the synonym (an alternative name
for another database object providing a layer of abstraction over the
original object).
The Nomad extensions support both SQL Server authentication and Integrated
NT authentication for database connections, and support local, LAN-connected
,
and Web-connected backend databases.
"Leila" wrote:

> Hi,
> Are there any new feature for restricting users access to particular rows
in
> SQL Server 2005?
> Thanks in advance,
> Leila
>
>

Row based security

I have a database system that contains 1 category of data.
There are plans in the future to add other categories of data as well.
Some users of the database can have access to 1 or more of the categories of
data, and other users can only have access to 1 category, and non of the
other categories of data.
I have a solution in place for row level security using stored procedures
and lookup tables to define what data can be viewed by what users.
In the Oracle world, row based security is handled, but in SQL Server there
is no mechanism in place within the security model to accommodate for it.
What are some approaches in SQL Server that others have used to accommodate
for Row level security issues?
Simon WorthCheck this link for an approach on how do implement row level security in SQ
L
Server:
http://vyaskn.tripod.com/ row_level...as
es.htm
-Sue
"Simon Worth" wrote:

> I have a database system that contains 1 category of data.
> There are plans in the future to add other categories of data as well.
> Some users of the database can have access to 1 or more of the categories
of
> data, and other users can only have access to 1 category, and non of the
> other categories of data.
> I have a solution in place for row level security using stored procedures
> and lookup tables to define what data can be viewed by what users.
> In the Oracle world, row based security is handled, but in SQL Server ther
e
> is no mechanism in place within the security model to accommodate for it.
> What are some approaches in SQL Server that others have used to accommodat
e
> for Row level security issues?
> --
> Simon Worth
>
>|||Thanks Sue,
I've read that article before, and that's actually were I developed some
ideas from.
I was just wondering what others have done in the past, or are planning on
implementing in the future.
Just curiosity I guess. Plus, once in awhile you get an answer that really
makes sense that you hadn't thought of before.
Simon Worth
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:0D4434BE-321E-4A38-87BF-2D08A238D35F@.microsoft.com...
> Check this link for an approach on how do implement row level security in
SQL[vbcol=seagreen]
> Server:
> http://vyaskn.tripod.com/ row_level...as
es.htm
> -Sue
> "Simon Worth" wrote:
>
categories of[vbcol=seagreen]
procedures[vbcol=seagreen]
there[vbcol=seagreen]
it.[vbcol=seagreen]
accommodate[vbcol=seagreen]

Row based security

Hi,
Are there any new feature for restricting users access to particular rows in
SQL Server 2005?
Thanks in advance,
LeilaLeila
I have not used it by myself if I remember well there is an option that
allows you to encrypt a row or a column.
"Leila" <Leilas@.hotpop.com> wrote in message
news:e7Nw6uFDGHA.2704@.TK2MSFTNGP11.phx.gbl...
> Hi,
> Are there any new feature for restricting users access to particular rows
> in SQL Server 2005?
> Thanks in advance,
> Leila
>|||Go to:
http://support.microsoft.com/search/?adv=1
and for "Search Product" select "SQL Server". In the "For" box enter
"row-level security". In the "Categories" select and unselect the options
according to your needs and such.
Does that help?
"Leila" <Leilas@.hotpop.com> wrote in message
news:e7Nw6uFDGHA.2704@.TK2MSFTNGP11.phx.gbl...
> Hi,
> Are there any new feature for restricting users access to particular rows
> in SQL Server 2005?
> Thanks in advance,
> Leila|||Consider adding a user name column to the table and then using the
SYSTEM_USER function in a view of the table. Only allow users to access the
view, not the table.
E.g.:
Table definition:
Col1 : Col2 : ... : UserName (default=system_user)
View definition:
select Col1, Col2, ...
from <table>
where (UserName = system_user)
This way each user only sees his/her own rows.
ML
http://milambda.blogspot.com/|||Use VIEWS that have a WITH CHECK OPTION.|||Please see http://www.technicalmedia.com for row level security extension to
SQL 2005 (free eval, $100 dev license, no runtimes)
Row-Level Security for Microsoft SQL Server 2005
========================================
=====
Data Nomad? is an affordable set of developer tools that extend the
Microsoft SQL Server 2005 platform to provide row-level security and remote
access features allowing developers to accurately and efficiently create and
manage powerful distributed applications that insure access to information i
s
protected.
Developers of .NET 1.1 and .NET 2.0 smart client and web applications can
now easily add row-level security to database applications through the Data
Nomad? developer tools. Existing databases are easily configured by
identifying the tables to be protected and by creating row-level permission
grants.
The same (unmodified) SQL statements work against the Nomad database
extensions. The extended database appears to only contain the rows to which
the user has at least read permissions. Database updates and deletes only
succeed against rows to which the user has owner permissions.
This type of seamless integration is achieved by leveraging two powerful new
features of Microsoft SQL Server 2005: the schema (a collection of database
objects that form a single namespace) and the synonym (an alternative name
for another database object providing a layer of abstraction over the
original object).
The Nomad extensions support both SQL Server authentication and Integrated
NT authentication for database connections, and support local, LAN-connected
,
and Web-connected backend databases.
"Leila" wrote:

> Hi,
> Are there any new feature for restricting users access to particular rows
in
> SQL Server 2005?
> Thanks in advance,
> Leila
>
>