Controlling Data Modification At Row Level

Jul 23, 2005

Hi,
I'm writing an application that involves data that has a set of users
that are allowed to perform certain operations on it.
i.e. Only the row owner can modify a row, but there is a set of users
who can view it.

At the moment, I've started to implement this by calling a UDF at the
beginning of each stored procedure that validates that the user is
allowed to call the procedure on that particular row (trusting a higher
teir to verify the user), and throws an error if they are not.

I don't particularly like this solution, as I need a UDF for each
procedure, and will have to re-write the udf's if the access rules
change (which they might).

Can anyone suggest a method of implementing a more generic row
permissions system?

Cheers,
Ben

View 5 Replies


ADVERTISEMENT

Row- And Field-level Modification Dates

Jul 30, 2007

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), ...)

View 18 Replies View Related

Retrieving Row- And Field-level Modification Dates

Jul 30, 2007

Hello all,

(This was previously posted @ http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1935938&SiteID=1&mode=1, I transferred it here because it's more relevant)

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).
I use C++ non-.NET ADO in order to access that database (msado15.dll).
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), ...)

View 2 Replies View Related

Data Modification

Sep 6, 2000

I am trying to migrate a system across and need some help tidying data and merging info.
Firstly if I have some companies with the suffix 'Limited' and some with 'Ltd' ie BBC Ltd and BBC Limited. How do I change all these to read the company name and then Ltd??
Secondly I am pulling info from a table which has company name duplicates into a table that also may have company details relating to that company name. I wish to add the details if the company name doesn't exist in the table and also only input the once from the source table. I am using cursors for this. My scripts see below is adding every record. Any ideas why??
Thanks for any suggestions


CREATE PROCEDURE check_company2
AS
declare
@id varchar(50),
@company_name varchar(50),
@business_type varchar(50),
@hardware varchar(50),
@operating_system varchar(50),
@networks varchar(50),
@pcs varchar(50),
@terminals varchar(50),
@test_companies_id varchar(50)
begin
declare s_completelist_cur cursor for select [company name],[business type],[hardware],[operating system],[networks],
[pcs],[terminals] from s_completelist
open s_completelist_cur

fetch next from s_completelist_cur into @company_name, @business_type, @hardware, @operating_system, @networks,@pcs, @terminals
while (@@fetch_status=0)

begin
print @company_name
select @test_companies_id from companydetails c where c.company_name = @company_name
if @test_companies_id is not null

begin
print 'match found'
end
else
begin
insert into companydetails ( company_name, business_type,hardware, op_system, network, pcs, terminals)
values (@company_name, @business_type,@hardware, @operating_system, @networks, @pcs, @terminals)
end

fetch next from s_completelist_cur into @company_name, @business_type, @hardware, @operating_system, @networks,@pcs, @terminals
end
close s_completelist_cur
deallocate s_completelist_cur
end

View 1 Replies View Related

Dynamic Modification Of Data Flow Objects

Jul 24, 2006

My idea is to read in source and destination values from a table and using these values within a ForEach loop dynamically alter the source, destination and mapping on the data flow within the package. My reading on SSIS leads me to believe that these properties are not available for modification at run-time. Has anyone any ideas on how to accomplish this task. I have data in over 200 tables to import every 4 hours so I'd rather have to maintain 1 package rather than 200.

View 9 Replies View Related

Plug-in Algorithm In Data Mining Using Sql Server 2005-- Modification For Association , Classification??

Sep 7, 2006

managed plug-in framework that's available for download here: http://www.microsoft.com/downloads/details.aspx?familyid=DF0BA5AA-B4BD-4705-AA0A-B477BA72A9CB&displaylang=en#DMAPI.

This package includes the source code for a sample plug-in algorithm written in C#.

in this source code all .cs files are modified for clustering algorithm

if my plugin algorithm is of association or classification type then what modifications are requried in source code???

View 9 Replies View Related

Controlling SQL Access In .net - Revised

Apr 14, 2007

I have windows application (Basically a accounting system). I want to use either SQL authenticaion, or Windows authentication for my users of the application (Basically each db user will have their own account in Users for that db, so I can have sql control the access).

All access to the db is via stored procedures, but I want to have a set schema for each user type:

