|
I posted this over on the ASP.NET forums but didn''t hear much of anything. So I guess I'm cross posting....
I have an application that has stores sensitive data in an SQL server and I am currently handling this through my ASP.NET application using the encryption classes in C#.
One of the things we would eventually like to be able to do is use other programs (like Microsoft Access) to run advanced Querys on the tables and retrive the data. With the encyption being done in C#/ASP all that would be returned would be the encrypted data.
I wondering if there is a way to build a layer to encrypt/decrypt data at the database level, my searches haven't yeiled to much info (As well as a trip to Borders) as I haven't seen any books that even touch on this.
I don't know a lot about SQL right now (Mainly only MySQL so Stored Procs and all that stuff are really new to me) so I don't know if I would go about it this way or not? (Using a stored procedure)
Can anyone reccommened where I should start to learn about accomplishing this? Books are usually the best help but I'll take any kind of information that can be thrown my way
Thanks!
|
|
|
|
|
Kluch wrote:
I wondering if there is a way to build a layer to encrypt/decrypt data at the database level, my searches haven't yeiled to much info (As well as a trip to Borders) as I haven't seen any books that even touch on this.
I don't think any T-SQL statements can do that for you , why don't you write a web serice which do that encryption/encryption. It suit this situation.
Mazy
"One who dives deep gets the pearls,the burning desire for realization brings the goal nearer." - Babuji
|
|
|
|
|
Mazdak wrote:
I don't think any T-SQL statements can do that for you , why don't you write a web serice which do that encryption/encryption. It suit this situation.
If it's a web service though would you still be able to tie into it with 3rd party programs (mentioned above) and still have it decrypt the data?
|
|
|
|
|
I don't know exacly what do you mean. Any platform can comunicate with web service. So you do can do your encryption and decryption in the web serivce. I mwan you send request to web service , then it send the request to your database and get encrypted data , you decrypt data in web service and send it back to client. So no problem. I don't know exactly how to communicate from Access to a web service , if you can write VB module there , then you can use Soap ToolKit and do that. Is that clear?
Mazy
"One who dives deep gets the pearls,the burning desire for realization brings the goal nearer." - Babuji
|
|
|
|
|
This seems like a good solution. As mentioed I would have to figure out
A) How to send a request to the web service (Hopefully this could even just be my select string with some logon info or something)
B) What type of Data would be needed to send back to the client (Don't know if it would be easy as just passing a data grid)
What would be nuce is my main ASP app could use the same method just to encrypt decrypt the data. It's something to look into. Thanks!
|
|
|
|
|
Oh , thats a long topic , you can use authentication too in web serive. Chris have introduction article about web service. Web Service is not very comlpicated topic , you send/recieve any datatype.
You can use it in the same way in your ASP.NET pages too. I really too lazy to describe all these topics , they are very simple and you can find lots of info about them in books and googling.
Mazy
"One who dives deep gets the pearls,the burning desire for realization brings the goal nearer." - Babuji
|
|
|
|
|
You can create an extended procedure - basically a C function exported from a dll you write. You can then attach it to MSSQL and use it the same way you would use a stored procedure.
Look at :
http://www.codeproject.com/database/xp_md5.asp[^]
...cmk
Save the whales - collect the whole set
|
|
|
|
|
Hi,
I am not sure if I grasped your question completely. But here is my take:
I see two options:
- Have a windows or web service as a layer or interface between the outside program and your database. Since your database contains sensitive information, probably it is a good idea to allow connections only locally i.e. from your windows or web service.
- Second option would be to encrypt the data before you store it in the database. This way you do not have the hassle of encrypting an decrypting data everytime some program accesses it. You encrypt it only one time when you store it in the database and maybe decrypt it on the client side again.
Hope that helped.
Best,
Pankaj
Without struggle, there is no progress
|
|
|
|
|
Hi everyone,
I have very limited database experience. The little experience I have is on SQL server and now I have to port this database to Oracle.
I have the following questions about Oracle:
- What is the best tool to connect to an Oracle database? Something along the lines of SQL server Enterprise Manager? Right now I have the Oracle ODBC client installed and I use Access to connect to it. However, Access does not seem to be able to handle it well.
- Is it possible to change the password for an Oracle user over ODBC? What do I have to do to achieve that?
Thanks,
Sincerely,
Keith
|
|
|
|
|
- I use SQL Navigator[^], but it's a little bit expensive; they also have another solution, TOAD[^], very nice and useful.
- Yes you can. Use this SQL instruction:
ALTER USER USER_NAME IDENTIFIED BY NEW_PASSWORD
Hope this helps...
Free your mind...
|
|
|
|
|
Hi,
Thanks a lot. Have to wait till tomorrow now to try it out. Working hours are over here on my side of the world
Best always,
Keith
|
|
|
|
|
Hi Keith. Oracle does have its own set of tools for connecting as well, including its own Enterprise Manager[^] and sql query client.
|
|
|
|
|
When you install Oracle Client software, you usually have the opportunity to specify one of several flavours of installation. These range from a simple user installation, to a full blown administrator installation. One of the products installed during the admin installation is called, 'Enterprise Manager Console'. While it's not the best tool in the world, it does suffice for a lot of stuff, like password changes, schema changes, etc.
Chris Meech
We're more like a hobbiest in a Home Depot drooling at all the shiny power tools, rather than a craftsman that makes the chair to an exacting level of comfort by measuring the customer's butt. Marc Clifton
VB is like a toolbox, in the hands of a craftsman, you can end up with some amazing stuff, but without the skills to use it right you end up with Homer Simpson's attempt at building a barbeque or his attempt at a Spice rack. Michael P. Butler
|
|
|
|
|
Hi Chris,
Thanks for the reply.
I must have installed the InstantClient package from Oracle. However, I do not know which is the regular Oracle client package that I need to install. Can you point me a link to where I can download and find more about this client. I miss SQL Server... I hate corporate policies.
Thanks,
Keith
|
|
|
|
|
InstantClient is a separate package from the 10G database package. If you follow the links to download 10G, you eventually will get to a page from where you can download the Oracle Database 10g Client Release 1 (10.1.0.2) for Microsoft Windows (32-bit)[^]. I hope this is what you are looking for. You might have to signon to OTN first in order to access that link, though.
Chris Meech
We're more like a hobbiest in a Home Depot drooling at all the shiny power tools, rather than a craftsman that makes the chair to an exacting level of comfort by measuring the customer's butt. Marc Clifton
VB is like a toolbox, in the hands of a craftsman, you can end up with some amazing stuff, but without the skills to use it right you end up with Homer Simpson's attempt at building a barbeque or his attempt at a Spice rack. Michael P. Butler
|
|
|
|
|
Hi there,
Thanks Chris. I hope this is what I need. I will find out tomorrow morning
Thanks again,
Keith
|
|
|
|
|
I'm using max and where in my select statement but the problem is I want the max of all the dates for a given id but only want to return those rows that fall between certain dates.
A simplified version of the query is something like this :
<br />
SELECT MAX(BookingDate.bookingDate) AS MaxDate, MIN(BookingDate.bookingDate) AS MinDate<br />
<br />
FROM Booking <br />
<br />
WHERE (Booking.fkChannelId = ? ) AND (BookingDate.bookingDate > ?) AND (BookingDate.bookingDate < ?)<br />
<br />
But I'd like the max and min dates for all the bookings for a given channel id but only want to return rows that fall within the dates.
Life is hard
Try not! Do or do not, there is no try. - Master Yoda
|
|
|
|
|
Without knowing your data schema (you refer to two tables Booking and BookingDate in your example, but you don't join them) it's a bit difficult to know how to help.
GROUP BY and HAVING clauses may be helpful. GROUP BY allows data to be aggregated across groups, and HAVING allows you to select from the aggregated data. If WHERE applies to the data in the table, HAVING applies to the aggregated data.
Stability. What an interesting concept. -- Chris Maunder
|
|
|
|
|
Just try:
SELECT MAX(BookingDate.bookingDate) AS MaxDate, MIN(BookingDate.bookingDate) AS MinDate
<br />
FROM Booking <br />
HAVING (BookingDate.bookingDate > ?) AND (BookingDate.bookingDate < ?)<br />
WHERE (Booking.fkChannelId = ? ) <br />
Maybe the HAVING part belongs after WHERE...
Wout Louwers
|
|
|
|
|
Thank you both for your help.
I tried adding the HAVING clause instead of WHERE and got the following errors:
Column 'BookingDate.bookingDate' is invalid in the HAVING clause because it is not contained in either an aggregate function or the GROUP BY clause.
Column 'Booking.fkChannelId' is invalid in the HAVING clause because it is not contained in either an aggregate function or the GROUP BY clause.
The tables I have are as follows each with a one to many relationship with the following table:
Ad
pkAdId
Booking
pkBookingId
fkAdId
BookingDate
pkBookingDateId
fkBookingId
BookingHour
pkBookingHourId
fkBookingDateId
and heres the query as it stands :
SELECT DISTINCT
Ad.pkAdId, Ad.fileName, Ad.name,
Ad.length, Ad.lastUpdated, MAX(BookingDate.bookingDate) AS MaxDate, MIN(BookingDate.bookingDate) AS MinDate,
AVG(BookingHour.bookedSlots + BookingHour.bonusSlots) AS AverageSlots
FROM Ad INNER JOIN
Booking ON Ad.pkAdId = Booking.fkAdId INNER JOIN
BookingDate ON Booking.pkBookingId = BookingDate.fkBookingId INNER JOIN
BookingHour ON BookingDate.pkBookingDateId = BookingHour.fkBookingDateId
WHERE (BookingDate.bookingDate >= @startDate)
AND (BookingDate.bookingDate <= @endDate)
AND (Booking.fkChannelId = @channelId) AND
GROUP BY Ad.pkAdId, Ad.fileName, Ad.name, Ad.length, Ad.lastUpdated
ORDER BY Ad.fileName
Try not! Do or do not, there is no try. - Master Yoda
|
|
|
|
|
Try:
<br />
SELECT DISTINCT <br />
Ad.pkAdId, Ad.fileName, Ad.name, Ad.length, Ad.lastUpdated,<br />
MAX(BookingDate.bookingDate) AS MaxDate,<br />
MIN(BookingDate.bookingDate) AS MinDate,<br />
AVG(BookingHour.bookedSlots + BookingHour.bonusSlots) AS AverageSlots<br />
FROM Ad <br />
INNER JOIN Booking ON Ad.pkAdId = Booking.fkAdId <br />
INNER JOIN BookingDate ON Booking.pkBookingId = BookingDate.fkBookingId<br />
INNER JOIN BookingHour ON BookingDate.pkBookingDateId = BookingHour.fkBookingDateId<br />
WHERE (Booking.fkChannelId = @channelId)<br />
GROUP BY Ad.pkAdId, Ad.fileName, Ad.name, Ad.length, Ad.lastUpdated<br />
Having MinDate >= @startDate)<br />
AND MaxDate <= @endDate)<br />
ORDER BY Ad.fileName<br />
Wout Louwers
|
|
|
|
|
Hi Wout,
Thanks for your reply, unfortunatley Query Analyser complains :
Invalid column name 'MinDate'.
Invalid column name 'MaxDate'.
Also I want those bookings between the dates not whose max and min booking dates fall between the dates.
Sorry to be a pain and thanks again.
Try not! Do or do not, there is no try. - Master Yoda
|
|
|
|
|
<br />
SELECT DISTINCT <br />
Ad.pkAdId, Ad.fileName, Ad.name, Ad.length, Ad.lastUpdated,<br />
MAX(BookingDate.bookingDate) AS MaxDate,<br />
MIN(BookingDate.bookingDate) AS MinDate,<br />
AVG(BookingHour.bookedSlots + BookingHour.bonusSlots) AS AverageSlots<br />
FROM Ad <br />
INNER JOIN Booking ON Ad.pkAdId = Booking.fkAdId <br />
INNER JOIN BookingDate ON Booking.pkBookingId = BookingDate.fkBookingId<br />
INNER JOIN BookingHour ON BookingDate.pkBookingDateId = BookingHour.fkBookingDateId<br />
WHERE (Booking.fkChannelId = @channelId)<br />
GROUP BY Ad.pkAdId, Ad.fileName, Ad.name, Ad.length, Ad.lastUpdated<br />
Having MAX(BookingDate.bookingDate) >= @startDate<br />
AND MIN(BookingDate.bookingDate) <= @endDate)<br />
ORDER BY Ad.fileName<br />
??
Wout Louwers
|
|
|
|
|
Worked like a charm,
thank you very much
Try not! Do or do not, there is no try. - Master Yoda
|
|
|
|
|
1. Are there any considerations when to use untyped and typed datasets? I find typed-datasets more useful and less error prone in typo since you can call the members exactly by the name itself besides the fact that it adds some useful methods not present in its counterpart.
2. What condition should stop me from using typed-datasets? Are there limitations?
3. Does datasets supports concurrency..i.e. move the record pointer forward, backward, BOF and EOF? How about bookmarks?
|
|
|
|