Securing data with application role – codeproject

This article introduces the concepts behind SQL Server 2008 Application Role. Database gui Besides the concepts, the article presents the following aspects around creating and using application roles.

Application role is a database level principal.

Data recovery technician It may own one or more database schema and be granted specific permissions. Database engine tuning advisor The application accesses data with the permissions of the application role irrespective of who is connected to the database. Data recovery rates The application role needs to be enabled for it to be active. Database developer salary This provides a level of security for the data and other database objects. Database backup and recovery Any one wishing to access data using the application as a gateway will be restricted by the application role that lives during the life of the current application instance. Yale b database 3. Sybase database Sample Application – An Illustrative Example

The best way to understand the application role is to work with an actual application that uses application role to access the database. Database design So, let me present an actual Windows Forms based application that controls read-only access to the HumanResources.Employee table in the AdventureWorks database. Database 3 normal forms Note that HumanResources is the schema under which the Employee table exists. Database for dummies I used AdventureWorks database, but you may create your own database and table and use it if you like. A database is a collection of The sample application I present is simple enough to be modified for your SQL Server instance, database and tables. Library database 3.1 Create an Application Role

Open SQL Server Enterprise Manager console connecting as administrator and expand the AdventureWorks sample database (You may download and install AdventureWorks sample database available online, if you do not already have it on your SQL Server instance). Database node Expand Roles node under Security. Data recovery after format Right click the Application Roles node and click New Application Role… Database 2015 menu. Data recovery nyc The dialog under the General tab to create the role is shown in the figure below:

I have named the role as HRUPDATER. Database weekly Select the schema as HumanResources and the database schemas owned by the role as db_datareader and db_datawriter. Data recovery utah Now, select the Securables tab below the General tab. Data recovery deleted files This displays the dialog for you to select the database object you want to secure and grant necessary permission to the HRUPDATER role on the object. Database health check Select the Employee table under HumanResources schema as shown in the dialog below by clicking the Search… R studio data recovery software button. Data recovery iphone Then check the Select permission under the Grant column.

Create a user under AdventureWorks database from an existing login. Ease use data recovery For this example, I created a login called sqluser and used the same name to create a database user. Free database software Note that this user does not have any permission at all on any of the AdventureWorks database objects. Raid 0 data recovery software Add this login as a user under the master database, name it as sqluser and give it execute permission for the two system stored procedures namely, sys.sp_setapprole and sys.sp_unsetapprole. Mail database This is done to allow the same user who is logged in to the database to enable and disable the application role. Hdata recovery master This is done for illustration only. In database 3.2 Create a Windows Form Application

Create a simple Windows Forms application. Drupal 7 database query I used Visual Studio 2008, C# and .NET 3.5 to create an application as shown in the figure below. Data recovery usa The simple form includes a DataGridView control and three buttons as shown.

The form on load event opens an SQL connection to the AdventureWorks database. Data recovery business The connection string is configured in the app.config file of the application. Database visualization Once the connection is open, it is cached in a form level variable. Data recovery qatar Once the form is launched, do the following to verify the functioning of the application role.

Click the Display button. Data recovery no root The click event of the Display button uses the current connection of the user and tries to fetch the Employee records from the Employee table. Database keywords Recall that the user sqluser does not have any read access to the Employee table. Normalization in database Thus, the following access prohibited dialog is displayed.

Now, click Ok on the error dialog and click the Enable AppRole button. Database 3nf This button click event executes the sys.sp_setapprole procedure with required parameters and stores the returned cookie in a byte array in a form scoped variable. Database server This cookie is used to disable the application role. Data recovery wd passport After enabling the application role, click the Display button again and this time the grid view will be populated with the results of the SQL query.

Now, click the Disable AppRole button. Relational database management system The button click event will clear the grid view of data and then disable the application role using the cookie. Database generator Now, if you click the Display button, the access to the Employee table data will be prohibited and an error will be generated. Database 3nf example In this example, application role provides more access than the user permission. Data recovery hard drive software A reverse situation can also be tested where the user can access and display the data but the application role will prohibit access. Iphone 4 data recovery 4. Database logo Code Discussion

The following are code snippets for the discussion in this article. Moto g data recovery You can also download the complete solution, modify the connection parameters and test on your local computer. Database analyst salary // Reading the connection string variable from app.config private string ReadConfigurationString()