|
Dear All,
We created a windows application using Sqlserver2000,We have to deploy it at client side. My question is, the client should not be abel to open the database in any way, even if he well known with SqlServer. How do we secure it?
|
|
|
|
|
alav wrote: We created a windows application using Sqlserver2000,We have to deploy it at client side. My question is, the client should not be abel to open the database in any way, even if he well known with SqlServer. How do we secure it?
If you prevent the client from connecting to the database you also prevent the application you deployed on the client from connecting to the database.
There is no such thing as 100% security. If your application can get through then there is a risk that something else can get through also. The solution is to minimise that risk and design the database as if the client has been compromised. Since you are talking about the creation of the windows applicaiton in the past tense I'm guessing it is too late to do much about that. So, what follows are some options you may consider if you were starting again:
* Have the application connect to a specific account on the SQL Server and do not share that information with anyone. Pros: You don't have to give permission to a windows account as the user name and ID are only issued for the one application. Cons: You then have to manage the security of the user name and password which must get installed on the client machine somehow.
* Access the database via a web service. The Web service can then validate all requests to ensure they are acceptable and won't damage the database. The database only needs to give permission to the webservice for access. Pros: A third party arbitrates all requests. Cons: Adds an extra layer that will slow down communication. The web service still has to authenticate the client somehow.
* Stucture your database so no one gets direct access to any of the tables. They must uses stored procedures that validate the input. Pros: The database can go some way to protecting itself. Only a well defined specific set of changes can be made to the database. Cons: Some of the business logic ends up in the database.
|
|
|
|
|
Hi all,
1.can any one please explain me what are this DTS? and why we use this DTS?
2.what is the difference b/w Stored procedure & Function?
3.what are Cursors?
4. what is a join and it's types?
Thanks,
Rahi
If you look at what you do not have in life, you don't have anything,
If you look at what you have in life, you have everything... "
|
|
|
|
|
Rahithi wrote: 1.can any one please explain me what are this DTS? and why we use this DTS?
Data Transformation Services. Supersceded by SSIS (SQL Server Integration Services). It allows you to extract, load and transform data between different databases.
Rahithi wrote: 2.what is the difference b/w Stored procedure & Function?
A function must be deterministic. A stored procedure doesn't have to be.
Stored procedures are more flexible in what you can do within them.
Functions can be used inside a SELECT statement, but stored procedures can't.
e.g.
SELECT * FROM dbo.FunctionName(1, 2, 3)
Rahithi wrote: 3.what are Cursors?
Cursors provide a way to iterate over a result set in a procedural way. They are slow and should be avoided unless you cannot do what is required in a set based operation.
Rahithi wrote: 4. what is a join and it's types?
A join is a way of combining the data in two or more tables into one result set.
For more details see Types of Join[^]
|
|
|
|
|
a google is highly recommended
|
|
|
|
|
Unless you're just asking out of curiosity you'll really need to dig into a good book which addresses each of the topics in detail. Off the top of my head I'd say SQL Server Unleashed would be a good place to start. It should address each and every one of those questions and show you how to use them as well.
|
|
|
|
|
Is there any way to define Optional paramter in SQL server SP.??
CREATE PROC [ EDURE ] procedure_name [ ; number ]
[ { @parameter data_type }
[ VARYING ] [ = default ] [ OUTPUT ]
] [ ,...n ]
[ WITH
{ RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ]
[ FOR REPLICATION ]
AS sql_statement [ ...n ]
Thanks
Amit
|
|
|
|
|
Amit Kumar G wrote: Is there any way to define Optional paramter in SQL server SP.??
Yes, and you've already answered your question
Amit Kumar G wrote: [ = default ]
Here is an extract from the documentation, which you obviously have since you've quoted from it:
default
Is a default value for the parameter. If a default is defined, the procedure can be executed without specifying a value for that parameter. The default must be a constant or it can be NULL. It can include wildcard characters (%, _, [], and [^]) if the procedure uses the parameter with the LIKE keyword.
|
|
|
|
|
|
Hi,
To illustrate the problem, let's say we have those two tables:
table1
id cost
===============
1 100.15
2 577
3 214.975
4 894.12
table2
id cost
===============
1 217.13
2 577
3 214.975
4 214.975
I want to select all rows from both tables that don't have an equivalent value in the cost field .. for example, in table1 we have the value 100.15 which doesn't exist in table2 therefore we want it to show in the results, the tricky part here is that if you look at table2 you'll find that the value 214.975 exist twice while it only exists once in table1, I want it to show only once in the results.
To make it clearer, this is an accounting application, we have two accounts that should be identical, so we want to select all amounts from both accounts that have no equivalent in the other account.
Your help is really appreciated
Wal
|
|
|
|
|
check these queries:
for table1:
Select distinct cost<br />
from table1 t1<br />
where (Select count(*) from table2 t2 where t2.cost=t1.cost) <> (Select count(*) from table1 t11 where t11.cost=t1.cost)
results:
100.15
214.975
894.12
for table2:
Select distinct cost<br />
from table2 t2<br />
where (Select count(*) from table1 t1 where t1.cost=t2.cost) <> (Select count(*) from table2 t22 where t22.cost=t2.cost)
results:
214.975
217.13
note that these queries are not great from the performance point of view.
cursors can do the work too..
|
|
|
|
|
hspc wrote: check these queries:
for table1:
Select distinct cost
from table1 t1
where (Select count(*) from table2 t2 where t2.cost=t1.cost) <> (Select count(*) from table1 t11 where t11.cost=t1.cost)
results:
100.15
214.975
894.12
Thanks for your help, but well, actually this is not quite what I'm trying to do. In the results above, 214.975 shouldn't be showing as it already exists in table2, it should only show if it occurs in table1 more times than it occurs in table2 ... e.g. if it occurs 7 times in table1 and 4 times in table2 then it should show 3 times in the results, can that be achieved?
Wal
|
|
|
|
|
Hai,
In my product table
pr_id Pr_ty pro_name
1001 video film au
1002 audio film vd
1003 drinks cocacola
1004 video songs_vd
1005 audio songs_au
i have above records if i select pr_ty i want to display only 3 records like video,audio and drinks only ...
I am new in sql server please help me.....
A.Rajapandian
|
|
|
|
|
SELECT DISTINCT pr_ty FROM Product
|
|
|
|
|
Thanks for your help...
A.Rajapandian
|
|
|
|
|
I have a few fields I would like to have encrypted in the database that will be displayed on a web page but will also be accessed by windows application. What is the best encryption stategy. Use the encryption tools from SQL Server 2005, or the encryption toolset from ASP.net 2.0
Right now I have been playing with using SQL server but trying to understand how to open the keys when I need to decrypt something to display it on the page or on a report. One thought is to put it all in the stored procedure but I dont know what the performance hit would be for that.
Any help or good references would be appreciated.
Thanks.
DW
|
|
|
|
|
How about the System.Security.Cryptography namespace? That way the data is being transferred to the server in encrypted form.
Just a suggestion to consider, I'm by no means an authority on SQL (or ASP for that matter  ).
I have no idea what I just said. But my intentions were sincere.
|
|
|
|
|
Thanks for the reply.
I looked at that but my problem is that I need to be able to run reports using Crystal Reports along with display the information in a web page. So I dont know how to do it without building a separate application to run the CR.
It seems the simplest way to do that would be to use the SQL server encryption.
What do you think?
|
|
|
|
|
I have a table that looks something like this
ID Site_Code Item_Code Some_Other_Stuff....
=======================================================
1 AA 123 ...
2 DD 234 ...
3 GG 567 ...
4 QQ 111 ...
4 FF 123 ...
My problem is this, I need to retrieve all the rows within a group of Site_Codes (basically all the Site_Codes that relate to say, North America), but for each site the Item_Code may reoccur. So, Item "123" appears both in "AA" and "FF" but I only want to retrieve one copy of "123" for the group. To make things a little more complicated though, "123" in "AA" and "FF" may (although they are the same item) have different values in the "Some_Other_Stuff" fields, so I have to decide whether I want the "AA" copy or the "FF" copy of "123".
So, ultimately what I want to do is set a priority order the the site codes. So I'd retrieve all rows with Site_Code = "AA" and then all rows with Site_Code in ("DD","GG",...) that have Item_Codes not in "AA". I'd image I could do this with some creative table joining, but what would be the most efficient way to do this? The database is on Oracle and unfortunately I don't have the option to change the design.
Thanks
|
|
|
|
|
wouldn't something like this work?
Select A.Site_Code, A.Item_Code
FROM MyTable A
WHERE A.Item_Code NOT IN (Select Item_Code FROM MyTable WHERE Site_Code='AA')
|
|
|
|
|
Something like that would work. In particular I can do this:
Select A.*
FROM MyTable A
WHERE A.Item_Code NOT IN (Select Item_Code FROM MyTable WHERE Site_Code='AA') AND Site_Code in ('BB','CC','DD')
UNION
SELECT * FROM MyTable WHERE Site_Code='AA'
And that mostly works. I get all the records with Site_Code='AA' plus all the records in 'BB','CC' and 'DD' with Item_Codes not in 'AA'. The problem, of course, is that there may be a few Item_Codes in 'BB' and 'CC' but not in 'AA', which means I get duplicate records for that Item_Code. There aren't very many of them, but they do exist.
I assume I could fix the problem with something like this (not yet tested):
Select * FROM MyTable WHERE Site_Code='AA'
UNION
Select A.* FROM MyTable A WHERE A.Item_Code NOT IN
(Select Item_Code FROM MyTable WHERE Site_Code = 'AA') AND Site_Code='BB'
UNION
Select A.* FROM MyTable A WHERE A.Item_Code NOT IN
(Select Item_Code FROM MyTable WHERE Site_Code IN ('AA','BB')) AND Site_Code='CC'
UNION
Select A.* FROM MyTable A WHERE A.Item_Code NOT IN
(Select Item_Code FROM MyTable WHERE Site_Code IN ('AA','BB','CC')) AND Site_Code='DD'
...
But that just seems absolutely horrible. Is there an easier way?
|
|
|
|
|
Even though it's another horrible option, You could create a temporary table that contains all site codes that are less than the current one.
I really think the problem is due to poor database design than anything else.
|
|
|
|
|
ednrgc wrote: I really think the problem is due to poor database design than anything else.
And I absolutely wouldn't disagree. But I only have limited leverage to try and use against the infrastructure people to get anything done. I'm not even sure if I have permission to create a temporary table. 
I'll probably have to end up doing a hybrid of table joins to try and catch most of the duplicates and then maybe prune out the remaining ones in code by iterating through the whole dataset. There are only a few thousand records to deal with (22K in all sites, about 8+K for all US sites).
|
|
|
|
|
Dear All,
i wanted to know is it possible to debug stored procedure as we debug in dotnet. As i have created following stored procedure to save the records.When
i Exec it it does not show any error but it does not insert the record to corresponding table. Please guide me
Stored procedure is
CREATE PROCEDURE [dbo].[servchargsav]
@branch varchar(20),
@serv varchar(20),
@scharge varchar(20),
@cname varchar(50),
@ip varchar(20)
AS
if @branch is not null
begin
if @serv is not null
begin
declare serv_cursor cursor for
select servicecharge from branchwisecharge where branchid = @branch and serviceid =@serv
open serv_cursor
fetch next from serv_cursor
if @@fetch_status = 0
begin
insert into branchwiseservice(branchid,serviceid,servicecharge,bcreatedby , bcreateddt,ipadd) values(@branch,@serv,@scharge,@cname,getdate(),@ip )
end
else
begin
update branchwisecharge set servicecharge = @scharge, bmodifiedby = @cname, bmodifieddt = getdate() where branchid = @branch and serviceid =@serv
end
close serv_cursor
deallocate serv_cursor
end
end
GO
regards
imran khan
|
|
|
|
|
You can debug a stored procedure with Query Analyzer
Wout Louwers
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
