1 / 13

September 4-6 Ølensvåg

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

wenda
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. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. September 4-6 Ølensvåg

  2. AppFrame Security Model

  3. Introduction • Built on top of SQL Server • Security is implemented in the database • afAccessLayer adds makes it even more secure • Table & Row Level Security

  4. 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

  5. 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

  6. Appframe Main Security Tables

  7. 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

  8. 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')

  9. 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

  10. 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

  11. 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

  12. 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!

  13. 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

More Related