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

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- and field-level modification dates

Hello all,

This is the best suited forum I found that fits my problem. If there's a better one, please direct me to it.

I have an Access database that contains (for the purpose of my application) a lot of information (15,000+ records in one of the tables).
My application suffers from slow performance because of the size of the tables (this is because the data has to be compared to data on a Palm device).
I have done a lot of optimizations to the code, but I haven't found a way to implement the best optimization for my case: Getting row- and field-level modification dates.
That way I can only compare relevant data and not the whole database.

For example, I wish there was a way to filter out rows from a table that were modified before January 1st, 2007.
Another example: Filter out rows that their Address column was modified in the last two weeks.

I know that a partial solution will be to implement this on my own: adding a Modified column to all my tables.
But, as I said, this is only a partial solution because it saves only the row's modification date, and not for each of the fields. Plus, this is a really ugly solution, having this column standing out everywhere.

Is there a chance this is implemented inside Access (or SQL server, for that matter)?
The closest I came was finding out that there's a field in msysobjects that specifies when a table was last modified. But not a row or a field in a row.

Any help will be greatly appreciated!

Thanks.

P.S. The weird thing is that this "modified" bit that I'm looking for is implemented on Palm devices and is very easy to use... Seems ironic that there won't be a simple way to do this on a PC.

P.P.S. I guess row- and field-level modification dates are the same... of course, Modified(row) = MAX(Modified(field1), Modified(field2), ...)
This mechanism of auditing can be implemented in Acces with help of AfterUpdate Event code and in SQL with triggers.
I should create a table
ID(identity) Column DataofModifying(DataTime default value) Before After

When the user do something, after update you can write in this table.

There is no automatic way (wait some time because I heard Katmai, SQL Server 2008 have data auditing)|||

There is no specific, built in, way to determine if a row or field has been altered. In situations where alteration auditing is requried, it is often necessary to use 'a really ugly solution' and add ChangedDate columns to every table. (And other columns necessary for auditing are often added as well.)

However, SQL Server does provide 'Replication' where data changes are constantly 'set aside' in a special set of tables to be sent to the replicant. I don't think that would be a solution for your situation.

|||Thanks for replying so quickly.

I think that is amazing.
Are you saying that this feature exists on all Palm devices (even the old black-and-white ones), but is missing from all data server solutions?

I find that extremely hard to believe.

Just for argument's sake, here is a quote out of Palm OS Conduit Development Kit 6.01, describing the eRecAttrDirty flag:

eRecAttrDirty = 0x40 Indicates that the record has been marked as modified. This replaces the older constant DIRTY_BIT.

|||

You should recognize that Palm is an 'application', not a data server.

.NET applications also have the capability to track data changes to a datset.

But SQL Server does not offer the functionality you had hoped to find.

|||Hi again.

Can you elaborate on the functionality in .NET applications?

That might be the solution I'm looking for, if there's any way to make it interact with Access.
|||

Yes, .NET applications can use data stored in Access databases.

But you would be better positioned to post your .NET/Access questions in a .NET (VB/C#) or Access related forum. Not as many folks here that are deeply very familiar with those environments.

|||Alright then, I'll direct this question to a more suitable forum.

Thanks for the help!
|||

SQL Server has a data type called "timestamp", which is really a "row version" and has nothing to do time. You can use this to determine whether a row has been modified when (manually/in your app) synchronizing against another database.

Row- and field-level modification dates

Hello all,

This is the best suited forum I found that fits my problem. If there's a better one, please direct me to it.

I have an Access database that contains (for the purpose of my application) a lot of information (15,000+ records in one of the tables).
My application suffers from slow performance because of the size of the tables (this is because the data has to be compared to data on a Palm device).
I have done a lot of optimizations to the code, but I haven't found a way to implement the best optimization for my case: Getting row- and field-level modification dates.
That way I can only compare relevant data and not the whole database.

For example, I wish there was a way to filter out rows from a table that were modified before January 1st, 2007.
Another example: Filter out rows that their Address column was modified in the last two weeks.

I know that a partial solution will be to implement this on my own: adding a Modified column to all my tables.
But, as I said, this is only a partial solution because it saves only the row's modification date, and not for each of the fields. Plus, this is a really ugly solution, having this column standing out everywhere.

Is there a chance this is implemented inside Access (or SQL server, for that matter)?
The closest I came was finding out that there's a field in msysobjects that specifies when a table was last modified. But not a row or a field in a row.

Any help will be greatly appreciated!

Thanks.

P.S. The weird thing is that this "modified" bit that I'm looking for is implemented on Palm devices and is very easy to use... Seems ironic that there won't be a simple way to do this on a PC.

P.P.S. I guess row- and field-level modification dates are the same... of course, Modified(row) = MAX(Modified(field1), Modified(field2), ...)
This mechanism of auditing can be implemented in Acces with help of AfterUpdate Event code and in SQL with triggers.
I should create a table
ID(identity) Column DataofModifying(DataTime default value) Before After

When the user do something, after update you can write in this table.

There is no automatic way (wait some time because I heard Katmai, SQL Server 2008 have data auditing)|||

There is no specific, built in, way to determine if a row or field has been altered. In situations where alteration auditing is requried, it is often necessary to use 'a really ugly solution' and add ChangedDate columns to every table. (And other columns necessary for auditing are often added as well.)

However, SQL Server does provide 'Replication' where data changes are constantly 'set aside' in a special set of tables to be sent to the replicant. I don't think that would be a solution for your situation.

|||Thanks for replying so quickly.

I think that is amazing.
Are you saying that this feature exists on all Palm devices (even the old black-and-white ones), but is missing from all data server solutions?

I find that extremely hard to believe.

Just for argument's sake, here is a quote out of Palm OS Conduit Development Kit 6.01, describing the eRecAttrDirty flag:

eRecAttrDirty = 0x40 Indicates that the record has been marked as modified. This replaces the older constant DIRTY_BIT.

|||

You should recognize that Palm is an 'application', not a data server.

.NET applications also have the capability to track data changes to a datset.

But SQL Server does not offer the functionality you had hoped to find.

|||Hi again.

Can you elaborate on the functionality in .NET applications?

That might be the solution I'm looking for, if there's any way to make it interact with Access.
|||

Yes, .NET applications can use data stored in Access databases.

But you would be better positioned to post your .NET/Access questions in a .NET (VB/C#) or Access related forum. Not as many folks here that are deeply very familiar with those environments.

|||Alright then, I'll direct this question to a more suitable forum.

Thanks for the help!
|||

SQL Server has a data type called "timestamp", which is really a "row version" and has nothing to do time. You can use this to determine whether a row has been modified when (manually/in your app) synchronizing against another database.