How To Prevent DBA From Getting Confidential Data Stored In SQL Server?
Suppose I have a database storing some confidential information, such
as legal information, medical or financial records, etc., and a
Web site with a membership system so that only authorized users can
view the information.
I understand I can encrypt the information, and the user's passwords,
so that if the database is compromised it still shouldn't be possible
for an outsider to view the confidential information.
However, what about people who have legitimate access to the database,
such as the DBA, Web developer, etc., but who should not be able to
view the confidential information? For example, even though the
user's password was encrypted, what would stop the DBA from replacing
the user's password with his own (encrypted) password, then logging in
and viewing the user's info, then copying back the original encrypted
password? Or, adding a new user for himself with whatever
permissions he chooses?
View Complete Forum Thread with Replies
Related Forum Messages:
Stored Procs: How To Prevent Return Of Uneccesary Data?
Hi, I have a stored procedure that looks like this:...WHILE @@FETCH_STATUS = 0BEGINDECLARE @MyCount int ; EXEC spLoanQuestionnaireCriteria @AuditSelectedLoanID, @Criteria, @MyCount OUTPUTEND ...SELECT * FROM #Categories... Executing this stored procedure will return me 1 table for each time the EXEC statement is called that only has on column (MyCount)I really don't need this data to be returned, it is only used for some internal calculations in the stored procedureThe stored procedure should only return the results from SELECT * FROM #Categories in 1 table.Is there a Keyword I can use to exclude the EXEC results being returned to the dataset? Thanks in advance,Andre
View Replies !
Archive Data Instead Of Deleting It To Prevent 4GB Data Limit
We are running SQL Server 2005 express on Windows 2003. The database server gets significant amounts of data. Because of the 4GB data limit we have a daily cron task which goes through and deletes data older then 90 days. We would like a way to archive this data instead of deleting it. Is there any way to take data and compress it and store it in a different way, so that if needed, customers can query directly out from the compressed data? Cleary querying from compressed would be slower but that is ok. Any other solutions that would allow us to archive data instead of deleting it? Thanks.
View Replies !
Prevent Data Being Inserted Twice
I have a table with 3 columns: ID, Status, DateTime. I created a stored procedure to insert a staus value for each ID. This will run every hour. The DateTime stores the time, date when the Status was inserted. If the procedure was to be run a second time in hour window I do not want any Status to be inserted. Note: that I cannot rely on the procedure being run at exactly the right time - if it was scheduled to run on the hour (i.e at 1:00, 2:00, 3 :00 etc) but didn't run until 1:20 it sould still be able to run at 2:00. Does anyone know if there is anyway I can gaurd against this?
View Replies !
How Can I Prevent From Inserting Duplicate Data?
I have a table storing only 2 FKs, let's say PID, MID Is there any way that I can check distinct data before row is added to this table? For example, current data is PID MID------------100 2001100 2005101 3002102 1009102 7523102 2449 If my query is about to insert PID 100, MID 2001, since it's existing data, i don't want to add it. Can I use trigger to solve this issue? Thanks.
View Replies !
Does Db_denydatawriter Prevent Procs From Updating Data?
If a user is a member of a role which would allow him to execute a proc which updates a table, and he is then granted db_denydatawriter , can he still update the table through the proc? SS2000 SP4. Thanks, michael Since posting this I tested the scenario and YES! The user was still able to execute an update proc even though the user excuting the proc was granted db_denydatawriter.
View Replies !
How To Prevent System Administrator To View And Edit A Database Structure And Data
I represent a software development house and we have developed a client server system based on SQL Server. Most of our customers have already purchased Enterprise License of SQL Server, therefore they own the SA Login and Password. We are bound to attach our Database with their Server on their machine. My question is how can we stop a System Administrator of SQL Server to view our Database Structure, Queries, Data installed on their SQL Server on their machine. Our database structure is a trade secret and we cant reveal the structure to the client. please answer this question by email to me at farhandotcom@gmail.com Thanks & Regards Farhan
View Replies !
SQL Server How To Prevent Locking To Do Read-Only Reports
Need some information regarding SQL Server locking.Requirement is to run SQL Statements for reports (read-only) that willnot lock-out other people trying to update (write lock). For this, wetried the following to make the db connection not deny write locks(our report code will not have a read-only lock).ADODB.ConnectionObj.Mode =adModeShareDenyNoneBut this made our connection bumped whenever someone tried to update.Tried enabling dirty reads by doing:ADODB.ConnectionObj.IsolationLevel =adXactBrowseEven this made our connection bumped whenever someone tried to update.Is there a way to achieve what is needed without having a seperatedatabase for reports?
View Replies !
Northwind Database In SQL Server Express Is Busted And Gone,after SqlCommand Fails In VB2005Express-How To Prevent It Happen?
Hi all, I tried to use dbo.tables of Northwind database in SQL Server Express to do SqlCommand to populate a DataTable in DataSet. The SqlCommand failed in the project execution and I found that Northwind database in SQL Server Express is busted and gone (just the name "Northwind" remained in the SQL Server Management Studio Express). How can I prevent it from happening in my "SqlCommand-SqlConnection" project? Please help and advise. I tried to repair my "Northwind" database by using the SQL2000SampleDb.msi of Northwind and pubs Sample Databases for SQL Server 2000 downloaded from the http://www.microsoft.com/downloads. My "pubs" database is still in my SQL Server Management Studio Express. How can I just repair my "Northwind" database by using the Microsoft SQL2000SampleDb.msi program? Please help and advise this matter too. Thanks in advance, Scott Chang
View Replies !
Encrypting Stored Sql Server Data
We have a sql server database that serves a departmental client serverapplication. The database contains confidential information and I wouldlike to encrypt the stored data. Has anyone done this? If so, do yourecommend a particular product? The ones I am aware of are DBEncrypt , SQLEncryptionizer and PGP.I am on a non-profit budget, so I can't spend $10,000.Thanks in advance,Steve
View Replies !
Problem In Data Access Though SQL Server Stored Procedure
I have two database SOP and CRM. Both supports windows authentication and SOP suports sql authentication too. Now i have to write a SP in SOP database with identity impersonation (with superadmin authentication) which will do some work with this impersonated id on SOP database but needs to fetch some values from CRM with the current user account - not impersonated account.Lets clear... I want to show the orders generated by all users and in the display grid there is a column like "IsMyCustomer?" which will show the order is to a customer which i have access. In CRM (MS) if i select from FilteredCustomer view i will get the customersid of those under me. And if i left outer join with FilteredOrder view in SOP (with Super Admin windows credentials) which returns orders by current user i will get the desired result.Now the problem is i can't sent two connection credentials to a SP. So what i want is to connect SOP with user's windows credentials (not with impersonation) and from the SP we will Select data with Admin's account. But i don't know is there any way to connect to a linked server with a different credentials. Like when i am selecting from CRM server it will use different credentials.Remember i am using SQL server 2000 and everything should be done through a single SP.I know i can do this easily with two different select from Data access layer. But i am looking for some performance effectinve way. My PM wants this idea to be implemented. I have no chice guys....
View Replies !
SQL Server Express Stored Proc Does Not Return Any Data
Hi!I have this table:Units -id uniqueidentified (PK), -groupName NVARCHAR(50) NOT NULL, -name NVARCHAR(50) NOT NULL, -ratio float NULL and the stored proc that simply returns all rows:ALTER PROCEDURE dbo.ilgSP_GetUnitsAS SELECT [id], [groupName], [name], [ratio] FROM [Units] ORDER BY [groupName], [name]If I select 'Show Table Data' in Visual Studio 2005 I see all rows from the table. If I 'Execute' my stored from VS 2005 I get this:Running [dbo].[ilgSP_GetUnits].id groupName name ratio -------------------------------------- -------------------------------------------------- -------------------------------------------------- ------------------------- No rows affected.(1 row(s) returned)@RETURN_VALUE = 0Finished running [dbo].[ilgSP_GetUnits].And I don't get any data in my ASP.NET application. WHY?Thanks!
View Replies !
Need Coding For Inserting Data Into SQL Server Using Stored Proecdure.
Hi to all.. I am new to ASP.Net, I want coding for inserting data in to SQL server 2005 that code contain the following criteria. 1.ASP.Net using C#.Net. 2.Using Stored Procdure. 3.the connetion string ,command , Data reader is in the same class. this class will be reusable for other page also without modifying any thing in it. Please Help me. Advance thanks.
View Replies !
To Pass Data To An Stored Procedure Inside Sql Server
I want to use a stored procedure inside Sql Server from my aspx page so that the data entered in the form goes to the database after submit. My stored procedure (inside the SQL Server) inserts several fields in a table and it returns two variables. What code I need to write in order to pass the data from the form to the stored procedure inside the Sql Server? And to store the two returned values? Thanks
View Replies !
Exporting Data From SQL Server To Excel From A Stored Procedure
I need to export data, from within a MSSql stored procedure to excel. Right now we use DTS, but its cumbersome and the users always screw it up. I would usually just send the tabel to a .csv fiel and pick it up in excel, but I have a field that has preceding zeros and excel truncates them and uses a general fromat. Any ideas Thanks
View Replies !
Data Truncated When Stored Procedure Run From Sql Server Agent
I have a stored procedure which loops thru about 180 tables and inserts the data into one table used for reporting purposes. One field is a ntext field. If I execute the stored procedure from query analyzer the entire contents of the ntext field is available. When I run as scheduled task (every 1/2 hour) from sql server agent, the data in the ntext field is truncated.
View Replies !
Copy Data From 1 Table To Other In Stored Procedure In Sql Server
Hi there, Can u please tell me how to copy data from table A(database A) to table B(databaseB) which table A contain 10 fields but table B consist of 11 fields. I have to insert current date and time into another field in Table B (which has extra field compare to tableA) automatically every hour or so. Please help. Thanx
View Replies !
Array Data Type In SQL Server Stored Procedures?
Is there any array data type in SQL Server 7.0. I am using VB 6.0 with ADO 2.1. I am populating a MSFlexGrid with values that I pass to SQL Server one at a time and insert into the database. What I would like to do is pass the entire contents of the Grid at once to a stored procedure and let SQL do the processing so my routine is not going back and forth to the client. I did not find any documentation on any array data types in SQL. What is my best approach to this problem? Thanks, Dan Collins
View Replies !
Problem To Retrieve Data From SQL Server 7.0 Via Stored Procedure
I'm writing VB 6.0 codes to retrieve data from SQL Server 7.0. if the sql code is embedded in VB, everything works fine. But when I tried to use stored procedure, one error called "invalid object name 'author'" occurs. author is a table in my database and obviously is in the database. what's wrong? appreciate for any suggestion! ...mike
View Replies !
Stored Proc Requesting Data Via An Exchange Linked Server
Hello, I have created a MS Exchange 2000 link server in my MS SQL Server 2k. I have created a stored procedure (and a view...) using info from that linked server. When I am logged on the server as the Administrator, I can call my stored proc without any problems. When I use another computer (and I am not logged as the admin of the server) and I call the stored procedure, the following error is always raised : Server: Msg 7302, Level 16, State 1, Procedure test_proc, Line 3 " Impossible de créer une instance du fournisseur OLE DB 'exoledb.DataSource.1'. " <== I know it is a french error but it can be translated as : "Unable to instancied the OLE DB 'exoledb.DataSource.1' provider" I would like to know if I can make run my stored proc in the admin account or what should I do to make it work
View Replies !
DTS Or Import Data Into SQL Server 7/2000 From Oracle Stored Proc Returning Resultset
Dear Brains of the Universe. Here is my challenge or I think it is that I am challenged. The scenario is, after days of searching I have finally managed to find how to return resultsets from an Oracle stored procedure. What I want to do is get this resultset into an SQL Server table using T/SQL or DTS. 1) Question 1. Is this possible? (I bet there is a way to do it, but just dunno how..) 2) Question 2. Hmmm, Is there any other way I can achieve what I want to achieve? Ok here is the SQL Script for the Oracle Package, function and statement to view the resultset in oracle. (remember to use SQL* Plus). /* ||Package Creation */ create or replace package Types as type cursorType is ref cursor; end; / /* ||Function Creation */ create or replace function sp_ListEmp return types.cursorType as l_cursor Types.cursorType; begin open l_cursor for select ename, empno from emp_order by ename; return l_cursor; end; / /* ||View Resultset */ variable c refcursor exec :c := sp_ListEmp print c I hope this enuff to explain my pridacament. Any help would be most gracefully appreciated. Thanks in advance.
View Replies !
SQL Injection - How To Prevent It?
I am building my first ASP.Net app from scratch and while working on the DAL I came across the problem of SQL Injection. I searched on the web and read different articles but I am still unsure about the answer. My question is should I add db.AddInParameter(dbCommand, "AvatarImageID", DbType.Int32, avatarImageID); Add in Parameters to my C# code to avoid SQL Injection. What is the best practice. I am unclear if the stored procedure already helps me avoid SQl Injection or if I need the add in parameters in the C# methods to make it work. I need some help. Thanks, Newbie My C# update method in the DAL (still working on the code) private static bool Update(AvatarImageInfo avatarImage) { //Invoke a SQL command and return true if the update was successful. db.ExecuteNonQuery("syl_AvatarImageUpdate", avatarImage.AvatarImageID, avatarImage.DateAdded, avatarImage.ImageName, avatarImage.ImagePath, avatarImage.IsApproved); return true; } I am using stored procedures to access the data in the database. My update stored proc set ANSI_NULLS ON set QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[syl_AvatarImageUpdate] @AvatarImageID int, @DateAdded datetime, @ImageName nvarchar(64), @ImagePath nvarchar(64), @IsApproved bit AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; BEGIN TRY UPDATE [syl_AvatarImages] SET [DateAdded] = @DateAdded, [ImageName] = @ImageName, [ImagePath] = @ImagePath, [IsApproved] = @IsApproved WHERE [AvatarImageID] = @AvatarImageID RETURN END TRY BEGIN CATCH --Execute LogError SP EXECUTE [dbo].[syl_LogError]; --Being in a Catch Block indicates failure. --Force RETURN to -1 for consistency (other return values are generated, such as -6). RETURN -1 END CATCH END
View Replies !
Prevent SP Compilation
Hi,I'm using SQL Server 2000 MSDE on a laptop running Windows XP.I have a couple of SP's that that quite some time to compile. So I waswondering: is there any way to have the database *not* recompile them everytime after a reboot?BOL says: "As a database is changed by such actions as adding indexes orchanging data in indexed columns, the original query plans used to accessits tables should be optimized again by recompiling them. This optimizationhappens automatically the first time a stored procedure is run afterMicrosoft® SQL ServerT 2000 is restarted."Now the SQL Server is restarted a lot, because laptops don't have endlessbatteries <g>Cheers,Bas
View Replies !
Prevent Sql Job Failure.
Have a job that calls a DTS package, DTS is an Export & Import wizard to copy tables. Someone deleted a table from source and my job failed last night. Inputs appreaciated.
View Replies !
How To Prevent Db Deletion
Hi I want to try and protect myself from my own stupidity. I have a number of sql databases, but one is LIVE. It is easy to drop tables but I want to set something (e.g. a password) which will help prevent me from dropping tables on the live database. Any help/direction here would be appreciated.
View Replies !
Best Way To Prevent Deadlocks
I'm going thru my application log, and just seeing what errors are popping up. I have a relatively intense search feature, thats causing alot of deadlocks. Exception type: SqlException Exception message: Transaction (Process ID 105) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction. In general, what's the best way to resolve this ? Should I see if I can apply "WITH (NOLOCK)" to my data ? Any suggestions are greatly appreciated ! thanks again! mike123
View Replies !
Prevent Duplication On UPDATE
Hello I noticed a spelling mistake in the data in a column of several tables, I used the following syntax to alter the spelling: UPDATE [dbo].[Prod_Cat] SET [ProdName]=N'merseyside' WHERE ProdName = 'mmserseyside' The above code correctly updated the spelling error, but it also inserted a new row with the corrected data. So I found myself with two Identical rows containing the corrected information. I had to manually delete the extra row. Because if I had put in a DELETE statement, I would have then lost both rows. What do I need to do to prevent this happening next time. As I find that I need to update the names of some products, but I don't want to duplicate them. Thanks Lynn
View Replies !
How Do I: Prevent Race Condition?
I want to be able to read and update a value in the database without entering a race condition. For example: User #1 reads a row from the database, changes a value then writes the value back. User #2 reads the same row AFTER user #1 has read it, but BEFORE user #1 writes it back. User #2 then changes the value and writes it back, overwriting the value that user #1 wrote. I thought I could do this with transactions, but it just makes user #2 wait until user #1 is done writing before user #2 can write. It doesn't stop user #2 from reading while user #1 has it out. Does that make sense?
View Replies !
How To Prevent SQL Injection Attacks
Hi, On my site I have a simple textbox which is a keyword search, people type a keyword and then that looks in 3 colums of an SQL database and returns any matches The code is basic i.e. SELECT * FROM Table WHERE Column1 LIKE %searcg% There is no validation of what goes into the text box and I am worried about SQL injection, what can I do to minimize the risk I have just tried the site and put in two single quotes as the search term, this crashed the script so I know I am vunerable. Can anyone help, perhaps point me in the direction of furthur resources on the subject? Thanks Ben
View Replies !
What Are Sql Injection Attacks And How To Prevent?
this is a question I put in the sql community in microsoft, but havent be answered in full ------------ I am using dynamic sql to do a query with differents 'order' sentences and/or 'where' sentences depending on a variable I pass to the sp ex: create proc ex @orden varchar(100) @criterio varchar(100) as declare consulta varchar(4000) set consulta=N'select pais from paises where '+@criterio' order by '+@orden ------------ I'd like to know it it uses 2 sp in the cache, as I read, the main sp and the query inside the variable of the dynamic sql. if so, as I imagine, then I suppose I have to do the main sp without any 'if' sentence to be the same sp, and so taking it from the cache and not recompile the sp now, I have various 'if' sentences in the main sp (the caller of the dynamic sql) but I plan to remove them and do the 'if' by program -it is in asp.net-, so I suppose it is better because in this way the main sp is took from the cache, supposing this uses the cache different that the dynamic sql in the variable what do u think? does the dynamic sql use 2 caches? if so, u think it is better to try to do the main sp same in all uses (no 'if' statements)? ----- They told me this coding is not good (dynamic sql) because it can give control to the user? I ask, how does it give control to use? what ar sql injection attack and how to prevent them? I use dynamis sql because I have 150 queries to do, and thought dynamic sql is good is it true that dynamic sql have to be recompiled in each execution? I suppose so only if the sql variable is different, right? can u help me?
View Replies !
Prevent DELETE And/or UPDATE
Hi all!Are there any other way than using rights or Triggers to prevent aDELETE or an UPDATE on a specific column.The "problem" with rights is that they dont apply to all DB-usersThe "problem" with triggers is that they generate lots of extraSQL-codeI would like a solution something like below. If there are anyprimitives like this or other more neat solutions I would be glad toknowCREATE TABLE some_table NO DELETE/* ^^^^^^^^^*/(some_column SOME_TYPE NO UPDATE/* ^^^^^^^^^*/)For clarity, here is a trigger that currently solves the problemCREATE TRIGGER check_updateable_columns ON some_tableFOR UPDATEASIF UPDATE(some_column)RAISERROR(...)GOorCREATE TRIGGER delete_not_allowed ON some_tableINSTEAD OF DELETEASRAISERROR(...)GO
View Replies !
Prevent One Single Row From Being Updated
I am doing customization for microsoft POS. I manually added a recordto a table. The manage and maintenance of this table are done by POS,and user can update the contents of this table. Is there any way I canlock this single row at database level to prevent it from being deletedor changed by user? I am using SQL 2000 and vb.net.Thanks.Leanne
View Replies !
Trigger To Prevent Duplicates
Hi all, I'm writing a trigger to prevent duplicates. I know that this can be done through primary key or unique constraints but in the real world my uniqueness is defined by 8 columns which is too a big an index to maintain on the primary / unique key. If I create a table with 2 columns CREATE TABLE Table1 (CentreCHAR(10), Month CHAR(3) ) Then create a trigger to prevent duplicates CREATE TRIGGER trigger_Check_Duplicates ON Table1 FOR INSERT, UPDATE AS -- This trigger has been created to check that duplicate rows are not inserted into AudioVisual table. DECLARE @IsDuplicate INTEGER -- Check if row exists SELECT @IsDuplicate = 1 FROM Inserted i, Table1 t WHERE t.Centre = i.Centre AND t.Month = i.Month IF (@IsDuplicate = 1) -- Display Error and then Rollback transaction BEGIN RAISERROR ('This row already exists in the table', 16, 1) ROLLBACK TRANSACTION END Then insert a row into the new table (no other data is in there) INSERT Table1 VALUES('0691040176','AUG') I get the Trigger error message that the row already exists. Why is this the case? I though that Table 1 (target table) would show no entries as it has no data - it should be a before image of the table and the inserted table should be an after image. Please help!!! Thanks Neill
View Replies !
Prevent ODBC By USER/APP
Is there a way to monitor all ODBC connections to a MSSQL server and prevent a username/ODBC combination. My problem is that we have many frontends for viewing reports, but we managed them all and users are not allow to make their own connections. Some users now uses MSAccess over ODBC to draw their own reports - they have all the permissions as neededd by the other apps. thanx
View Replies !
Prevent SQL/CLR Auto Deploy
Hi, I have a solution (VS 2005, C#) that includes a SQL 2005 CLR project. When I run the solution (in debug mode, and with a test harness in a different project in the solution set as the startup project) the IDE often wants to deploy my CRL assembly. How do I tell it NOT to try to do the deployment? (i.e. to only do it when I right-click the project and tell it to). Cheers,
View Replies !
Is That Possible To Prevent Modifying The Name Of An IDTSInputColumn90?
Hi, I'm building a custom SSIS data flow component and I create myself input and output columns from a custom property of my pipeline component. That's why I don't want the user to modify the name of the input and output columns by using the advanced editor. Is there a way either to make input/output column properties - at least the name - readonly or to override any PipelineComponent method to throw an exception like we do when we want to prevent the user from adding/removing input/output colulmns with methods like PipelineComponent.DeleteOutputColumn? Thanks for your help, David
View Replies !
Prevent MS Access To Connect
Hello I'm facing a dilemma. I have quite a lot of users who need read access to data for analysis purposes. By granting them read access you also give them the option to connect to the database using MS Access which puts locks on the data. First I thought of generating stored procedures for all tables within a database those users need to query but when users execute a stored procedure with the name of the table, they get a resultset of all the data. They cannot filter the data by for example using select top 100* from usp_table where name like '%worf%' I'm sure I'm not the only one here but I don't see any alternative then grant them those rights. Thanks! Best Regards, Worf
View Replies !
Is It Possible To Prevent Databases From Being Copied?
Hi, We have a point of sale application (C# .NET 2.0) and a Sql Server 2005 database back end. Our customers are concerned that employees could create a backup of the SQL Server database (or even of the MDF file) and use it to steel customer data. Very often, the application is running on a single PC in a shop using Sql Server Express Edition 2005 under Windows XP. The users usually log on as local administrator. It's hard for us to force our customers to change their local security policies. Ideally, I would like some form of security mechanism that prevents a backup from being restored on to another PC without either a password or some other form of authentication. Is this possible? Regards, Sigol.
View Replies !
|
TRACKER
