Secure a single SQL Server database + Make float fields encrypted

Nicolas Iacovides

Hi all,

I am developing an application for a big office which uses SQL Server 2000.
Apart from my database, on that server, there are two databases by other companies.
The administrator also has access to server but the client only wants him to backup the database.

I have two questions:

1) First of all (if it is possible) I would like to protect my own database from the other companies.

I don't want them to:

• see the data in the tables (around 20 tables)
• make changes to the stored procedures (more than 100 stored procedures)
• be able to backup the database

2) The client will save sensitive data to the database (mainly currency amounts, salaries etc) which he wants to keep hidden.
I am using float type for these fields and I would like to make the data encrypted. I could do it for nvarchar fields but changing these float to nvarchar would be time consuming.

Thanks for your patience reading this!

Would really appreciate some help on any of these

Nicolas

Arnie Rowland

The comments below are focused on SQL 2000. Security is subtaintially enhanced in SQL 2005 with many improvements in the ability to deny even seeing the table names, etc.

1. Proper use of logins, database permissions, and roles, will allow you to have multiple databases on a single server, and prevent users of databaseA from seeing or accessing data in databaseB. (Users of DatabaseA will, however, be able to list the tables, views, stored procedures, etc., on DatabaseB -just not access the definitions. They will not be able to alter the stored procedures or make SQL Backups.

2. SQL 2000 does not have internal capability to encrypt data. There are thrid party products available. There are many 'unsound' encryption suggestions floating around, but it really can't be easily done without a third party product. (SQL 2005 allows strong encryption of fields as well as tables.)

With all of this in mind, a local administrator (or domain administrator) could easily take the database offline for a few minutes and make a copy. Then by attaching that copy to another server, he/she would have relatively easy access to all data. There is no simple method to protect a database from local administrators that are inclined to defeat your security.

On another point, you will find, over time, that float is the wrong datatype for currency -use decimal instead, unless of course, you are dealing with amounts as large as the national debt.

Bottom line: If you can't 'trust' the administrators, you have a problem.

Nicolas Iacovides

So what will be the right path to follow to create logins, database permissions, and roles I should say that the server is not using the windows authentication mode.
For now, I would be happy if I could block the other companies from accessing the database. When you say list the tables you mean just the table names Or the design of the tables

Thanks!

Arnie Rowland

Users that can login to the Server will be able to see the table definitions of databases they do not have permissions to access.

Here are some resources that will get you started on the security topic. Good luck.

Security ¨CBest Practices
http://vyaskn.tripod.com/sql_server_security_best_practices.htm

Security -Giving Permissions through Stored Procedures
http://www.sommarskog.se/grantperm.html

Security ¨CProtecting against Hacking and Password Cracking
http://www.airscanner.com/pubs/sql.pdf#search=%22sql%20server%20password%20crack%20sqlping%22