Solved

How to protect a database from being accessed from Excel?

Posted on 2000-04-07
5
277 Views
Last Modified: 2008-02-01
We have lots of pc based applications developed using VB. The programs resides on the our NT server and accessed by multiple users.  Once a user a allowed to access the program, he has also access to the database location. This exposes the databases since this can be accessed using the excel or any other compatible database.
Question: what options do we have to protect the database from being viewed, amemded deleted and copied.


0
Comment
Question by:elmerpevidal
5 Comments
 
LVL 6

Accepted Solution

by:
crsankar earned 200 total points
Comment Utility
This may help you.

Application Security and Application Roles

The security system in Microsoft® SQL Server™ is implemented at the lowest level, the database itself. This is the best, most robust method for controlling user activities regardless of the application used to communicate with SQL Server. However, you may have situations when security controls must be customized to accommodate the special requirements of an individual application, especially when dealing with complex databases and databases with large tables.

Additionally, you may want users to be restricted to accessing data only through a specific application without the ability to access data directly, for example using SQL Server Query Analyzer or Microsoft Excel. This prevents a user from connecting to SQL Server using an application such as SQL Server Query Analyzer and executing a poorly written query, which affects the performance of the whole server.

SQL Server accommodates this situation through the use of application roles. The fundamental differences between standard and application roles are:

Application roles contain no members. Users, Microsoft Windows NT® groups, and roles cannot be added to application roles; the permissions of the application role are gained when the application role is activated for the user’s connection through a specific application(s). A user’s association with an application role is due to being capable of running an application that activates the role, rather than being a member of the role.
Application roles are inactive by default and require a password to be activated by using the sp_setapprole system stored procedure. The password can be provided by the user, for example, through an application prompt, but it is more likely that the password is incorporated within the application. The password can be encrypted as it is sent to SQL Server.
When an application role is activated for a connection by the application, the connection permanently loses all permissions applied to the login, user account, or other groups or database roles in all databases for the duration of the connection. The connection gains the permissions associated with the application role for the database in which the application role exists. Because application roles are applicable only to the database in which they exist, the connection can gain access to another database only by virtue of permissions granted to the guest user account in the other database. Therefore, if the guest user account does not exist in a database, the connection cannot gain access to that database. If the guest user account does exist in the database but permissions to access an object are not explicitly granted to guest, the connection cannot access that object regardless of who created the object. The permissions the user gained from the application role remain in effect until the connection logs out of SQL Server.

--------------------------------------------------------------------------------

Important It is necessary for a connection to lose default permissions applied to the login/user account or other groups or database roles in all databases for the duration of the connection and gain the permissions associated with the application role to ensure that all the functions of the application can be performed. For example, if a user is usually denied access to a table that the application must access, then the denied access should be revoked for the user to successfully use the application. Application roles overcome any conflicts with user’s default permissions by temporarily suspending the user’s default permissions and assigning them only the permissions of the application role.


--------------------------------------------------------------------------------

Application roles allow the application to take over the responsibility of user authentication, rather than SQL Server. However, because SQL Server still needs to authenticate the application when it accesses databases, the application must provide a password because there is no other way to authenticate an application.

If ad hoc access to a database is not required, users and Windows NT groups do not need to be granted any permissions because all permissions can be assigned by the applications they use to access the database. In such an environment, assuming access to the applications is secure, standardizing on one system-wide password assigned to an application role is possible.

There are several options for managing application role passwords without hard-coding them into applications. For example, an encrypted key stored in the registry (or the SQL Server database), for which only the application has the decryption code, can be used. The application reads the key, decrypts it, and uses the value to set the application role. Using the Multiprotocol Net-Library, the network packet containing the password can also be encrypted. Additionally, the password can be encrypted, before being sent to SQL Server, when the role is activated.

When an application user connects to SQL Server using Windows NT Authentication Mode, an application role is a good way to set the permissions the Windows NT user has in a database when using the application. This allows Windows NT auditing of the user account and control over user permissions, while they use the application, to be easily maintained.

If SQL Server Authentication is used and auditing user access in the database is not required, it can be easier for the application to connect to SQL Server using a predefined SQL Server login. For example, an order entry application authenticates users running the application itself, and then connects to SQL Server using the same OrderEntry login. All connections use the same login, and relevant permissions are granted to this login.


--------------------------------------------------------------------------------

Note Application roles work with both authentication modes.


--------------------------------------------------------------------------------

Example
As an example of application role usage, a user Sue runs a sales application that requires SELECT, UPDATE, and INSERT permissions on the Products and Orders tables in database Sales to work, but she should not have any SELECT, INSERT, or UPDATE permissions when accessing the Products or Orders tables using SQL Server Query Analyzer or any other tool. To ensure this, you could create one user-database role that denies SELECT, INSERT, or UPDATE permissions on the Products and Orders tables, and add Sue as a member of that database role. You can then create an application role in the Sales database with SELECT, INSERT, and UPDATE permissions on the Products and Orders tables. When the application runs, it provides the password to activate the application role by using sp_setapprole, and gains the permissions to access the Products and Orders tables. If Sue tries to log in to SQL Server using any tool except the application, she will not be able to access the Products or Orders tables.

0
 

Author Comment

by:elmerpevidal
Comment Utility
Adjusted points from 100 to 200
0
 
LVL 1

Expert Comment

by:vujos
Comment Utility
Hi,

Some of the ruleas are :

1) Never use 'sa' (or dbo) account to connect to SQL server.

2) Create different levels of user groups on database level and assign database users minimum rights in order to perform required tasks. This you will have to coordinate and test with your VB developers.

3) Also it is important to know what type SQL login environment you are having. Is it integrated, standard or mixed. If it is integrated you will also have to involve your NT user administrator.

Hope this helps.
0
 
LVL 9

Expert Comment

by:david_levine
Comment Utility
An option which works well is to only use stored procedure. That way users don't have direct access to tables. They do have execute on the stored procedures, but they would need to know the names and what needs to be passed (and most MIS people can't figure that out ;)

David
0
 
LVL 6

Expert Comment

by:Jon_Raymond
Comment Utility
Views are used for this purpose.  You can grant permissions to a view and not to the underlying tables.  The views can query just the columns needed.  They are used the same as a table.
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Suggested Solutions

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now