[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
SF.net SVN: ledger-smb:[2963] trunk/sql
- Subject: SF.net SVN: ledger-smb:[2963] trunk/sql
- From: ..hidden..
- Date: Mon, 15 Mar 2010 00:15:46 +0000
Revision: 2963
http://ledger-smb.svn.sourceforge.net/ledger-smb/?rev=2963&view=rev
Author: einhverfr
Date: 2010-03-15 00:15:45 +0000 (Mon, 15 Mar 2010)
Log Message:
-----------
* Permissions fixes
* Changes to audit trail logic
* Cleaned up some old tables
* test case corrections
Modified Paths:
--------------
trunk/sql/Pg-database.sql
trunk/sql/modules/Reconciliation.sql
trunk/sql/modules/Roles.sql
trunk/sql/modules/test/Base.sql
trunk/sql/modules/test/Payment.sql
trunk/sql/modules/test/System.sql
Modified: trunk/sql/Pg-database.sql
===================================================================
--- trunk/sql/Pg-database.sql 2010-03-14 06:58:10 UTC (rev 2962)
+++ trunk/sql/Pg-database.sql 2010-03-15 00:15:45 UTC (rev 2963)
@@ -1190,29 +1190,6 @@
PRIMARY KEY (curr, transdate)
);
--
-
---
-create table shipto (
- trans_id int,
- shiptoname varchar(64),
- shiptoaddress1 varchar(32),
- shiptoaddress2 varchar(32),
- shiptocity varchar(32),
- shiptostate varchar(32),
- shiptozipcode varchar(10),
- shiptocountry varchar(32),
- shiptocontact varchar(64),
- shiptophone varchar(20),
- shiptofax varchar(20),
- shiptoemail text,
- entry_id SERIAL PRIMARY KEY
-);
-
--- SHIPTO really needs to be pushed into entities too
-
---
-
---
CREATE TABLE project (
id serial PRIMARY KEY,
projectnumber text,
@@ -1459,6 +1436,41 @@
reportable bool
);
+CREATE OR REPLACE FUNCTION gl_audit_trail_append()
+RETURNS TRIGGER AS
+$$
+DECLARE
+ t_reference text;
+ t_row RECORD;
+BEGIN
+
+IF TG_OP = 'INSERT' then
+ t_row := NEW;
+ELSE
+ t_row := OLD;
+END IF;
+
+IF TG_RELNAME IN ('ar', 'ap') THEN
+ t_reference := t_row.invnumber;
+ELSE
+ t_reference := t_row.reference;
+END IF;
+
+INSERT INTO audittrail (trans_id, reference, action, person_id)
+values (t_row.id, t_reference, TG_OP, person__get_my_entity_id());
+
+return null; -- AFTER TRIGGER ONLY, SAFE
+END;
+$$ language plpgsql security definer;
+
+CREATE TRIGGER gl_audit_trail AFTER insert or update or delete ON gl
+FOR EACH ROW EXECUTE PROCEDURE gl_audit_trail_append();
+
+CREATE TRIGGER ar_audit_trail AFTER insert or update or delete ON ar
+FOR EACH ROW EXECUTE PROCEDURE gl_audit_trail_append();
+
+CREATE TRIGGER ap_audit_trail AFTER insert or update or delete ON ar
+FOR EACH ROW EXECUTE PROCEDURE gl_audit_trail_append();
create index acc_trans_trans_id_key on acc_trans (trans_id);
create index acc_trans_chart_id_key on acc_trans (chart_id);
create index acc_trans_transdate_key on acc_trans (transdate);
@@ -1508,8 +1520,6 @@
create index partstax_parts_id_key on partstax (parts_id);
--
--
-create index shipto_trans_id_key on shipto (trans_id);
---
create index project_id_key on project (id);
create unique index projectnumber_key on project (projectnumber);
--
@@ -2786,31 +2796,6 @@
INITCOND = '{}'
);
-CREATE TABLE pending_reports (
- id bigserial primary key not null,
- report_id int,
- scn int,
- their_balance INT,
- our_balance INT,
- errorcode INT,
- entered_by int references entity(id) not null,
- corrections INT NOT NULL DEFAULT 0,
- clear_time TIMESTAMP NOT NULL,
- insert_time TIMESTAMPTZ NOT NULL DEFAULT now(),
- ledger_id int REFERENCES acc_trans(entry_id),
- overlook boolean not null default 'f'
-);
-
-
-CREATE TABLE report_corrections (
- id serial primary key not null,
- correction_id int not null default 1,
- entry_in int references pending_reports(id) not null,
- entered_by int not null,
- reason text not null,
- insert_time timestamptz not null default now()
-);
-
CREATE INDEX company_name_gist__idx ON company USING gist(legal_name gist_trgm_ops);
CREATE INDEX location_address_one_gist__idx ON location USING gist(line_one gist_trgm_ops);
CREATE INDEX location_address_two_gist__idx ON location USING gist(line_two gist_trgm_ops);
Modified: trunk/sql/modules/Reconciliation.sql
===================================================================
--- trunk/sql/modules/Reconciliation.sql 2010-03-14 06:58:10 UTC (rev 2962)
+++ trunk/sql/modules/Reconciliation.sql 2010-03-15 00:15:45 UTC (rev 2963)
@@ -15,12 +15,6 @@
CHECK (deleted is not true or approved is not true)
);
-create table cr_approval (
- report_id bigint references cr_report(id) primary key,
- approved_by int references entity(id) not null,
- approved_at timestamptz default now() not null
-);
-
CREATE TABLE cr_report_line (
id bigserial primary key not null,
report_id int NOT NULL references cr_report(id),
Modified: trunk/sql/modules/Roles.sql
===================================================================
--- trunk/sql/modules/Roles.sql 2010-03-14 06:58:10 UTC (rev 2962)
+++ trunk/sql/modules/Roles.sql 2010-03-15 00:15:45 UTC (rev 2963)
@@ -3,6 +3,8 @@
CREATE ROLE "lsmb_<?lsmb dbname ?>__contact_read"
WITH INHERIT NOLOGIN;
+GRANT SELECT ON partsvendor, partscustomer, taxcategory
+TO "lsmb_<?lsmb dbname ?>__contact_read";
GRANT SELECT ON entity TO "lsmb_<?lsmb dbname ?>__contact_read";
GRANT SELECT ON company TO "lsmb_<?lsmb dbname ?>__contact_read";
GRANT SELECT ON location TO "lsmb_<?lsmb dbname ?>__contact_read";
@@ -155,7 +157,9 @@
WITH INHERIT NOLOGIN
IN ROLE "lsmb_<?lsmb dbname ?>__contact_read";
-GRANT INSERT ON ar TO "lsmb_<?lsmb dbname ?>__ar_transaction_create";
+GRANT INSERT ON ar, invoice_note
+TO "lsmb_<?lsmb dbname ?>__ar_transaction_create";
+
GRANT ALL ON id TO "lsmb_<?lsmb dbname ?>__ar_transaction_create";
GRANT INSERT ON acc_trans TO "lsmb_<?lsmb dbname ?>__ar_transaction_create";
GRANT ALL ON acc_trans_entry_id_seq TO "lsmb_<?lsmb dbname ?>__ar_transaction_create";
@@ -184,11 +188,13 @@
IN ROLE "lsmb_<?lsmb dbname ?>__contact_read",
"lsmb_<?lsmb dbname ?>__ar_transaction_create";
-GRANT INSERT ON invoice TO "lsmb_<?lsmb dbname ?>__ar_invoice_create";
+GRANT INSERT ON invoice, new_shipto
+TO "lsmb_<?lsmb dbname ?>__ar_invoice_create";
GRANT ALL ON invoice_id_seq TO "lsmb_<?lsmb dbname ?>__ar_invoice_create";
GRANT INSERT ON inventory TO "lsmb_<?lsmb dbname ?>__ar_invoice_create";
GRANT ALL ON inventory_entry_id_seq TO "lsmb_<?lsmb dbname ?>__ar_invoice_create";
+
INSERT INTO menu_acl (node_id, acl_type, role_name)
values (3, 'allow', 'lsmb_<?lsmb dbname ?>__ar_invoice_create');
INSERT INTO menu_acl (node_id, acl_type, role_name)
@@ -323,7 +329,8 @@
WITH INHERIT NOLOGIN
IN ROLE "lsmb_<?lsmb dbname ?>__contact_read";
-GRANT INSERT ON ap TO "lsmb_<?lsmb dbname ?>__ap_transaction_create";
+GRANT INSERT ON ap, invoice_note
+TO "lsmb_<?lsmb dbname ?>__ap_transaction_create";
GRANT ALL ON id TO "lsmb_<?lsmb dbname ?>__ap_transaction_create";
GRANT INSERT ON acc_trans TO "lsmb_<?lsmb dbname ?>__ap_transaction_create";
GRANT ALL ON acc_trans_entry_id_seq TO "lsmb_<?lsmb dbname ?>__ap_transaction_create";
@@ -553,7 +560,9 @@
TO "lsmb_<?lsmb dbname ?>__reconciliation_enter";
GRANT DELETE ON cr_report_line
TO "lsmb_<?lsmb dbname ?>__reconciliation_enter";
-GRANT SELECT ON acc_trans TO "lsmb_<?lsmb dbname ?>__reconciliation_enter";
+GRANT SELECT ON acc_trans, account_checkpoint
+TO "lsmb_<?lsmb dbname ?>__reconciliation_enter";
+
GRANT ALL ON cr_report_id_seq TO "lsmb_<?lsmb dbname ?>__reconciliation_enter";
INSERT INTO menu_acl (node_id, acl_type, role_name)
@@ -565,15 +574,16 @@
CREATE ROLE "lsmb_<?lsmb dbname ?>__reconciliation_approve"
WITH INHERIT NOLOGIN;
-GRANT UPDATE ON cr_report TO "lsmb_<?lsmb dbname ?>__reconciliation_enter";
-GRANT SELECT ON acc_trans TO "lsmb_<?lsmb dbname ?>__reconciliation_enter";
+GRANT UPDATE ON cr_report TO "lsmb_<?lsmb dbname ?>__reconciliation_approve";
+GRANT SELECT ON acc_trans, account_checkpoint TO
+"lsmb_<?lsmb dbname ?>__reconciliation_approve";
INSERT INTO menu_acl (node_id, acl_type, role_name)
-values (35, 'allow', 'lsmb_<?lsmb dbname ?>_reconciliation_enter');
+values (35, 'allow', 'lsmb_<?lsmb dbname ?>_reconciliation_approve');
INSERT INTO menu_acl (node_id, acl_type, role_name)
-values (41, 'allow', 'lsmb_<?lsmb dbname ?>_reconciliation_enter');
+values (41, 'allow', 'lsmb_<?lsmb dbname ?>_reconciliation_approve');
INSERT INTO menu_acl (node_id, acl_type, role_name)
-values (44, 'allow', 'lsmb_<?lsmb dbname ?>_reconciliation_enter');
+values (44, 'allow', 'lsmb_<?lsmb dbname ?>_reconciliation_approve');
CREATE ROLE "lsmb_<?lsmb dbname ?>__all_reconciliation_enter"
@@ -585,6 +595,9 @@
WITH INHERIT NOLOGIN
IN ROLE "lsmb_<?lsmb dbname ?>__ap_transaction_list";
+GRANT INSERT, SELECT ON payment, payment_links, overpayments
+TO "lsmb_<?lsmb dbname ?>__payment_process";
+
GRANT SELECT, INSERT ON acc_trans TO "lsmb_<?lsmb dbname ?>__payment_process";
GRANT ALL ON acc_trans_entry_id_seq TO "lsmb_<?lsmb dbname ?>__payment_process";
GRANT UPDATE ON ap TO "lsmb_<?lsmb dbname ?>__payment_process";
@@ -600,6 +613,9 @@
WITH INHERIT NOLOGIN
IN ROLE "lsmb_<?lsmb dbname ?>__ar_transaction_list";
+GRANT INSERT, SELECT ON payment, payment_links, overpayment
+TO "lsmb_<?lsmb dbname ?>__receipt_proces";
+
GRANT INSERT ON acc_trans TO "lsmb_<?lsmb dbname ?>__receipt_process";
GRANT ALL ON acc_trans_entry_id_seq TO "lsmb_<?lsmb dbname ?>__receipt_process";
GRANT UPDATE ON ar TO "lsmb_<?lsmb dbname ?>__receipt_process";
@@ -620,9 +636,11 @@
-- Inventory Control
CREATE ROLE "lsmb_<?lsmb dbname ?>__part_create"
-WITH INHERIT NOLOGIN;
+WITH INHERIT NOLOGIN
+IN ROLE "lsmb_<?lsmb dbname ?>__contact_read";
-GRANT INSERT ON parts TO "lsmb_<?lsmb dbname ?>__part_create";
+GRANT ALL ON partsvendor, partscustomer TO "lsmb_<?lsmb dbname ?>__part_create";
+GRANT INSERT ON parts, makemodel TO "lsmb_<?lsmb dbname ?>__part_create";
GRANT ALL ON parts_id_seq TO "lsmb_<?lsmb dbname ?>__part_create";
GRANT INSERT ON partstax TO "lsmb_<?lsmb dbname ?>__part_create";
@@ -644,6 +662,7 @@
WITH INHERIT NOLOGIN;
GRANT UPDATE ON parts TO "lsmb_<?lsmb dbname ?>__part_edit";
+GRANT ALL ON makemodel TO "lsmb_<?lsmb dbname ?>__part_edit";
INSERT INTO menu_acl (node_id, acl_type, role_name)
values (77, 'allow', 'lsmb_<?lsmb dbname ?>__part_edit');
@@ -834,7 +853,8 @@
IN ROLE "lsmb_<?lsmb dbname ?>__ar_transaction_list",
"lsmb_<?lsmb dbname ?>__ap_transaction_list";
-GRANT SELECT ON gl TO "lsmb_<?lsmb dbname ?>__gl_reports";
+GRANT SELECT ON gl, acc_trans, account_checkpoint
+TO "lsmb_<?lsmb dbname ?>__gl_reports";
INSERT INTO menu_acl (node_id, acl_type, role_name)
values (73, 'allow', 'lsmb_<?lsmb dbname ?>__gl_reports');
@@ -845,7 +865,8 @@
CREATE ROLE "lsmb_<?lsmb dbname ?>__yearend_run"
WITH INHERIT NOLOGIN;
-GRANT INSERT, SELECT ON acc_trans TO "lsmb_<?lsmb dbname ?>__yearend_run";
+GRANT INSERT, SELECT ON acc_trans, account_checkpoint, yearend
+TO "lsmb_<?lsmb dbname ?>__yearend_run";
INSERT INTO menu_acl (node_id, acl_type, role_name)
values (128, 'allow', 'lsmb_<?lsmb dbname ?>__yearend_run');
@@ -1000,6 +1021,8 @@
WITH INHERIT NOLOGIN
IN ROLE "lsmb_<?lsmb dbname ?>__gl_reports";
+GRANT select ON yearend TO "lsmb_<?lsmb dbname ?>__financial_reports";
+
INSERT INTO menu_acl (node_id, acl_type, role_name)
values (109, 'allow', 'lsmb_<?lsmb dbname ?>__financial_reports');
INSERT INTO menu_acl (node_id, acl_type, role_name)
@@ -1081,7 +1104,9 @@
WITH INHERIT NOLOGIN;
GRANT INSERT ON chart TO "lsmb_<?lsmb dbname ?>__account_create";
-GRANT INSERT ON account TO "lsmb_<?lsmb dbname ?>__account_create";
+GRANT INSERT ON account, cr_coa_to_account
+TO "lsmb_<?lsmb dbname ?>__account_create";
+
GRANT ALL ON account_id_seq TO "lsmb_<?lsmb dbname ?>__account_create";
GRANT INSERT ON account_heading TO "lsmb_<?lsmb dbname ?>__account_create";
GRANT ALL ON account_heading_id_seq TO "lsmb_<?lsmb dbname ?>__account_create";
@@ -1100,7 +1125,8 @@
CREATE ROLE "lsmb_<?lsmb dbname ?>__account_edit"
WITH INHERIT NOLOGIN;
-GRANT UPDATE ON chart TO "lsmb_<?lsmb dbname ?>__account_edit";
+GRANT ALL ON account, account_heading, account_link, cr_coa_to_account
+TO "lsmb_<?lsmb dbname ?>__account_edit";
INSERT INTO menu_acl (node_id, acl_type, role_name)
values (128, 'allow', 'lsmb_<?lsmb dbname ?>__account_edit');
@@ -1109,7 +1135,16 @@
INSERT INTO menu_acl (node_id, acl_type, role_name)
values (138, 'allow', 'lsmb_<?lsmb dbname ?>__account_edit');
+CREATE ROLE "lsmb_<?lsmb dbname ?>__auditor"
+WITH INHERIT NOLOGIN;
+GRANT SELECT ON audittrail TO "lsmb_<?lsmb dbname ?>__auditor";
+
+CREATE ROLE "lsmb_<?lsmb dbname ?>__audit_trail_maintenance"
+WITH INHERIT NOLOGIN;
+
+GRANT DELETE ON audittrail TO "lsmb_<?lsmb dbname ?>__audit_trail_maintenance";
+
CREATE ROLE "lsmb_<?lsmb dbname ?>__gifi_create"
WITH INHERIT NOLOGIN;
@@ -1392,9 +1427,10 @@
"lsmb_<?lsmb dbname ?>__project_translation_create";
CREATE ROLE "lsmb_<?lsmb dbname ?>__users_manage"
-WITH INHERIT NOLOGIN;
-IN ROLE "lsmb_<?lsmb dbname ?>__contact_read"
+WITH INHERIT NOLOGIN
+IN ROLE "lsmb_<?lsmb dbname ?>__contact_read";
+GRANT SELECT ON role_view TO "lsmb_<?lsmb dbname ?>__users_manage";
GRANT EXECUTE ON FUNCTION admin__add_user_to_role(TEXT, TEXT)
TO "lsmb_<?lsmb dbname ?>__users_manage";
GRANT EXECUTE ON FUNCTION admin__remove_user_from_role(TEXT, TEXT)
@@ -1417,8 +1453,11 @@
TO "lsmb_<?lsmb dbname ?>__users_manage";
-- Grants to all users;
+GRANT SELECT ON makemodel TO public;
GRANT SELECT ON custom_field_catalog TO public;
GRANT SELECT ON custom_table_catalog TO public;
+GRANT SELECT ON oe_class TO public;
+GRANT SELECT ON note_class TO public;
GRANT ALL ON defaults TO public;
GRANT ALL ON "session" TO public;
GRANT ALL ON session_session_id_seq TO PUBLIC;
@@ -1432,7 +1471,7 @@
grant select on employee to public;
GRANT SELECT ON parts, partsgroup TO public;
GRANT SELECT ON language, project TO public;
-GRANT SELECT ON business, exchangerate, department, shipto, tax TO public;
+GRANT SELECT ON business, exchangerate, department, new_shipto, tax TO public;
GRANT ALL ON recurring, recurringemail, recurringprint, status TO public;
GRANT ALL ON transactions, entity_employee, customer, vendor TO public;
GRANT ALL ON pending_job, payments_queue TO PUBLIC;
@@ -1449,6 +1488,8 @@
GRANT SELECT ON assembly TO public;
GRANT SELECT ON jcitems TO public;
GRANT SELECT ON payment_type TO public;
+GRANT SELECT ON lsmb_roles TO public;
+GRANT SELECT ON employee_search TO PUBLIC;
GRANT EXECUTE ON FUNCTION user__get_all_users() TO public;
Modified: trunk/sql/modules/test/Base.sql
===================================================================
--- trunk/sql/modules/test/Base.sql 2010-03-14 06:58:10 UTC (rev 2962)
+++ trunk/sql/modules/test/Base.sql 2010-03-15 00:15:45 UTC (rev 2963)
@@ -6,6 +6,9 @@
INSERT INTO entity (id, name, entity_class, control_code, country_id)
VALUES (-100, 'Testing.....', 3, '_TESTING.....', 242);
+INSERT INTO person(id, entity_id, first_name, last_name)
+values (-100, -100, 'Test', 'User');
+
DELETE FROM users WHERE username = CURRENT_USER;
INSERT INTO users (entity_id, username)
Modified: trunk/sql/modules/test/Payment.sql
===================================================================
--- trunk/sql/modules/test/Payment.sql 2010-03-14 06:58:10 UTC (rev 2962)
+++ trunk/sql/modules/test/Payment.sql 2010-03-15 00:15:45 UTC (rev 2963)
@@ -8,9 +8,6 @@
insert into session (session_id, users_id, token, last_used, transaction_id)
values (-200, -200, md5(random()::text), now(), 0);
-INSERT INTO person(first_name, last_name, entity_id, id)
-VALUES ('test', 'test', -100, -100);
-
INSERT INTO chart (accno, description, charttype, category, link)
VALUES ('00001', 'testing', 'A', 'L', 'AP');
INSERT INTO chart (accno, description, charttype, category, link)
Modified: trunk/sql/modules/test/System.sql
===================================================================
--- trunk/sql/modules/test/System.sql 2010-03-14 06:58:10 UTC (rev 2962)
+++ trunk/sql/modules/test/System.sql 2010-03-15 00:15:45 UTC (rev 2963)
@@ -23,6 +23,22 @@
insert into test_exempt_funcs values ('concat_colon');
insert into test_exempt_funcs values ('to_args');
+create table test_exempt_tables (tablename text, reason text);
+insert into test_exempt_tables values ('note', 'abstract table, no data');
+insert into test_exempt_tables values ('open_forms', 'security definer only');
+
+insert into test_exempt_tables
+values ('person_to_company', 'Unused in core, for addons only');
+insert into test_exempt_tables
+values ('person_to_entity', 'Unused in core, for addons only');
+
+insert into test_exempt_tables values ('test_exempt_funcs', 'test data only');
+
+insert into test_exempt_tables values ('test_exempt_tables', 'test data only');
+insert into test_exempt_tables values ('menu_friendly', 'dev info only');
+insert into test_exempt_tables values ('note', 'abstract table, no data');
+analyze test_exempt_tables;
+
INSERT INTO test_result(test_name, success)
select 'No overloaded functions in current schema', count(*) = 0
FROM (select proname FROM pg_proc
@@ -41,7 +57,7 @@
group by proname
having count(*) > 1;
-CREATE TEMPORARY VIEW permissionless_tables AS
+CREATE TEMPORARY table permissionless_tables AS
select t.table_catalog, t.table_schema, t.table_type, t.table_name
from information_schema.tables t
where t.table_catalog = current_database()
@@ -53,6 +69,10 @@
and r.table_schema = t.table_schema
and r.table_name = t.table_name
)
+ and not exists (
+ select *
+ from test_exempt_tables x
+ where x.tablename = t.table_name)
order by t.table_catalog, t.table_schema, t.table_type, t.table_name;
select * from permissionless_tables;
This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.