SF.net SVN: ledger-smb:[6665] trunk/sql/modules

[..hidden..]

Revision: 6665
          http://sourceforge.net/p/ledger-smb/code/6665
Author:   einhverfr
Date:     2014-01-27 08:44:24 +0000 (Mon, 27 Jan 2014)
Log Message:
-----------
Beginnings of table select permission tests and some fixes to problems that were found

Modified Paths:
--------------
    trunk/sql/modules/Roles.sql

Added Paths:
-----------
    trunk/sql/modules/test/Roles.sql

Modified: trunk/sql/modules/Roles.sql
===================================================================
--- trunk/sql/modules/Roles.sql	2014-01-27 07:45:57 UTC (rev 6664)
+++ trunk/sql/modules/Roles.sql	2014-01-27 08:44:24 UTC (rev 6665)
@@ -121,6 +121,8 @@
 SELECT lsmb__create_role('budget_obsolete');
 
 SELECT lsmb__grant_role('budget_obsolete', 'budget_view');
+SELECT lsmb__grant_perms('budget_view', 'budget_info', 'SELECT');
+SELECT lsmb__grant_perms('budget_view', 'budget_line', 'SELECT');
 SELECT lsmb__grant_perms('budget_enter', 'budget_info', 'INSERT');
 SELECT lsmb__grant_perms('budget_enter', 'budget_to_business_unit', 'INSERT');
 SELECT lsmb__grant_perms('budget_enter', 'budget_line', 'INSERT');

Added: trunk/sql/modules/test/Roles.sql
===================================================================
--- trunk/sql/modules/test/Roles.sql	                        (rev 0)
+++ trunk/sql/modules/test/Roles.sql	2014-01-27 08:44:24 UTC (rev 6665)
@@ -0,0 +1,65 @@
+BEGIN;
+\i Base.sql
+
+-- This function is vulnerable to SQL injection but it is transient for the 
+-- purposes of these test cases. In particular it is intended only to ensure
+-- that basic permissions are tested.
+-- 
+-- IT IS THE RESPONSIBILITY OF TEST CASE AUTHORS TO ENSURE THAT THE USAGE OF
+-- THIS FUNCTION IS SAFE.
+CREATE OR REPLACE FUNCTION test__has_select_permission
+(rolname name, relspec text)
+returns bool language plpgsql as
+$$
+BEGIN
+   EXECUTE 'SET SESSION AUTHORIZATION ' || lsmb__role(rolname);
+   EXECUTE 'SELECT * FROM '  || relspec || ' LIMIT 1';
+   RESET SESSION AUTHORIZATION;
+   RETURN TRUE;
+EXCEPTION
+   WHEN insufficient_privilege THEN 
+       RESET SESSION AUTHORIZATION;
+       RETURN FALSE;
+END;
+$$;
+
+-- READ PERMISSIONS
+INSERT INTO test_result (test_name, success)
+SELECT 'budget_view can read budget_info',
+             test__has_select_permission('budget_view', 'budget_info');
+
+INSERT INTO test_result (test_name, success)
+SELECT 'budget_view can read budget_info',
+             test__has_select_permission('budget_view', 'budget_line');
+
+INSERT INTO test_result (test_name, success)
+SELECT 'file_read can read file_base', 
+        test__has_select_permission('file_read', 'file_base');
+
+INSERT INTO test_result (test_name, success)
+SELECT 'file_read can read file_links', 
+        test__has_select_permission('file_read', 'file_links');
+
+INSERT INTO test_result (test_name, success)
+SELECT 'file_read can read file_secondary_transaction', 
+        test__has_select_permission('file_read', 'file_secondary_attachment');
+
+INSERT INTO test_result (test_name, success)
+SELECT 'file_read can read file_order', 
+        test__has_select_permission('file_read', 'file_order');
+
+INSERT INTO test_result (test_name, success)
+SELECT 'file_read can read file_part', 
+        test__has_select_permission('file_read', 'file_part');
+
+-- TEST RESULTS
+SELECT test_name, success FROM test_result;
+
+
+SELECT (select count(*) from test_result where success is true)
+|| ' tests passed and '
+|| (select count(*) from test_result where success is not true)
+|| ' failed' as message;
+
+ROLLBACK;
+

This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.


------------------------------------------------------------------------------
CenturyLink Cloud: The Leader in Enterprise Cloud Services.
Learn Why More Businesses Are Choosing CenturyLink Cloud For
Critical Workloads, Development Environments & Everything In Between.
Get a Quote or Start a Free Trial Today. 
http://pubads.g.doubleclick.net/gampad/clk?id=119420431&iu=/4140/ostg.clktrk
_______________________________________________
Ledger-smb-commits mailing list
..hidden..
https://lists.sourceforge.net/lists/listinfo/ledger-smb-commits