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

[..hidden..]

Revision: 2861
          http://ledger-smb.svn.sourceforge.net/ledger-smb/?rev=2861&view=rev
Author:   einhverfr
Date:     2010-02-07 23:38:16 +0000 (Sun, 07 Feb 2010)

Log Message:
-----------
Minor changes for better security

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

Modified: trunk/sql/modules/Roles.sql
===================================================================
--- trunk/sql/modules/Roles.sql	2010-02-07 23:28:52 UTC (rev 2860)
+++ trunk/sql/modules/Roles.sql	2010-02-07 23:38:16 UTC (rev 2861)
@@ -1447,7 +1447,6 @@
 GRANT SELECT ON assembly TO public;
 GRANT SELECT ON jcitems TO public;
 GRANT SELECT ON payment_type TO public;
-GRANT ALL ON open_forms, open_forms_id_seq to public;
 
 GRANT EXECUTE ON FUNCTION user__get_all_users() TO public;
 

Modified: trunk/sql/modules/Session.sql
===================================================================
--- trunk/sql/modules/Session.sql	2010-02-07 23:28:52 UTC (rev 2860)
+++ trunk/sql/modules/Session.sql	2010-02-07 23:38:16 UTC (rev 2861)
@@ -1,9 +1,12 @@
 CREATE OR REPLACE FUNCTION form_check(in_session_id int, in_form_id int)
 RETURNS BOOL AS
 $$
-SELECT count(*) = 1 FROM open_forms
- WHERE session_id = $1 and id = $2;
-$$ language sql;
+SELECT count(*) = 1 
+  FROM open_forms f
+  JOIN "session" s USING (session_id)
+  JOIN users u ON (s.users_id = u.id)
+ WHERE f.session_id = $1 and f.id = $2 and u.username = SESSION_USER;
+$$ language sql SECURITY DEFINER;
 
 CREATE OR REPLACE FUNCTION form_close(in_session_id int, in_form_id int)
 RETURNS BOOL AS
@@ -21,7 +24,7 @@
 	ELSE RETURN FALSE;
 	END IF;
 END;
-$$ language plpgsql;
+$$ language plpgsql SECURITY DEFINER;
 
 CREATE OR REPLACE FUNCTION check_expiration() RETURNS bool AS
 $$
@@ -52,11 +55,21 @@
 CREATE OR REPLACE FUNCTION form_open(in_session_id int)
 RETURNS INT AS
 $$
+DECLARE usertest bool;
 BEGIN
+        SELECT count(*) = 1 INTO usertest FROM session 
+         WHERE session_id = in_session_id 
+               AND users_id IN (select id from users 
+                                WHERE username = SESSION_USER);
+
+        IF usertest is not true THEN
+            RAISE EXCEPTION 'Invalid session';
+        END IF;
+      
 	INSERT INTO open_forms (session_id) VALUES (in_session_id);
 	RETURN currval('open_forms_id_seq');
 END;
-$$ LANGUAGE PLPGSQL;
+$$ LANGUAGE PLPGSQL SECURITY DEFINER;
 
 CREATE OR REPLACE FUNCTION session_check(in_session_id int, in_token text) 
 RETURNS session AS


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