i'm considering implementing my own artificial key table that would
work similarly to an identity value. insert transactions would first
have to visit this artificial key table, lock the artificial key row
defining the key needed by the data row to be inserted. after the table
lock, it would get the value, insert the record, increment the value,
and update the artificial key table.
the reason i am doing this in a user-defined fashion instead of using
an identity column are varied. first, sometimes the sequence needs to
be semi-random. also, i want to avoid all the issues with migrating
identity columns.
so, i have two questions:
1) assuming that the code and locking are written the the appropriate
levels of granularity, integrity, and speed, is this a reasonable thing
to attempt?
2) all the books i am reading say "trust SQL Server locking management"
-- but i'm guessing they didn't have this kind of use in mind. is this
a circumstance where manual transactional locking is appropriate?
thanks for any suggestions / advice,
jasonI've given this a lot of thought, because IDENTITY doesn't provide a
database-wide unique value and the size of ROWGUIDs hinder performance. I
had considered the possibility of two user-defined functions, one scalar
function that returns a single surrogate value, and one table-valued
function that returns a specified number of values. The main problem with
this scheme is the issue of locking. Either you can obtain the surrogates
prior to starting a transaction, or you have to find another way to deal
with the locks and blocking, or you set up separate identity ranges for each
table. What is needed is a way to serialize access to a "next key" table
outside of the calling transaction. I've thought about writing an extended
stored proceedure or using the sp_OA procs to do this because they can open
or use a separate shared connection, but I haven't had time to pursue the
issue futher, nor am I convinced that the performance will be satisfactory.
Extended procedures are deprecated in SQL Server 2005 because it hosts the
CLR, so anything I write now will probably have to be rewritten later.
"jason" <iaesun@.yahoo.com> wrote in message
news:1122929530.893879.301170@.g44g2000cwa.googlegroups.com...
> i'm considering implementing my own artificial key table that would
> work similarly to an identity value. insert transactions would first
> have to visit this artificial key table, lock the artificial key row
> defining the key needed by the data row to be inserted. after the table
> lock, it would get the value, insert the record, increment the value,
> and update the artificial key table.
> the reason i am doing this in a user-defined fashion instead of using
> an identity column are varied. first, sometimes the sequence needs to
> be semi-random. also, i want to avoid all the issues with migrating
> identity columns.
> so, i have two questions:
> 1) assuming that the code and locking are written the the appropriate
> levels of granularity, integrity, and speed, is this a reasonable thing
> to attempt?
> 2) all the books i am reading say "trust SQL Server locking management"
> -- but i'm guessing they didn't have this kind of use in mind. is this
> a circumstance where manual transactional locking is appropriate?
> thanks for any suggestions / advice,
> jason
>|||jason wrote:
> i'm considering implementing my own artificial key table that would
> work similarly to an identity value. insert transactions would first
> have to visit this artificial key table, lock the artificial key row
> defining the key needed by the data row to be inserted. after the
> table lock, it would get the value, insert the record, increment the
> value, and update the artificial key table.
> the reason i am doing this in a user-defined fashion instead of using
> an identity column are varied. first, sometimes the sequence needs to
> be semi-random. also, i want to avoid all the issues with migrating
> identity columns.
> so, i have two questions:
> 1) assuming that the code and locking are written the the appropriate
> levels of granularity, integrity, and speed, is this a reasonable
> thing to attempt?
> 2) all the books i am reading say "trust SQL Server locking
> management" -- but i'm guessing they didn't have this kind of use in
> mind. is this a circumstance where manual transactional locking is
> appropriate?
> thanks for any suggestions / advice,
> jason
You can do this, but try and keep the key value generation routine in a
separate transaction to prevent tying it to the new inserts themselves.
For example:
Begin Tran
Update dbo.keytest
Set NextKey = NextKey + 1
Select @.key = NextKey From dbo.keytest
Commit Tran
Insert Into dbo.MyTable (newkey) values (@.key)
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Actually you can save a few steps by doing this below. Since the Update is
ATOMIC by itself you don't need a BEGIN TRAN - COMMIT and the extra select.
CREATE TABLE [dbo].[NEXT_ID] (
[ID_NAME] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[NEXT_VALUE] [int] NOT NULL ,
CONSTRAINT [PK_NEXT_ID_NAME] PRIMARY KEY CLUSTERED
(
[ID_NAME]
) WITH FILLFACTOR = 100 ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE PROCEDURE get_next_id
@.ID_Name VARCHAR(20) ,
@.ID int OUTPUT
AS
UPDATE NEXT_ID SET @.ID = NEXT_VALUE = (NEXT_VALUE + 1)
WHERE ID_NAME = @.ID_Name
RETURN (@.@.ERROR)
Andrew J. Kelly SQL MVP
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:u3zEmPvlFHA.3256@.tk2msftngp13.phx.gbl...
> jason wrote:
> You can do this, but try and keep the key value generation routine in a
> separate transaction to prevent tying it to the new inserts themselves.
> For example:
> Begin Tran
> Update dbo.keytest
> Set NextKey = NextKey + 1
> Select @.key = NextKey From dbo.keytest
> Commit Tran
> Insert Into dbo.MyTable (newkey) values (@.key)
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com|||This kind of thing is best done at the raw engine level. if you really
need it, switch products to something with row-level locking. Have you
looked at Firebird, Interbase and other optimistic concurrency control
databases?|||seems reasonable. decreases the lock time, and only costs me an ID
value if the insert fails for some reason. thanks.|||seems like a good implementation, thanks!|||no, i haven't looked at any alternative database products. this
database is thoroughly intwined with a growing .NET middleware, and i
think the company is looking quite enthusiastically at Sql Server 2005,
with the .NET platform built in.
Sql Server does claim to have row-level locking. they just say it is
good to trust the lock manager for most tasks. is Sql Server manual
row-level locking inadequate in some way?
thanks,
jason|||Hi
SQL Server 2000 and 2005 both do full row level locking in their default
configurations.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"jason" <iaesun@.yahoo.com> wrote in message
news:1122989683.046590.261270@.f14g2000cwb.googlegroups.com...
> no, i haven't looked at any alternative database products. this
> database is thoroughly intwined with a growing .NET middleware, and i
> think the company is looking quite enthusiastically at Sql Server 2005,
> with the .NET platform built in.
> Sql Server does claim to have row-level locking. they just say it is
> good to trust the lock manager for most tasks. is Sql Server manual
> row-level locking inadequate in some way?
> thanks,
> jason
>|||by the description of the task at hand, does this sound like something
i would have to employ manual locking to accomplish? or would beginning
a transaction with an update, and then commiting the transaction when
"safe" do all the locking i need?
Showing posts with label implementing. Show all posts
Showing posts with label implementing. Show all posts
Friday, March 30, 2012
Wednesday, March 28, 2012
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.
sqlTuesday, March 20, 2012
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
> > >
> > >
> >
> >
>
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 - 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]
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
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
>
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
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
>
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 and column level permissions.
Dear All,
Any recommendations on best practices for implementing row level and column level permissions in an SQL server database. I did some googling and i could find some recommendations for implementing row level permissions.(I am aware that Yukon is going to ha
ve this feature built-in, am using SQL 2k). All of them were seemed to be specific to applications.
Regarding column level security what concerns me is that, i want this to be applicable for my reporting tool(either crystal or sql reporting services).
Thanks in advance.
regards
Arun
You could use roles and have different views based on the columns you want
each role to see?
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Arun" <ar_kumar@.hotmail.com> wrote in message
news:0C4D56B1-7357-45D1-8EF4-FAE3E8FD5A29@.microsoft.com...
> Dear All,
> Any recommendations on best practices for implementing row level and
column level permissions in an SQL server database. I did some googling and
i could find some recommendations for implementing row level permissions.(I
am aware that Yukon is going to have this feature built-in, am using SQL
2k). All of them were seemed to be specific to applications.
> Regarding column level security what concerns me is that, i want this to
be applicable for my reporting tool(either crystal or sql reporting
services).
> Thanks in advance.
> regards
> Arun
|||Hi i did consider this option, i am worried if i have too many users and too many roles?
|||Maybe the problem is the number of columns? I can understand how you can
have a lot of users, but why would you need a lot of different roles unless
you have 18 billion columns and need a role for every single permutation?
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Arun" <ar_kumar@.hotmail.com> wrote in message
news:0618401B-03CB-422A-9EBF-1309E459D21D@.microsoft.com...
> Hi i did consider this option, i am worried if i have too many users and
too many roles?
|||Hi
Thanks for your reply.I am not considering direct sql statements to come from anywhere. I meant all the access will be restricted using sp's. I have to consider reporting also. where a report (crystal mostly) will read directly from an sp to produce repor
ts. Do you have any links that tells about implementing column level permissions.
Thanks in advance
regards
Arun
Any recommendations on best practices for implementing row level and column level permissions in an SQL server database. I did some googling and i could find some recommendations for implementing row level permissions.(I am aware that Yukon is going to ha
ve this feature built-in, am using SQL 2k). All of them were seemed to be specific to applications.
Regarding column level security what concerns me is that, i want this to be applicable for my reporting tool(either crystal or sql reporting services).
Thanks in advance.
regards
Arun
You could use roles and have different views based on the columns you want
each role to see?
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Arun" <ar_kumar@.hotmail.com> wrote in message
news:0C4D56B1-7357-45D1-8EF4-FAE3E8FD5A29@.microsoft.com...
> Dear All,
> Any recommendations on best practices for implementing row level and
column level permissions in an SQL server database. I did some googling and
i could find some recommendations for implementing row level permissions.(I
am aware that Yukon is going to have this feature built-in, am using SQL
2k). All of them were seemed to be specific to applications.
> Regarding column level security what concerns me is that, i want this to
be applicable for my reporting tool(either crystal or sql reporting
services).
> Thanks in advance.
> regards
> Arun
|||Hi i did consider this option, i am worried if i have too many users and too many roles?
|||Maybe the problem is the number of columns? I can understand how you can
have a lot of users, but why would you need a lot of different roles unless
you have 18 billion columns and need a role for every single permutation?
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Arun" <ar_kumar@.hotmail.com> wrote in message
news:0618401B-03CB-422A-9EBF-1309E459D21D@.microsoft.com...
> Hi i did consider this option, i am worried if i have too many users and
too many roles?
|||Hi
Thanks for your reply.I am not considering direct sql statements to come from anywhere. I meant all the access will be restricted using sp's. I have to consider reporting also. where a report (crystal mostly) will read directly from an sp to produce repor
ts. Do you have any links that tells about implementing column level permissions.
Thanks in advance
regards
Arun
Row level and column level permissions.
Dear All,
Any recommendations on best practices for implementing row level and column
level permissions in an SQL server database. I did some googling and i could
find some recommendations for implementing row level permissions.(I am awar
e that Yukon is going to ha
ve this feature built-in, am using SQL 2k). All of them were seemed to be sp
ecific to applications.
Regarding column level security what concerns me is that, i want this to be
applicable for my reporting tool(either crystal or sql reporting services).
Thanks in advance.
regards
ArunYou could use roles and have different views based on the columns you want
each role to see?
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Arun" <ar_kumar@.hotmail.com> wrote in message
news:0C4D56B1-7357-45D1-8EF4-FAE3E8FD5A29@.microsoft.com...
> Dear All,
> Any recommendations on best practices for implementing row level and
column level permissions in an SQL server database. I did some googling and
i could find some recommendations for implementing row level permissions.(I
am aware that Yukon is going to have this feature built-in, am using SQL
2k). All of them were seemed to be specific to applications.
> Regarding column level security what concerns me is that, i want this to
be applicable for my reporting tool(either crystal or sql reporting
services).
> Thanks in advance.
> regards
> Arun|||Hi i did consider this option, i am worried if i have too many users and too
many roles?|||Maybe the problem is the number of columns? I can understand how you can
have a lot of users, but why would you need a lot of different roles unless
you have 18 billion columns and need a role for every single permutation?
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Arun" <ar_kumar@.hotmail.com> wrote in message
news:0618401B-03CB-422A-9EBF-1309E459D21D@.microsoft.com...
> Hi i did consider this option, i am worried if i have too many users and
too many roles?|||Hi
Thanks for your reply.I am not considering direct sql statements to come fro
m anywhere. I meant all the access will be restricted using sp's. I have to
consider reporting also. where a report (crystal mostly) will read directly
from an sp to produce repor
ts. Do you have any links that tells about implementing column level permiss
ions.
Thanks in advance
regards
Arun
Any recommendations on best practices for implementing row level and column
level permissions in an SQL server database. I did some googling and i could
find some recommendations for implementing row level permissions.(I am awar
e that Yukon is going to ha
ve this feature built-in, am using SQL 2k). All of them were seemed to be sp
ecific to applications.
Regarding column level security what concerns me is that, i want this to be
applicable for my reporting tool(either crystal or sql reporting services).
Thanks in advance.
regards
ArunYou could use roles and have different views based on the columns you want
each role to see?
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Arun" <ar_kumar@.hotmail.com> wrote in message
news:0C4D56B1-7357-45D1-8EF4-FAE3E8FD5A29@.microsoft.com...
> Dear All,
> Any recommendations on best practices for implementing row level and
column level permissions in an SQL server database. I did some googling and
i could find some recommendations for implementing row level permissions.(I
am aware that Yukon is going to have this feature built-in, am using SQL
2k). All of them were seemed to be specific to applications.
> Regarding column level security what concerns me is that, i want this to
be applicable for my reporting tool(either crystal or sql reporting
services).
> Thanks in advance.
> regards
> Arun|||Hi i did consider this option, i am worried if i have too many users and too
many roles?|||Maybe the problem is the number of columns? I can understand how you can
have a lot of users, but why would you need a lot of different roles unless
you have 18 billion columns and need a role for every single permutation?
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Arun" <ar_kumar@.hotmail.com> wrote in message
news:0618401B-03CB-422A-9EBF-1309E459D21D@.microsoft.com...
> Hi i did consider this option, i am worried if i have too many users and
too many roles?|||Hi
Thanks for your reply.I am not considering direct sql statements to come fro
m anywhere. I meant all the access will be restricted using sp's. I have to
consider reporting also. where a report (crystal mostly) will read directly
from an sp to produce repor
ts. Do you have any links that tells about implementing column level permiss
ions.
Thanks in advance
regards
Arun
Row level and column level permissions.
Dear All
Any recommendations on best practices for implementing row level and column level permissions in an SQL server database. I did some googling and i could find some recommendations for implementing row level permissions.(I am aware that Yukon is going to have this feature built-in, am using SQL 2k). All of them were seemed to be specific to applications
Regarding column level security what concerns me is that, i want this to be applicable for my reporting tool(either crystal or sql reporting services)
Thanks in advance
regard
ArunYou could use roles and have different views based on the columns you want
each role to see?
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Arun" <ar_kumar@.hotmail.com> wrote in message
news:0C4D56B1-7357-45D1-8EF4-FAE3E8FD5A29@.microsoft.com...
> Dear All,
> Any recommendations on best practices for implementing row level and
column level permissions in an SQL server database. I did some googling and
i could find some recommendations for implementing row level permissions.(I
am aware that Yukon is going to have this feature built-in, am using SQL
2k). All of them were seemed to be specific to applications.
> Regarding column level security what concerns me is that, i want this to
be applicable for my reporting tool(either crystal or sql reporting
services).
> Thanks in advance.
> regards
> Arun|||Maybe the problem is the number of columns? I can understand how you can
have a lot of users, but why would you need a lot of different roles unless
you have 18 billion columns and need a role for every single permutation?
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Arun" <ar_kumar@.hotmail.com> wrote in message
news:0618401B-03CB-422A-9EBF-1309E459D21D@.microsoft.com...
> Hi i did consider this option, i am worried if i have too many users and
too many roles?
Any recommendations on best practices for implementing row level and column level permissions in an SQL server database. I did some googling and i could find some recommendations for implementing row level permissions.(I am aware that Yukon is going to have this feature built-in, am using SQL 2k). All of them were seemed to be specific to applications
Regarding column level security what concerns me is that, i want this to be applicable for my reporting tool(either crystal or sql reporting services)
Thanks in advance
regard
ArunYou could use roles and have different views based on the columns you want
each role to see?
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Arun" <ar_kumar@.hotmail.com> wrote in message
news:0C4D56B1-7357-45D1-8EF4-FAE3E8FD5A29@.microsoft.com...
> Dear All,
> Any recommendations on best practices for implementing row level and
column level permissions in an SQL server database. I did some googling and
i could find some recommendations for implementing row level permissions.(I
am aware that Yukon is going to have this feature built-in, am using SQL
2k). All of them were seemed to be specific to applications.
> Regarding column level security what concerns me is that, i want this to
be applicable for my reporting tool(either crystal or sql reporting
services).
> Thanks in advance.
> regards
> Arun|||Maybe the problem is the number of columns? I can understand how you can
have a lot of users, but why would you need a lot of different roles unless
you have 18 billion columns and need a role for every single permutation?
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Arun" <ar_kumar@.hotmail.com> wrote in message
news:0618401B-03CB-422A-9EBF-1309E459D21D@.microsoft.com...
> Hi i did consider this option, i am worried if i have too many users and
too many roles?
Monday, March 12, 2012
Row Count SSIS
I need help implementing row count on a SSIS Package
*** Sent via Developersdex http://www.codecomments.com ***
That's a pretty skimpy requirement for us to help you on Michael! :-) Can
you please elaborate?
TheSQLGuru
President
Indicium Resources, Inc.
"Michael Watford" <mwatford@.edgecorp.us> wrote in message
news:%23nljMuIyHHA.1168@.TK2MSFTNGP02.phx.gbl...
>I need help implementing row count on a SSIS Package
>
> *** Sent via Developersdex http://www.codecomments.com ***
*** Sent via Developersdex http://www.codecomments.com ***
That's a pretty skimpy requirement for us to help you on Michael! :-) Can
you please elaborate?
TheSQLGuru
President
Indicium Resources, Inc.
"Michael Watford" <mwatford@.edgecorp.us> wrote in message
news:%23nljMuIyHHA.1168@.TK2MSFTNGP02.phx.gbl...
>I need help implementing row count on a SSIS Package
>
> *** Sent via Developersdex http://www.codecomments.com ***
Row Count SSIS
I need help implementing row count on a SSIS Package
*** Sent via Developersdex http://www.developersdex.com ***That's a pretty skimpy requirement for us to help you on Michael! :-) Can
you please elaborate?
--
TheSQLGuru
President
Indicium Resources, Inc.
"Michael Watford" <mwatford@.edgecorp.us> wrote in message
news:%23nljMuIyHHA.1168@.TK2MSFTNGP02.phx.gbl...
>I need help implementing row count on a SSIS Package
>
> *** Sent via Developersdex http://www.developersdex.com ***
*** Sent via Developersdex http://www.developersdex.com ***That's a pretty skimpy requirement for us to help you on Michael! :-) Can
you please elaborate?
--
TheSQLGuru
President
Indicium Resources, Inc.
"Michael Watford" <mwatford@.edgecorp.us> wrote in message
news:%23nljMuIyHHA.1168@.TK2MSFTNGP02.phx.gbl...
>I need help implementing row count on a SSIS Package
>
> *** Sent via Developersdex http://www.developersdex.com ***
Row Count SSIS
I need help implementing row count on a SSIS Package
*** Sent via Developersdex http://www.codecomments.com ***That's a pretty skimpy requirement for us to help you on Michael! :-) Can
you please elaborate?
TheSQLGuru
President
Indicium Resources, Inc.
"Michael Watford" <mwatford@.edgecorp.us> wrote in message
news:%23nljMuIyHHA.1168@.TK2MSFTNGP02.phx.gbl...
>I need help implementing row count on a SSIS Package
>
> *** Sent via Developersdex http://www.codecomments.com ***
*** Sent via Developersdex http://www.codecomments.com ***That's a pretty skimpy requirement for us to help you on Michael! :-) Can
you please elaborate?
TheSQLGuru
President
Indicium Resources, Inc.
"Michael Watford" <mwatford@.edgecorp.us> wrote in message
news:%23nljMuIyHHA.1168@.TK2MSFTNGP02.phx.gbl...
>I need help implementing row count on a SSIS Package
>
> *** Sent via Developersdex http://www.codecomments.com ***
Friday, March 9, 2012
Row and cell level security
A doctoral student of mine would like to know how he can get in contact
with the authors of the interesting white paper "Implementing Row and
Cell Level Security in Classified Databases Using SQL Server 2005"
Published: April 1, 2005 By Art Rask, Don Rubin, and Bill Neumann,
Microsoft Consulting Services.
Dr. Bo Sanden, Ph.D.
Prof. of Computer Science
Colorado Technical University
Colorado Springs, CO 80907Hi,
hop its correct :
art.rask@.tpi.net
source of information (google search)
http://www.uga.edu/~spc/Undergrad/s...n3700fall05.pdf
http://blogs.msdn.com/federaldev/ar.../19/471595.aspx
http://www.tpi.net/about/selectedAdvisor.aspx?ID=153
HTH
Regards
Andy Davis
Activecrypt Team
---SQL Server Encryption Software
http://www.activecrypt.com
"Bo Sanden" wrote:
> A doctoral student of mine would like to know how he can get in contact
> with the authors of the interesting white paper "Implementing Row and
> Cell Level Security in Classified Databases Using SQL Server 2005"
> Published: April 1, 2005 By Art Rask, Don Rubin, and Bill Neumann,
> Microsoft Consulting Services.
>
> Dr. Bo Sanden, Ph.D.
> Prof. of Computer Science
> Colorado Technical University
> Colorado Springs, CO 80907
>|||Thanks, Andy.
Bo
Andy Davis wrote:
> Hi,
> hop its correct :
> art.rask@.tpi.net
> source of information (google search)
> http://www.uga.edu/~spc/Undergrad/s...n3700fall05.pdf
> http://blogs.msdn.com/federaldev/ar.../19/471595.aspx
> http://www.tpi.net/about/selectedAdvisor.aspx?ID=153
>
> HTH
> Regards
>|||For a well documented, alternative approach for row level security
implementations please see http://www.technicalmedia.com (Data Nomad
product). Please feel free to contact me through the feedback page on the
site.
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.
"Bo Sanden" wrote:
> A doctoral student of mine would like to know how he can get in contact
> with the authors of the interesting white paper "Implementing Row and
> Cell Level Security in Classified Databases Using SQL Server 2005"
> Published: April 1, 2005 By Art Rask, Don Rubin, and Bill Neumann,
> Microsoft Consulting Services.
>
> Dr. Bo Sanden, Ph.D.
> Prof. of Computer Science
> Colorado Technical University
> Colorado Springs, CO 80907
>
with the authors of the interesting white paper "Implementing Row and
Cell Level Security in Classified Databases Using SQL Server 2005"
Published: April 1, 2005 By Art Rask, Don Rubin, and Bill Neumann,
Microsoft Consulting Services.
Dr. Bo Sanden, Ph.D.
Prof. of Computer Science
Colorado Technical University
Colorado Springs, CO 80907Hi,
hop its correct :
art.rask@.tpi.net
source of information (google search)
http://www.uga.edu/~spc/Undergrad/s...n3700fall05.pdf
http://blogs.msdn.com/federaldev/ar.../19/471595.aspx
http://www.tpi.net/about/selectedAdvisor.aspx?ID=153
HTH
Regards
Andy Davis
Activecrypt Team
---SQL Server Encryption Software
http://www.activecrypt.com
"Bo Sanden" wrote:
> A doctoral student of mine would like to know how he can get in contact
> with the authors of the interesting white paper "Implementing Row and
> Cell Level Security in Classified Databases Using SQL Server 2005"
> Published: April 1, 2005 By Art Rask, Don Rubin, and Bill Neumann,
> Microsoft Consulting Services.
>
> Dr. Bo Sanden, Ph.D.
> Prof. of Computer Science
> Colorado Technical University
> Colorado Springs, CO 80907
>|||Thanks, Andy.
Bo
Andy Davis wrote:
> Hi,
> hop its correct :
> art.rask@.tpi.net
> source of information (google search)
> http://www.uga.edu/~spc/Undergrad/s...n3700fall05.pdf
> http://blogs.msdn.com/federaldev/ar.../19/471595.aspx
> http://www.tpi.net/about/selectedAdvisor.aspx?ID=153
>
> HTH
> Regards
>|||For a well documented, alternative approach for row level security
implementations please see http://www.technicalmedia.com (Data Nomad
product). Please feel free to contact me through the feedback page on the
site.
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.
"Bo Sanden" wrote:
> A doctoral student of mine would like to know how he can get in contact
> with the authors of the interesting white paper "Implementing Row and
> Cell Level Security in Classified Databases Using SQL Server 2005"
> Published: April 1, 2005 By Art Rask, Don Rubin, and Bill Neumann,
> Microsoft Consulting Services.
>
> Dr. Bo Sanden, Ph.D.
> Prof. of Computer Science
> Colorado Technical University
> Colorado Springs, CO 80907
>
Subscribe to:
Posts (Atom)