Supervisor
Manager
Data Entry
etc.

All with a predefined access to the proper stored procedures(So far fairly simple).

In addition to this I want to be able to set the specifics of that user in my .net application via a interface (so a user by default will have the pre-defined schema, when a supervisor opens this interface, they can add/remove access to other stored procedures)

Here's the issue. Is this the proper methodology to achieve this, because it's quite of bit of programming work. I don't want to do it then need to re-due everything.

Also should I use Windows Authentication, or SQL, or have a completly seperate table in the DB where I do manual authentication then have the .net application restrict the access?

Any help or comments on this approach will be helpful. Also ifanyone has found any articles or information similiar to what I am trying to accomplish, please point me in the correct direction.

Thanks.

View 8 Replies View Related

Controlling The Sequence Of Execution

Oct 26, 2007

Hello,

I have a sequence problem in a package and I'm unable to figure it out: I have a control flow with some elements in this specified order: a script task -> Execute SQL task -> Data Flow. The script task is responsible for setting a value on a package variable; this same variable will then be used to decide if the Data Flow is disabled or not (there's an expression on the DataFlow). However, it looks like the variable is being tested before the script actually writes it, how can I guarantee that the DataFlow's expression is evaluated only after the script task has ended? Also, I have tried putting each component inside their own sequence container and I get the same result

Thanks in advance

View 7 Replies View Related

Controlling PDF Export Filename

Jan 18, 2006


Folks,

Our client has requested that we change the export file name (PDF and others) to match the report name. Currently, the name is based on the report's filename (eg, foo.rdl exports as foo.pdf) -- this causes problems because the same RDL is being used for a few fairly different reports. Is this controllable at all?

thanks,
--randy

View 11 Replies View Related

Controlling Sub Reports Visibility

Nov 29, 2007

Hi

I have a main report and wanted to embed a sub report in the main table. This is pretty easy enough.

The main report lists the suppliers we have. And the sub report lists any sub suppliers affialiated to those suppliers.

I have set the sub report visibility settings to hidden and is toggled by the suppliers ID.

The problem is that NOT ALL suppliers will have sub-suppliers, yet when you run the report the user gets presented with the option to toggle all suppliers' ID.

Is there any way to have a main report that only allows the user to click on a suppliers ID when that particular supplier has a sub supplier?

View 1 Replies View Related

Controlling Errors In Stored Procedure

Jul 14, 2006

Hi everyone:
I need to use the "SET ROWCOUNT" statement to limit the amount of data returned to the application in a query, I know that if "SET ROWCOUNT = 0" is not specified at the end of this stored proc all the next queries will return only the amount of records specified in the initial "SET ROWCOUNT" call, so I would like to know if a I can have something like the TRY-CATCH-FINALLY statement (in SQL-92 for SQL Server 2000, not in SQL 2005) to make sure the "SET ROWCOUNT = 0" is sent at the end even if an error is raised.
Can it be done?
Thanks for any help.

View 2 Replies View Related

Version Controlling For OLAP Database

Oct 20, 2006

Hi,

Can someone tell me how I can manage different versions of my OLAP Database, in Analysis Services 2000 without having to use Archive database ?
I want only the design of my cubes and my dimensions. Is there any way to get the script of a cube?


Is there any tool available to do this kind of actions ?

Thanks in advance.
Ashish

View 1 Replies View Related

OLE DB Error - Non-NULL Controlling IUnknown Was Specified

Nov 8, 2007

Hi All,

We have an odd OLE DB error here. We can create the connection but only lookup a handfull of tables that are in the database. (Maybe only 10 of 50 - of which I need the ones not being displayed) Even if I choose a table from the incomplete list and try and close the dialog I get the following error. We are trying to pull data from an IBM AS400 if that makes any difference. It did work in SQL2000 - Hope you can help us!!!

---

ERROR at HTEIMPORT(table) [connection manager "IPaddress.user.database"]; SSIS Eroor Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E33. An OLE DB record is available. Source: "Microsfot OLE DB Service Components" Hresult: 0x80040E22 Description: "Non-NULL controlling IUnknown was specified, and either the requested interface was not IUnknown, or the provider does not support COM aggregation.".

