From 74b51d657188e30cea1c41973ea1431d861d7b8d Mon Sep 17 00:00:00 2001 From: Kevin Day Date: Tue, 2 May 2017 14:25:55 -0500 Subject: [PATCH] Cleanup: do not explicitly specify public when referencing v_users_self_locked_not --- database/sql/reservation/reservation-associations.sql | 10 +++++----- database/sql/reservation/reservation-groups.sql | 12 ++++++------ database/sql/reservation/reservation-legal.sql | 4 ++-- database/sql/reservation/reservation-log_groups.sql | 8 ++++---- database/sql/reservation/reservation-log_problems.sql | 6 +++--- database/sql/reservation/reservation-log_users.sql | 10 +++++----- database/sql/standard/standard-groups.sql | 12 ++++++------ database/sql/standard/standard-log_groups.sql | 8 ++++---- database/sql/standard/standard-log_problems.sql | 6 +++--- database/sql/standard/standard-log_users.sql | 10 +++++----- 10 files changed, 43 insertions(+), 43 deletions(-) diff --git a/database/sql/reservation/reservation-associations.sql b/database/sql/reservation/reservation-associations.sql index 4c6c461..1d4e0d4 100644 --- a/database/sql/reservation/reservation-associations.sql +++ b/database/sql/reservation/reservation-associations.sql @@ -100,7 +100,7 @@ create index i_associations_id_sort_z on s_tables.t_associations (id_sort) with /*** provide current user access to their own information ***/ create view s_users.v_associations_self with (security_barrier=true) as - with this_user as (select id from public.v_users_self_locked_not), + with this_user as (select id from v_users_self_locked_not), allowed_groups as (select id from s_users.v_groups_self) select id, id_manager, id_coordinator, id_group, id_sort, name_machine, name_human, is_approved, is_cancelled, is_denied, is_troubled, is_locked, date_created, date_changed, date_synced, date_approved, date_cancelled, date_denied, date_troubled, date_locked, field_affiliation, field_classification from s_tables.t_associations where not is_deleted and (id_manager in (select * from this_user) or id_group in (select * from allowed_groups)); @@ -110,7 +110,7 @@ grant select on s_users.v_associations_self to r_reservation_requester, r_reserv /*** provide current user access to associations who they are assigned as the manager of ***/ create view s_users.v_associations_manage with (security_barrier=true) as - with this_user as (select id from public.v_users_self_locked_not) + with this_user as (select id from v_users_self_locked_not) select id, id_creator, id_coordinator, id_group, id_sort, name_machine, name_human, is_approved, is_cancelled, is_denied, is_troubled, is_locked, date_created, date_changed, date_synced, date_approved, date_cancelled, date_denied, date_troubled, date_locked, field_affiliation, field_classification from s_tables.t_associations where not is_deleted and id_manager in (select * from this_user); @@ -119,7 +119,7 @@ grant select on s_users.v_associations_manage to r_reservation_requester, r_rese /*** provide current user access to associations who they are assigned as the coordinator of ***/ create view s_users.v_associations_coordinate with (security_barrier=true) as - with this_user as (select id from public.v_users_self_locked_not) + with this_user as (select id from v_users_self_locked_not) select id, id_creator, id_manager, id_group, id_sort, name_machine, name_human, is_approved, is_cancelled, is_denied, is_troubled, is_locked, date_created, date_changed, date_synced, date_approved, date_cancelled, date_denied, date_troubled, date_locked, field_affiliation, field_classification from s_tables.t_associations where not is_deleted and id_coordinator in (select * from this_user); @@ -129,7 +129,7 @@ grant select on s_users.v_associations_coordinate to r_reservation_requester, r_ /** provide current user access to insert their own associations (with them as the manager) **/ create view s_users.v_associations_self_insert with (security_barrier=true) as select id_manager, id_group, id_coordinator, name_machine, name_human, field_affiliation, field_classification from s_tables.t_associations - where not is_deleted and id_manager in (select id from public.v_users_self_locked_not) + where not is_deleted and id_manager in (select id from v_users_self_locked_not) with check option; grant insert on s_users.v_associations_self_insert to r_reservation_requester, r_reservation_reviewer; @@ -138,7 +138,7 @@ grant insert on s_users.v_associations_self_insert to r_reservation_requester, r /** provide current user access to update associations they manager **/ create view s_users.v_associations_self_update with (security_barrier=true) as select id_manager, id_group, id_coordinator, name_machine, name_human, date_changed, field_affiliation, field_classification from s_tables.t_associations - where not is_deleted and id_manager in (select id from public.v_users_self_locked_not) + where not is_deleted and id_manager in (select id from v_users_self_locked_not) with check option; grant update on s_users.v_associations_self_update to r_reservation_requester, r_reservation_reviewer; diff --git a/database/sql/reservation/reservation-groups.sql b/database/sql/reservation/reservation-groups.sql index 42a0a87..eeaf445 100644 --- a/database/sql/reservation/reservation-groups.sql +++ b/database/sql/reservation/reservation-groups.sql @@ -100,7 +100,7 @@ create unique index i_groups_one_user_group on s_tables.t_groups (id_manager) wh /*** provide group managers access to manage their groups ***/ create view s_users.v_groups_manage_self with (security_barrier=true) as - with this_user as (select id from public.v_users_self_locked_not) + with this_user as (select id from v_users_self_locked_not) select id, id_external, name_machine, name_human, is_locked, is_composite, is_user, can_manage_paths, settings from s_tables.t_groups where not is_deleted and id_manager in (select * from this_user); @@ -108,7 +108,7 @@ grant select on s_users.v_groups_manage_self to r_reservation, r_reservation_sys create view s_users.v_groups_manage_update with (security_barrier=true) as select id, id_external, name_machine, name_human, is_locked, is_composite, is_user, can_manage_paths, settings from s_tables.t_groups - where not is_deleted and id_manager in (select id from public.v_users_self_locked_not) + where not is_deleted and id_manager in (select id from v_users_self_locked_not) with check option; grant update on s_users.v_groups_manage_update to r_reservation, r_reservation_system; @@ -181,7 +181,7 @@ grant select on s_tables.t_groups to r_reservation_auditor; /*** provide current user access to their own information ***/ create view s_users.v_groups_self with (security_barrier=true) as - with allowed_groups as (select id_group from s_tables.t_group_users where not is_deleted and not is_locked and id_user in (select id from public.v_users_self_locked_not)) + with allowed_groups as (select id_group from s_tables.t_group_users where not is_deleted and not is_locked and id_user in (select id from v_users_self_locked_not)) select id, id_external, id_manager, name_machine, name_human, is_locked, is_composite, date_created, date_changed, date_synced, can_manage_paths, settings from s_tables.t_groups where not is_deleted and id in (select * from allowed_groups); @@ -189,7 +189,7 @@ grant select on s_users.v_groups_self to r_reservation, r_reservation_system; /*** provide group managers access to manage users assigned to their groups (any user id less than 1000 is reserved/special case, prohibit those). ***/ create view s_users.v_group_users_manage with (security_barrier=true) as - with managed_groups as (select id from s_tables.t_groups where not is_deleted and id_manager in (select id from public.v_users_self_locked_not)), + with managed_groups as (select id from s_tables.t_groups where not is_deleted and id_manager in (select id from v_users_self_locked_not)), available_users as (select id from s_tables.t_users where not is_deleted and not is_locked and not is_system and not is_public) select id_user, id_group, is_locked from s_tables.t_group_users where not is_deleted and id_group in (select * from managed_groups) and id_user in (select * from available_users); @@ -198,14 +198,14 @@ grant select on s_users.v_group_users_manage to r_reservation, r_reservation_sys create view s_users.v_group_users_manage_insert with (security_barrier=true) as select id_user, id_group from s_tables.t_group_users - where not is_deleted and id_group in (select id from s_users.v_groups_manage_self) and id_group in (select id_group from s_tables.t_group_users where not is_deleted and not is_locked and id_user in (select id from public.v_users_self_locked_not)) and id_user in (select id from s_tables.t_users where not is_deleted and not is_locked and not is_system and not is_public) + where not is_deleted and id_group in (select id from s_users.v_groups_manage_self) and id_group in (select id_group from s_tables.t_group_users where not is_deleted and not is_locked and id_user in (select id from v_users_self_locked_not)) and id_user in (select id from s_tables.t_users where not is_deleted and not is_locked and not is_system and not is_public) with check option; grant insert on s_users.v_group_users_manage_insert to r_reservation, r_reservation_system; create view s_users.v_group_users_manage_update with (security_barrier=true) as select id_user, id_group from s_tables.t_group_users - where not is_deleted and id_group in (select id from s_users.v_groups_manage_self) and id_group in (select id_group from s_tables.t_group_users where not is_deleted and not is_locked and id_user in (select id from public.v_users_self_locked_not)) and id_user in (select id from s_tables.t_users where not is_deleted and not is_locked and not is_system and not is_public) + where not is_deleted and id_group in (select id from s_users.v_groups_manage_self) and id_group in (select id_group from s_tables.t_group_users where not is_deleted and not is_locked and id_user in (select id from v_users_self_locked_not)) and id_user in (select id from s_tables.t_users where not is_deleted and not is_locked and not is_system and not is_public) with check option; grant update on s_users.v_group_users_manage_update to r_reservation, r_reservation_system; diff --git a/database/sql/reservation/reservation-legal.sql b/database/sql/reservation/reservation-legal.sql index 08ca990..be24a38 100644 --- a/database/sql/reservation/reservation-legal.sql +++ b/database/sql/reservation/reservation-legal.sql @@ -103,7 +103,7 @@ create index i_signatures_deleted_not on s_tables.t_signatures (id) /*** provide current user access to their own information ***/ create view s_users.v_signatures_self with (security_barrier=true) as - with this_user as (select id from public.v_users_self_locked_not) + with this_user as (select id from v_users_self_locked_not) select id, id_type, id_request, date_created, field_fingerprint, field_signature from s_tables.t_signatures where not is_deleted and id_creator in (select * from this_user); @@ -113,7 +113,7 @@ grant select on s_users.v_signatures_self to r_reservation, r_reservation_system /** provide current user access to insert their own associations **/ create view s_users.v_signatures_self_insert with (security_barrier=true) as select id, id_type, id_creator, id_request, field_fingerprint, field_signature from s_tables.t_signatures - where not is_deleted and id_creator in (select id from public.v_users_self_locked_not) + where not is_deleted and id_creator in (select id from v_users_self_locked_not) with check option; grant insert on s_users.v_signatures_self_insert to r_reservation, r_reservation_system; diff --git a/database/sql/reservation/reservation-log_groups.sql b/database/sql/reservation/reservation-log_groups.sql index d7e4fb1..0c37a90 100644 --- a/database/sql/reservation/reservation-log_groups.sql +++ b/database/sql/reservation/reservation-log_groups.sql @@ -48,7 +48,7 @@ grant usage on s_tables.se_log_groups_id to r_reservation, r_reservation_system; /** only allow select and insert for users when user id is current user **/ create view s_users.v_log_groups_self with (security_barrier=true) as - with this_user as (select id from public.v_users_self_locked_not) + with this_user as (select id from v_users_self_locked_not) select id, id_user, id_group, log_type, log_type_sub, log_severity, log_facility, log_details, log_date from s_tables.t_log_groups where id_user in (select * from this_user); @@ -56,7 +56,7 @@ grant select on s_users.v_log_groups_self to r_reservation, r_reservation_system create view s_users.v_log_groups_self_insert with (security_barrier=true) as select id_group, log_type, log_type_sub, log_severity, log_facility, log_details from s_tables.t_log_groups - where id_user in (select id from public.v_users_self_locked_not) and id_group in (select id from s_users.v_groups_self where not is_locked) + where id_user in (select id from v_users_self_locked_not) and id_group in (select id from s_users.v_groups_self where not is_locked) with check option; grant insert on s_users.v_log_groups_self_insert to r_reservation, r_reservation_system; @@ -107,7 +107,7 @@ grant usage on s_tables.se_log_group_users_id to r_reservation, r_reservation_sy /** only allow select and insert for users when user id is current user **/ create view s_users.v_log_group_users_self with (security_barrier=true) as - with this_user as (select id from public.v_users_self_locked_not), + with this_user as (select id from v_users_self_locked_not), allowed_groups as (select id from s_users.v_groups_self where not is_locked) select id, id_user, id_group, log_type, log_type_sub, log_severity, log_facility, log_date from s_tables.t_log_group_users where id_user in (select * from this_user) or id_group in (select * from allowed_groups); @@ -116,7 +116,7 @@ grant select on s_users.v_log_group_users_self to r_reservation, r_reservation_s create view s_users.v_log_group_users_self_insert with (security_barrier=true) as select id_group, log_type, log_type_sub, log_severity, log_facility from s_tables.t_log_group_users - where id_user in (select id from public.v_users_self_locked_not) and id_group in (select id from s_users.v_groups_self where not is_locked) + where id_user in (select id from v_users_self_locked_not) and id_group in (select id from s_users.v_groups_self where not is_locked) with check option; grant insert on s_users.v_log_group_users_self_insert to r_reservation, r_reservation_system; diff --git a/database/sql/reservation/reservation-log_problems.sql b/database/sql/reservation/reservation-log_problems.sql index 57458f8..5337653 100644 --- a/database/sql/reservation/reservation-log_problems.sql +++ b/database/sql/reservation/reservation-log_problems.sql @@ -64,7 +64,7 @@ grant select on s_tables.t_log_problems_users to r_reservation_auditor; /** only allow select, insert, and delete for users when user id is current user **/ create view s_users.v_log_problems_users_self with (security_barrier=true) as - with this_user as (select id from public.v_users_self_locked_not) + with this_user as (select id from v_users_self_locked_not) select id_problem, date_created, date_changed, log_details from s_tables.t_log_problems_users where id_user in (select * from this_user); @@ -73,7 +73,7 @@ grant select on s_users.v_log_problems_users_self to r_reservation, r_reservatio create view s_users.v_log_problems_users_self_insert with (security_barrier=true) as select id_problem, date_changed, log_details from s_tables.t_log_problems_users - where id_user in (select id from public.v_users_self_locked_not) + where id_user in (select id from v_users_self_locked_not) with check option; grant insert on s_users.v_log_problems_users_self_insert to r_reservation, r_reservation_system; @@ -81,7 +81,7 @@ grant insert on s_users.v_log_problems_users_self_insert to r_reservation, r_res create view s_users.v_log_problems_users_self_delete with (security_barrier=true) as select id_problem from s_tables.t_log_problems_users - where id_user in (select id from public.v_users_self_locked_not) + where id_user in (select id from v_users_self_locked_not) with check option; grant delete on s_users.v_log_problems_users_self_delete to r_reservation, r_reservation_system; diff --git a/database/sql/reservation/reservation-log_users.sql b/database/sql/reservation/reservation-log_users.sql index d11dad8..656dca1 100644 --- a/database/sql/reservation/reservation-log_users.sql +++ b/database/sql/reservation/reservation-log_users.sql @@ -87,7 +87,7 @@ create index i_log_users_response_code_notable on s_tables.t_log_users (id) /** only allow select and insert for users when user id is current user **/ create view s_users.v_log_users_self with (security_barrier=true) as - with this_user as (select id from public.v_users_self_locked_not) + with this_user as (select id from v_users_self_locked_not) select id, id_user, log_title, log_type, log_type_sub, log_severity, log_facility, log_details, log_date, request_client, response_code from s_tables.t_log_users where id_user in (select * from this_user); @@ -95,7 +95,7 @@ grant select on s_users.v_log_users_self to r_reservation, r_reservation_system; create view s_users.v_log_users_self_insert with (security_barrier=true) as select log_title, log_type, log_type_sub, log_severity, log_facility, log_details, request_client, response_code from s_tables.t_log_users - where id_user in (select id from public.v_users_self_locked_not) + where id_user in (select id from v_users_self_locked_not) with check option; grant insert on s_users.v_log_users_self_insert to r_reservation, r_reservation_system; @@ -178,7 +178,7 @@ create index i_log_user_activity_response_code_notable on s_tables.t_log_user_ac /** only allow select and insert for users when user id is current user **/ create view s_users.v_log_user_activity_self with (security_barrier=true) as - with this_user as (select id from public.v_users_self_locked_not) + with this_user as (select id from v_users_self_locked_not) select id, id_user, request_path, request_arguments, request_date, request_client, request_headers, response_headers, response_code from s_tables.t_log_user_activity where id_user in (select * from this_user); @@ -186,7 +186,7 @@ grant select on s_users.v_log_user_activity_self to r_reservation, r_reservation create view s_users.v_log_user_activity_self_insert with (security_barrier=true) as select request_path, request_arguments, request_client, request_headers, response_headers, response_code from s_tables.t_log_user_activity - where id_user in (select id from public.v_users_self_locked_not) + where id_user in (select id from v_users_self_locked_not) with check option; grant insert on s_users.v_log_user_activity_self_insert to r_reservation, r_reservation_system; @@ -195,7 +195,7 @@ grant insert on s_users.v_log_user_activity_self_insert to r_reservation, r_rese /** public users should be able to insert, but should never be able to view the logs that they insert. **/ create view public.v_log_user_activity_self_insert with (security_barrier=true) as select request_path, request_arguments, request_client, request_headers, response_headers, response_code from s_tables.t_log_user_activity - where id_user in (select id from public.v_users_self_locked_not) + where id_user in (select id from v_users_self_locked_not) with check option; grant insert on public.v_log_user_activity_self_insert to r_reservation_public; diff --git a/database/sql/standard/standard-groups.sql b/database/sql/standard/standard-groups.sql index 2efb960..38f2baf 100644 --- a/database/sql/standard/standard-groups.sql +++ b/database/sql/standard/standard-groups.sql @@ -100,7 +100,7 @@ create unique index i_groups_one_user_group on s_tables.t_groups (id_manager) wh /*** provide group managers access to manage their groups ***/ create view s_users.v_groups_manage_self with (security_barrier=true) as - with this_user as (select id from public.v_users_self_locked_not) + with this_user as (select id from v_users_self_locked_not) select id, id_external, name_machine, name_human, is_locked, is_composite, is_user, can_manage_paths, settings from s_tables.t_groups where not is_deleted and id_manager in (select * from this_user); @@ -108,7 +108,7 @@ grant select on s_users.v_groups_manage_self to r_standard, r_standard_system; create view s_users.v_groups_manage_update with (security_barrier=true) as select id, id_external, name_machine, name_human, is_locked, is_composite, is_user, can_manage_paths, settings from s_tables.t_groups - where not is_deleted and id_manager in (select id from public.v_users_self_locked_not) + where not is_deleted and id_manager in (select id from v_users_self_locked_not) with check option; grant update on s_users.v_groups_manage_update to r_standard, r_standard_system; @@ -181,7 +181,7 @@ grant select on s_tables.t_groups to r_standard_auditor; /*** provide current user access to their own information ***/ create view s_users.v_groups_self with (security_barrier=true) as - with allowed_groups as (select id_group from s_tables.t_group_users where not is_deleted and not is_locked and id_user in (select id from public.v_users_self_locked_not)) + with allowed_groups as (select id_group from s_tables.t_group_users where not is_deleted and not is_locked and id_user in (select id from v_users_self_locked_not)) select id, id_external, id_manager, name_machine, name_human, is_locked, is_composite, date_created, date_changed, date_synced, can_manage_paths, settings from s_tables.t_groups where not is_deleted and id in (select * from allowed_groups); @@ -189,7 +189,7 @@ grant select on s_users.v_groups_self to r_standard, r_standard_system; /*** provide group managers access to manage users assigned to their groups (any user id less than 1000 is reserved/special case, prohibit those). ***/ create view s_users.v_group_users_manage with (security_barrier=true) as - with managed_groups as (select id from s_tables.t_groups where not is_deleted and id_manager in (select id from public.v_users_self_locked_not)), + with managed_groups as (select id from s_tables.t_groups where not is_deleted and id_manager in (select id from v_users_self_locked_not)), available_users as (select id from s_tables.t_users where not is_deleted and not is_locked and not is_system and not is_public) select id_user, id_group, is_locked from s_tables.t_group_users where not is_deleted and id_group in (select * from managed_groups) and id_user in (select * from available_users); @@ -198,14 +198,14 @@ grant select on s_users.v_group_users_manage to r_standard, r_standard_system; create view s_users.v_group_users_manage_insert with (security_barrier=true) as select id_user, id_group from s_tables.t_group_users - where not is_deleted and id_group in (select id from s_users.v_groups_manage_self) and id_group in (select id_group from s_tables.t_group_users where not is_deleted and not is_locked and id_user in (select id from public.v_users_self_locked_not)) and id_user in (select id from s_tables.t_users where not is_deleted and not is_locked and not is_system and not is_public) + where not is_deleted and id_group in (select id from s_users.v_groups_manage_self) and id_group in (select id_group from s_tables.t_group_users where not is_deleted and not is_locked and id_user in (select id from v_users_self_locked_not)) and id_user in (select id from s_tables.t_users where not is_deleted and not is_locked and not is_system and not is_public) with check option; grant insert on s_users.v_group_users_manage_insert to r_standard, r_standard_system; create view s_users.v_group_users_manage_update with (security_barrier=true) as select id_user, id_group from s_tables.t_group_users - where not is_deleted and id_group in (select id from s_users.v_groups_manage_self) and id_group in (select id_group from s_tables.t_group_users where not is_deleted and not is_locked and id_user in (select id from public.v_users_self_locked_not)) and id_user in (select id from s_tables.t_users where not is_deleted and not is_locked and not is_system and not is_public) + where not is_deleted and id_group in (select id from s_users.v_groups_manage_self) and id_group in (select id_group from s_tables.t_group_users where not is_deleted and not is_locked and id_user in (select id from v_users_self_locked_not)) and id_user in (select id from s_tables.t_users where not is_deleted and not is_locked and not is_system and not is_public) with check option; grant update on s_users.v_group_users_manage_update to r_standard, r_standard_system; diff --git a/database/sql/standard/standard-log_groups.sql b/database/sql/standard/standard-log_groups.sql index 12811f9..39546e8 100644 --- a/database/sql/standard/standard-log_groups.sql +++ b/database/sql/standard/standard-log_groups.sql @@ -48,7 +48,7 @@ grant usage on s_tables.se_log_groups_id to r_standard, r_standard_system; /** only allow select and insert for users when user id is current user **/ create view s_users.v_log_groups_self with (security_barrier=true) as - with this_user as (select id from public.v_users_self_locked_not) + with this_user as (select id from v_users_self_locked_not) select id, id_user, id_group, log_type, log_type_sub, log_severity, log_facility, log_details, log_date from s_tables.t_log_groups where id_user in (select * from this_user); @@ -56,7 +56,7 @@ grant select on s_users.v_log_groups_self to r_standard, r_standard_system; create view s_users.v_log_groups_self_insert with (security_barrier=true) as select id_group, log_type, log_type_sub, log_severity, log_facility, log_details from s_tables.t_log_groups - where id_user in (select id from public.v_users_self_locked_not) and id_group in (select id from s_users.v_groups_self where not is_locked) + where id_user in (select id from v_users_self_locked_not) and id_group in (select id from s_users.v_groups_self where not is_locked) with check option; grant insert on s_users.v_log_groups_self_insert to r_standard, r_standard_system; @@ -107,7 +107,7 @@ grant usage on s_tables.se_log_group_users_id to r_standard, r_standard_system; /** only allow select and insert for users when user id is current user **/ create view s_users.v_log_group_users_self with (security_barrier=true) as - with this_user as (select id from public.v_users_self_locked_not), + with this_user as (select id from v_users_self_locked_not), allowed_groups as (select id from s_users.v_groups_self where not is_locked) select id, id_user, id_group, log_type, log_type_sub, log_severity, log_facility, log_date from s_tables.t_log_group_users where id_user in (select * from this_user) or id_group in (select * from allowed_groups); @@ -116,7 +116,7 @@ grant select on s_users.v_log_group_users_self to r_standard, r_standard_system; create view s_users.v_log_group_users_self_insert with (security_barrier=true) as select id_group, log_type, log_type_sub, log_severity, log_facility from s_tables.t_log_group_users - where id_user in (select id from public.v_users_self_locked_not) and id_group in (select id from s_users.v_groups_self where not is_locked) + where id_user in (select id from v_users_self_locked_not) and id_group in (select id from s_users.v_groups_self where not is_locked) with check option; grant insert on s_users.v_log_group_users_self_insert to r_standard, r_standard_system; diff --git a/database/sql/standard/standard-log_problems.sql b/database/sql/standard/standard-log_problems.sql index ca728c8..b9ae5fa 100644 --- a/database/sql/standard/standard-log_problems.sql +++ b/database/sql/standard/standard-log_problems.sql @@ -64,7 +64,7 @@ grant select on s_tables.t_log_problems_users to r_standard_auditor; /** only allow select, insert, and delete for users when user id is current user **/ create view s_users.v_log_problems_users_self with (security_barrier=true) as - with this_user as (select id from public.v_users_self_locked_not) + with this_user as (select id from v_users_self_locked_not) select id_problem, date_created, date_changed, log_details from s_tables.t_log_problems_users where id_user in (select * from this_user); @@ -73,7 +73,7 @@ grant select on s_users.v_log_problems_users_self to r_standard, r_standard_syst create view s_users.v_log_problems_users_self_insert with (security_barrier=true) as select id_problem, date_changed, log_details from s_tables.t_log_problems_users - where id_user in (select id from public.v_users_self_locked_not) + where id_user in (select id from v_users_self_locked_not) with check option; grant insert on s_users.v_log_problems_users_self_insert to r_standard, r_standard_system; @@ -81,7 +81,7 @@ grant insert on s_users.v_log_problems_users_self_insert to r_standard, r_standa create view s_users.v_log_problems_users_self_delete with (security_barrier=true) as select id_problem from s_tables.t_log_problems_users - where id_user in (select id from public.v_users_self_locked_not) + where id_user in (select id from v_users_self_locked_not) with check option; grant delete on s_users.v_log_problems_users_self_delete to r_standard, r_standard_system; diff --git a/database/sql/standard/standard-log_users.sql b/database/sql/standard/standard-log_users.sql index daff46c..533c0c0 100644 --- a/database/sql/standard/standard-log_users.sql +++ b/database/sql/standard/standard-log_users.sql @@ -87,7 +87,7 @@ create index i_log_users_response_code_notable on s_tables.t_log_users (id) /** only allow select and insert for users when user id is current user **/ create view s_users.v_log_users_self with (security_barrier=true) as - with this_user as (select id from public.v_users_self_locked_not) + with this_user as (select id from v_users_self_locked_not) select id, id_user, log_title, log_type, log_type_sub, log_severity, log_facility, log_details, log_date, request_client, response_code from s_tables.t_log_users where id_user in (select * from this_user); @@ -95,7 +95,7 @@ grant select on s_users.v_log_users_self to r_standard, r_standard_system; create view s_users.v_log_users_self_insert with (security_barrier=true) as select log_title, log_type, log_type_sub, log_severity, log_facility, log_details, request_client, response_code from s_tables.t_log_users - where id_user in (select id from public.v_users_self_locked_not) + where id_user in (select id from v_users_self_locked_not) with check option; grant insert on s_users.v_log_users_self_insert to r_standard, r_standard_system; @@ -178,7 +178,7 @@ create index i_log_user_activity_response_code_notable on s_tables.t_log_user_ac /** only allow select and insert for users when user id is current user **/ create view s_users.v_log_user_activity_self with (security_barrier=true) as - with this_user as (select id from public.v_users_self_locked_not) + with this_user as (select id from v_users_self_locked_not) select id, id_user, request_path, request_arguments, request_date, request_client, request_headers, response_headers, response_code from s_tables.t_log_user_activity where id_user in (select * from this_user); @@ -186,7 +186,7 @@ grant select on s_users.v_log_user_activity_self to r_standard, r_standard_syste create view s_users.v_log_user_activity_self_insert with (security_barrier=true) as select request_path, request_arguments, request_client, request_headers, response_headers, response_code from s_tables.t_log_user_activity - where id_user in (select id from public.v_users_self_locked_not) + where id_user in (select id from v_users_self_locked_not) with check option; grant insert on s_users.v_log_user_activity_self_insert to r_standard, r_standard_system; @@ -195,7 +195,7 @@ grant insert on s_users.v_log_user_activity_self_insert to r_standard, r_standar /** public users should be able to insert, but should never be able to view the logs that they insert. **/ create view public.v_log_user_activity_self_insert with (security_barrier=true) as select request_path, request_arguments, request_client, request_headers, response_headers, response_code from s_tables.t_log_user_activity - where id_user in (select id from public.v_users_self_locked_not) + where id_user in (select id from v_users_self_locked_not) with check option; grant insert on public.v_log_user_activity_self_insert to r_standard_public; -- 1.8.3.1