September 4-6
This presentation is the property of its rightful owner.
Sponsored Links
1 / 13

September 4-6 Ølensvåg PowerPoint PPT Presentation


  • 84 Views
  • Uploaded on
  • Presentation posted in: General

September 4-6 Ølensvåg. AppFrame Security Model. Introduction. Built on top of SQL Server Security is implemented in the database afAccessLayer adds makes it even more secure Table & Row Level Security. Appframe Users. Must have a SQL Server Login Must be member of role: af_user

Download Presentation

September 4-6 Ølensvåg

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


September 4 6 lensv g

September 4-6

Ølensvåg


Appframe security model

AppFrame Security Model


Introduction

Introduction

  • Built on top of SQL Server

  • Security is implemented in the database

  • afAccessLayer adds makes it even more secure

  • Table & Row Level Security


Appframe users

Appframe Users

  • Must have a SQL Server Login

  • Must be member of role: af_user

  • af_user has access to:

    • Select, Insert, Update, Delete on views

    • Select on Primkey and Timestamp columns in tables

    • Insert, update and delete on tables

    • Execute on functions, stored procedures and triggers

  • Are members of one or more Appframe Groups


Appframe groups

Appframe Groups

Defines what data the users have access to

Tables

Domain

Rows (based on criteria fields)

Types of access

Select

Insert

Update

Delete

Master Groups

Define domain-independent access groups

Makes it easier to administrate groups

Groups inherits access from Master Groups


Appframe main security tables

Appframe Main Security Tables


Views triggers

Views & Triggers

  • Views are used for restricting read access

  • Triggers are used for restricting update, insert, delete actions

  • Stored procedure may be used to implement security

  • atbv views – returns data that user has access to in active domain

  • atbx views – returns all data that user has access to

  • sviw_System_MyPermissionsCurrentDomain

  • sviw_System_MyPermissions


Views

Views

  • atbv: Returns data that user has access to for current domain.The following example also utilize built in row level security.

SELECT *

FROM dbo.atbl_ProjectCost_WorkPacks (NOLOCK)

WHERE EXISTS

(

SELECT * FROM sviw_System_MyPermissionsCurrentDomain (NOLOCK)

WHERE ISNULL(Domain, '') = ISNULL(dbo.atbl_ProjectCost_WorkPacks.Domain, '')

AND TableID = 'atbl_ProjectCost_WorkPacks'

AND ISNULL(dbo.atbl_ProjectCost_WorkPacks.WBS, '') LIKE ISNULL(sviw_System_MyPermissionsCurrentDomain.Criteria1, '')

AND ISNULL(dbo.atbl_ProjectCost_WorkPacks.WorkPackID, '') LIKE ISNULL(sviw_System_MyPermissionsCurrentDomain.Criteria2, '‘)

)

  • atbx: Returns all data that user has access to (Cross Domain) .

SELECT * FROM dbo.atbl_MarketMaker_Persons (NOLOCK)WHERE EXISTS (SELECT * FROM sviw_System_MyPermissions (NOLOCK) WHERE ISNULL(Domain, '') = ISNULL(dbo.atbl_MarketMaker_Persons.Domain, '') AND TableID = 'atbl_MarketMaker_Persons')


Triggers

Triggers

  • Generated security sections for Insert, Update and Delete triggers.

  • Built-in support for data logging

  • Always remember to create triggers

  • Triggers needs to be regenerated after modifying criteria fields


Simple insert triggers overview

Simple Insert Triggers Overview

CREATETRIGGER [dbo].[stbl_ELearning_Tests_ITrig]

ON [dbo].[stbl_ELearning_Tests]

FORINSERT

AS

/* ----- AUTOMATIC GENERATED SECTION. DO NOT MODIFY BETWEEN THESE LINES. ----- */

/* afbm=TopStart */

/* Script generated: 29-aug-2007 14:30:01 */

IF@@RowCount= 0 RETURN

IFIS_MEMBER('db_owner')= 1 GOTO ContinueTransaction

IFEXISTS(SELECT MyP.*FROM dbo.sviw_System_MyPermissions MyP

WHERE MyP.TableID='stbl_ELearning_Tests'

AND MyP.PGrant LIKE'%I%')GOTO ContinueTransaction

RollbackTransaction:

DECLARE @RaisErrorUser NVARCHAR(128)

SET @RaisErrorUser =SUSER_SNAME()

RAISERROR('---- No insert permissions on table: stbl_ELearning_Tests for user %s ----',18,1,@RaisErrorUser)ROLLBACKTRANSACTION

RETURN

ContinueTransaction:

SETNOCOUNTON

/* afbm=TopEnd */

/* ----- AUTOMATIC GENERATED SECTION. DO NOT MODIFY BETWEEN THESE LINES. ----- */

………

Overrides security if user is member of SQL server [db_owner] role

Look up users table permissions

Insert denied

Insert granted


Keep in mind

Keep in Mind

Note that read permissions are bypassed when performing SELECT statements against tables within views, triggers, stored procedures and functions.

CREATE PROC [dbo].[astp_MySpace_MyProc]

AS

SELECT * FROM atbl_MySpace_MyTable

CREATE VIEW aviw_MySpace_MyView

AS

SELECT * FROM atbl_MySpace_MyTable


Sql injections

SQL Injections

  • Since security is implemented in the database, impact from SQL Injection is minimized

  • R3’s afAccessLayer prevents potential dangerous SQL Statements to be passed on to the server

  • Typical Appframe Scenario (Today):

    • Inside Corporate Firewall

    • Open for other clients (MS Access, Management Studio etc) for all users

    • Preventing SQL Injections does not increase security significantly

  • Getting more common:

    • Hosted solution outside corporate firewall

    • Only open for secure https traffic

    • Preventing SQL Injection adds another security layer

    • Resource: ScottGu’s blog: Guard against SQL Injection Attacks

  • If Impersonation is used: Preventing SQL Injection is critical!


Security modes

Security Modes

  • Windows Integrated Security or SQL Logins

  • Best Practice: Use Windows Integrated security

    • Achieves single sign-on

    • Simplifies login administration

    • Password management uses the ordinary Windows password policies and password change APIs

    • SQL Logins security improved in SQL Server 2005

    • No passwords are passed over the network

  • Prerequisites for Integrated Windows Security

    • SQL Server must be part of the Active Directory (AD) domain

    • Web Server must be part of the AD Domain

    • All clients must be logged onto the AD domain

      For more information:

  • developer.appframe.com: Setting up Integrated Windows Security


  • Login