Error at SQLTaskName [OLE DB Source [423]]: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AquireConnection method call to the connection manage "IPAddress.useratabase" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AquaireConnection method call failed.

Additional information:
Exception from HRESULT: 0xC020801C (Microsoft.SqlServer.DTSPipelineWrap)

View 4 Replies View Related

Controlling Access In An Open Environment.

Nov 12, 2007



I have a problem at the company I work for. For historic reasons many developers and users know the sa passwords for SQL. Obviously this is not a good situtation to be in. Now the obvious answer it to just change this but for reasons unknown to sanity half of our applications are hardcoded to use sa passwords.

Now as a DBA coming into this this puts me in a tricky position as its impossible for me to control who is doing what etc. Is it possible to put triggers or something to monitor logins which will deny access to anyone accessing via the sa password not through an application. Ie if an application calls then the sa will have to be allowed but if a user using Query Analyser for example tries to use sa then they are denied access ?

Long term all these passwords are going to change but it the short term I still need to create some control on the system. Anyone have any thoughts ?

Si

View 3 Replies View Related

Controlling Flow In ForEach Container

Feb 13, 2007

I have a few hundred data files that I need to process in groups from oldest to most recent. To that end, I have created staging folders for each date that appears in the file names (example: 20060213) and moved all of the data files to the appropriate folders.

I also have these folder names in a recordset that has been sorted in ascending order, so the oldest date should be the first record, the second-oldest date should be second and so on. My thinking is to use this recordset with a ForEach Loop Container to loop through each folder, but I am not sure if Integration Services will necessarily loop through the records in order.

Can someone tell me whether the records will be processed in order or suggest a better alternative to make sure the directories are processed in the correct order?

View 2 Replies View Related

Controlling Intra-day Datetime Range

Oct 5, 2007

I have a one table with 30-second updates for many day's worth of data. What I'd like to do is only select out the datetime between, say 07:00:00.000 and 11:00:00.000 for each day.

select * from TableName where "timestamp"
between '2007-06-04 07:00:00.000' and '2007-06-04 11:00:00.000'


This works for one specific day. But I'd like a return of values for all possible days in the table (as opposed to just 2007-06-04).

View 7 Replies View Related

Controlling A Transaction By User In SQL Server 2000

Jun 14, 2007

Hey Folks!
I have a typical requirement by my client. On submitting a Update (Bulk) button a huge database operation starts. A huge bulk update operation need to be performed. This would take 2-3 minutes some times. Client wants a cancel button in this case where he can be given a way to cancel the database Transaction.
 Please let me know in case if there is a way out.
Thanks, in advance.
Regards,
Uday.D

View 2 Replies View Related

Controlling Fields In A Select Statement By Use Of Parameters

Sep 29, 2005

Hi to allI wish to be able to have a standard select statement which hasadditional fields added to it at run-time based on suppliedparameter(s).iedeclare @theTest1 nvarchar(10)set @theTest1='TRUE'declare @theTest2 nvarchar(10)set @theTest2='TRUE'selectp_full_nameif @theTest1='TRUE'BEGINother field1,ENDif @theTest2='TRUE'BEGINother field2ENDfrom dbo.tbl_GIS_personwhere record_id < 20I do not wish to use an IF statement to test the parameter for acondition and then repeat the entire select statement particularly asit is a UNIONed query for three different statementiedeclare @theTest1 nvarchar(10)set @theTest1='TRUE'declare @theTest2 nvarchar(10)set @theTest2='TRUE'if @theTest1='TRUE' AND @theTest2='TRUE'BEGINselectp_full_name,other field1,other field2from dbo.tbl_GIS_personwhere record_id < 20ENDif @theTest1='TRUE' AND @theTest2='FALSE'BEGINselectp_full_name,other field1from dbo.tbl_GIS_personwhere record_id < 20END......if @theTest<>'TRUE'BEGINselectp_full_namefrom dbo.tbl_GIS_personwhere record_id < 20ENDMake sense? So the select is standard in the most part but with smallvariations depending on the user's choice. I want to avoid risk ofbreakage by having only one spot that the FROM, JOIN and WHEREstatements need to be defined.The query will end up being used in an XML template query.Any help would be much appreciatedRegardsGIS Analyst

