Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

Towards the innovative SQL ideas
Browse by Tags · View All
sql server 125
sql 124
ms sql server 119
ms sql 118
database 109
tsql 81
#SQL Server 78
t-sql 75
#sql 71
sql server general 67

Archive · View All
April 2011 14
July 2011 12
May 2011 12
August 2011 11
June 2011 10
September 2011 8
December 2011 6
November 2011 6
September 2013 5
June 2013 5

Working with Application Role - SQL Server

Jan 20 2012 8:01AM by Paresh Prajapati   

In last post we have seen custom database roles as how can we create it and assign required access to users. We also noticed that we can add multiple members with same role. That was the security with database roles and members comes into the picture. Now here we will study of Application Role. This is the security for the application level and no such members comes into the picture.

Application Role :
As per msdn, An application role is a database principal that enables an application to run with its own, user-like permissions. You can use application roles to enable access to specific data to only those users who connect through a particular application.

Workaround:
We can implement application role and take into effect with following steps, I am going to here with some of examples, so like create those required objects, so we can set them with application role.

1. Create required objects
USE demo
GO

CREATE TABLE SampleTable1
(
Id int,
Name varchar(10)
)
GO

CREATE TABLE SampleTable2
(
Id int,
Name varchar(10)
)
GO

CREATE PROCEDURE SampleSP1
AS 
BEGIN
SET NOCOUNT ON
SELECT * FROM SampleTable1
End
GO

CREATE PROCEDURE SampleSP2
AS 
BEGIN
SET NOCOUNT ON
SELECT * FROM SampleTable2
End
GO

Here we have created same required objects as we created in earlier post for database role.

2. Create an application role
USE demo
GO

-- sp_addapprole [@rolename = ] 'rolename', [@password = ] 'password'

EXEC sp_addapprole 'AppRole', 'AppPwd'
GO

3. Add permission to this application role
USE demo
GO

GRANT SELECT ON SampleTable1 to AppRole;
GRANT SELECT, INSERT,UPDATE ON SampleTable2 to AppRole;
GRANT EXEC ON SampleSP1 to AppRole;
GO

You can see we have assigned same access to application role as we did in earlier post for the custom database role. Application role is created here now, You can also create/view with expanding Roles inside security tab for particular database and inside it you can find Application Role tab.
































4. Connecting database and activating application role
Application roles are enabled/activated by sp_settapprole system stored procedure and it has required password.So application can be connected to SQL Server with this application role with scope of particular session and required password to authenticate it to connect SQL Server. To authenticate application roles and activate it it should be required to use in .Net connection code or other application database connection method code. You can refer the link here to use application role in application code.

sp_settapprole system stored procedure activate the application role for the specific connection while connecting with application and the syntax is as following,
USE demo
GO

-- sp_setapprole [@rolename = ] 'rolename', [@password = ] 'password'

EXEC sp_setapprole 'AppRole', 'AppPwd'
GO

We can change the password for the application role with following,
USE demo
GO

-- sp_approlepassword [@rolename = ] 'rolename', [@password = ] 'new password'

EXEC sp_approlepassword 'AppRole', 'AppChangedPwd'
GO

Hope these all the steps are enough to implement application role. Stay tuned for more.

Tags: sql server, ms sql, ms sql server, t-sql, #SQL Server, #sql, database, sql server general, SQL Scripts, login, query, Security,


Paresh Prajapati
6 · 23% · 7465
4
 
0
Lifesaver
 
0
Refreshed
 
 
0
Incorrect



Submit

1  Comments  

  • Here's the problem with Application Roles. They are almost exactly the same as everyone using the same SQL Server Login and password, something that is listed as a Worst Practice. The only difference is the user connects to the server under their own credentials (either Windows Authentication or a SQL Login), then switches to the Applicaiton Role's security context (so it is possible that someone has the Application Role and password, but won't be able to connect).

    The other downside is the user is interacting with the database under the security context of the Application Role. Any rights assigned to the user's Login or User are completely ignored. This can make security audits and troubleshooting very confusing if the use of Application Roles is not clearly documented and the documentation is accessible.

    My question around Application Roles has always been: how is this superior to using user-defined Database Roles and adding AD Users, AD Groups or SQL Users to the user-defined Database Role?

    commented on Feb 12 2012 4:39PM
    Marc Jellinek
    95 · 2% · 586

Your Comment


Sign Up or Login to post a comment.

"Working with Application Role - SQL Server" rated 5 out of 5 by 4 readers
Working with Application Role - SQL Server , 5.0 out of 5 based on 4 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]