View 2 Replies View Related

Controlling Process Priority In SQL Server 2000 - Possible..?

Jun 25, 2007

A similar question has been asked in a different thread, but didn't get a conclusive answer. Is there a way to set process priority on a specific process (alternatively - processes started by a specific user), similar to what the Task Manager allows in Windows?

I need to archive some data from an OLTP database. Unfortunately, some of the tables are growing rather large, and copying the data, and especially removing it based on record age, is a resource-intensive operation. My database must be available 24/7, and I can't have queries timing out, or I start losing business real fast. I was thinking of firing off the archive script and setting the process priority on it to something lower than normal, so it won't interfere with my OLTP processes.

Maybe someone can suggest a better solution?

View 2 Replies View Related

Data Presentation At Each Level

Aug 8, 2007



I have a table with Columns as metric id, product name, product id, sales, amount.
The values of the column metric id will be 101, 102, 103 etc..

I need to create a report, in which I need to show up data as below:

1 series -------
metric 101:
product name, product id, sales, amount

metric 102:
product name, product id, sales, amount


metric 103:
product name, product id, sales, amount

2 series--------
metric 201:
product name, product id, sales, amount

metric 202:
product name, product id, sales, amount


metric 303:
product name, product id, sales, amount

Please let me know, how I can show the rows of that table grouped for each metric id.

View 1 Replies View Related

Mass DTS Modification

Aug 13, 2004

I have a series of DTS packages.
Each package has 20 queries.
Each query has a server name.
Is there a way to change the servername without editing each query in each DTS package.
I'd like to copy the template DTS package, then perform the modification.

Thanks

View 2 Replies View Related

Need Help With String Modification

Feb 23, 2004

Im trying to basically take an email address user@domain.com that is in a row and basically split it up or get 1 side.

Im trying to find the command if it exists or a way to do this in tran sql to basically give me everything left or right of the '@' symbol, is there an easy way of doing this? Thanks!

Chris

View 7 Replies View Related

Trigger Modification

Jul 26, 2007

I have converted Access database to sql express. Access Database had AutoNumber FIelds for which trigger was generated by Upsizing wizard.

Now when I import data from client the autonumber field value changes because trigger is fired which distroys all links

I want to modify trigger so that it generates new number only when it is not supplied in a insert command.

Please help. Code is given below. Also suggest how to save because when I use save, it asks for a new .sql file name and a new file is generated instead of modifying the same trigger



set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[T_AcControlLimit_ITrig] ON [dbo].[AcControlLimit] FOR INSERT AS
SET NOCOUNT ON
DECLARE @randc int, @newc int /* FOR AUTONUMBER-EMULATION CODE */
/* * RANDOM AUTONUMBER EMULATION CODE FOR FIELD 'IntAcControlCode' */
SELECT @randc = (SELECT convert(int, rand() * power(2, 30)))
SELECT @newc = (SELECT IntAcControlCode FROM inserted)
UPDATE AcControlLimit SET IntAcControlCode = @randc WHERE IntAcControlCode = @newc

View 2 Replies View Related

Modification Logs

Jun 13, 2007

Is there a way to determine when a file was changed/modified? We're onSQL 2000 and I need to know when a view was modified and by whom.Thanks!

View 1 Replies View Related

Parameter Modification Within Mdx

Feb 14, 2007



Is it possible to modify a parameter value within a dataset using mdx?

lets say the param values of @bucket = 6

how would you go about writing @bucket + 1

so @bucket now would equal 7

is this possible?

View 1 Replies View Related

Modification Of The Primary Key !!

May 23, 2007

Hi

I have binded a DataGradView (DGV) with a Dataset (DS) , i execute and modify on the DataGradView the primary key (IDObj) and also the two other columns, by clicking on the UpDate button i call the function UpDateTable :







public void UpdateTable(string nameTable)
{
// con : ma connection OLE Ã un fichier Access 2003
// DS : dataSet
// DAdp : Dataadapter
// nameTable : nom de ma table



OleDbCommand comdUPDATE;

string CommandText = "UPDATE " + nameTable + " SET IDObj=@IDObj ,NameParent=@NameParent,TypeObj=@TypeObj WHERE IDObj=@IDObj";

comdUPDATE=DAdp.UpdateCommand = new OleDbCommand(CommandText, con);

// IDObj : clé principale
comdUPDATE.Parameters.Add(new OleDbParameter("@IDObj", OleDbType.VarChar, 50));
comdUPDATE.Parameters.Add(new OleDbParameter("@NameParent", OleDbType.VarChar, 50));
comdUPDATE.Parameters.Add(new OleDbParameter("@TypeObj", OleDbType.VarChar, 50));

comdUPDATE.Parameters["@IDObj"].SourceVersion= DataRowVersion.Original; //!!!
comdUPDATE.Parameters["@NameParent"].SourceVersion= DataRowVersion.Current;
comdUPDATE.Parameters["@TypeObj"].SourceVersion= DataRowVersion.Current;

comdUPDATE.Parameters["@IDObj"].SourceColumn = "IDObj";
comdUPDATE.Parameters["@NameParent"].SourceColumn = "NameParent";
comdUPDATE.Parameters["@TypeObj"].SourceColumn = "TypeObj";

DataSet modifiedDS = DS.GetChanges(DataRowState.Modified);


try
{
con.Open();
DAdp.Update(modifiedDS.Tables[nameTable]);
DS.Clear();
DAdp.Fill(DS, nameTable);
con.Close();

}
catch (Exception e)
{
MessageBox.Show(e.Message.ToString());

}
finally { con.Close(); }

}









But i have this exception : Concurrency violation : the update command affected 0 of the expected 1 records.




how is it possible to modify a primary key witch is the reference of that modified row?





Thanks for help

View 8 Replies View Related

Reporting Services :: SSRS - Controlling Space Between Two Tablix

Oct 16, 2015

I have a report I'm trying to modify for a client that has two auto-grow tablix.  We want the second tablix to start after the first one completes, however the first tablix will overwrite the second one if it contains multiple rows, rather than pushing it down. How can I control this?

View 2 Replies View Related

Controlling Security Through Stored Procedures -- 2005 Behaviour

Aug 23, 2005

Hi!

View 4 Replies View Related

Controlling Export Formats Based On User Role

Apr 26, 2007

I'm using Reporting Services 2000 and I have a situation where I need to specify the file formats available to export. I know I can change the RSReportServer.config file to change these at a global level, but what I really need is to be able to configure these differently for different user groups. For example:



group 1 - has all available export formats available

group 2 - only has the ability to export to excel

group 3 - has no option to export to any format.



Is there a way for this to be done? If so, can anybody give me any pointers on how it can be done?



Any help given will be most appreciated!!

View 3 Replies View Related

Column Level Or Database Level Encryption/decryption....

Jan 16, 2008

I want to perform column level and database level encryption/decryption....
Does any body have that code written in C# or VB.NET for AES-128, AES-192, AES-256  algorithms...
I have got code for single string... but i want to encrypt/decrypt columns and sometimes the whole database...
Can anybody help me out...
If you have Store procedure in SQL for the same then also it ll do...
Thanks in advance

View 1 Replies View Related

High Level &&amp; Detail Level Design Documents

Nov 19, 2007



Hi,
AM in need of SSRS 2005 design documents for a project purpose. Can somebody let me know where can i find these documents? Thanks in advance

View 1 Replies View Related

Data Level Security In Reports

Aug 30, 2007

Hi Experts,

Hi experts,

I have a BI Reporting scenario, wherein i have to fetch Reports from analysis Services.
when the user tries to access a report, he should be validated uisng the Windows Authentication ID, and only data specific to that user should be display.
I am not sure if this user authentication is to be done on analysis Services/reporting services.

Any suggestions/pointers would be highly appreciated.

Thanks


Alicia Rose

View 1 Replies View Related

Isolation Level Of Data Source

Nov 14, 2007

Is there a way to alter the isolation level of a data source? I have queries with nolock hints that I would like to remove in favor of "set isolation level read uncommitted" but this is not allowed in the report builder. I cannot move to SPs as I'm working in a third-party apps database. I guess I'm wishing for the SSIS style of isolation setting where it's a member of the properties.

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved