--close database; drop database dblbac_v03 ; create database dblbac_v03 in dados2 with buffered log ; ------------------------------------------------------------------------------ ------------------------------------------------------------------------------ -------- PERMISSÕES -- PERMISSÕES -- PERMISSÕES -- PERMISSÕES ------------ ------------------------------------------------------------------------------ ------------------------------------------------------------------------------ --------------------------------------- create role r_gravacao ; revoke r_gravacao from public ; create role r_leitura ; revoke r_leitura from public ; grant connect to cmartins,didi,bussunda,dercy,tiririca,gugu,pele,bozo ; grant r_leitura to cmartins,didi,bussunda,dercy,tiririca,gugu,pele,bozo ; grant r_gravacao to bussunda,gugu,cmartins,bozo ; ------------------------------------------------------------------------------ ------------------------------------------------------------------------------ ----------- SEGURANÇA -- SEGURANÇA -- SEGURANÇA -- SEGURANÇA ------------- ------------------------------------------------------------------------------ ------------------------------------------------------------------------------ --grant dbsecadm to ix_dbsa --; --create security label component lbac_cpm_nivel array [ 'Secreto','Confidencial','Restrito','Publico' ] --; --create security label component lbac_cpm_orgao set { 'Gab_Gov', 'Sec_Saude', -- 'Sec_Educacao', 'Prod_RH_SecPlan', 'Sec_Planejamento' , -- 'Usuario_RH_SecPlan', 'Prod_RH_SecSaude' } --; create security label component lbac_cpm_cargo tree ( 'Governador' root , 'Admin' under 'Governador', 'Secretario Educacao' under 'Governador', 'RH Educacao' under 'Secretario Educacao', 'Professor' under 'RH Educacao', 'Secretario Saude' under 'Governador', 'RH Saude' under 'Secretario Saude', 'Medico' under 'RH Saude') ; create security policy lbac_pol components -- v01 -- lbac_cpm_orgao lbac_cpm_cargo -- v02 -- lbac_cpm_nivel override not authorized write security label ; create security policy lbac_pol_sige components -- v01 -- lbac_cpm_orgao lbac_cpm_cargo -- v02 -- lbac_cpm_nivel ; create security label lbac_pol.governador component -- v01 -- lbac_cpm_orgao 'Gab_Gov' lbac_cpm_cargo 'Governador' -- v02 -- component lbac_cpm_nivel 'Secreto' ; create security label lbac_pol.admin component -- v01 -- lbac_cpm_orgao 'Sec_Planejamento','Prod_RH_SecPlan','Usuario_RH_SecPlan','Sec_Saude' lbac_cpm_cargo 'Admin' -- v02 -- component lbac_cpm_nivel 'Publico' ; create security label lbac_pol.secretario_saude component -- v01 -- lbac_cpm_orgao 'Sec_Saude' lbac_cpm_cargo 'Secretario Saude' -- v02 -- component lbac_cpm_nivel 'Confidencial' ; create security label lbac_pol.rh_plan component -- v01 -- lbac_cpm_orgao 'Prod_RH_SecPlan' lbac_cpm_cargo 'RH Saude' -- v02 -- component lbac_cpm_nivel 'Restrito' ; create security label lbac_pol.rh_saude component -- v01 -- lbac_cpm_orgao 'Prod_RH_SecSaude' lbac_cpm_cargo 'RH Saude' -- v02 -- component lbac_cpm_nivel 'Restrito' ; create security label lbac_pol.secretario_educacao component -- v01 -- lbac_cpm_orgao 'Sec_Educacao', lbac_cpm_cargo 'Secretario Educacao' -- v02 -- component lbac_cpm_nivel 'Confidencial' ; create security label lbac_pol.rh_educacao component -- v01 -- lbac_cpm_orgao 'Sec_Educacao' lbac_cpm_cargo 'RH Educacao' -- v02 -- component lbac_cpm_nivel 'Restrito' ; create security label lbac_pol.professor component -- v01 -- lbac_cpm_orgao 'Sec_Educacao' lbac_cpm_cargo 'Professor' -- v02 -- component lbac_cpm_nivel 'Publico' ; create security label lbac_pol_sige.professor component -- v01 -- lbac_cpm_orgao 'Sec_Educacao' lbac_cpm_cargo 'Professor' -- v02 -- component lbac_cpm_nivel 'Publico' ; create security label lbac_pol.medico component -- v01 -- lbac_cpm_orgao 'Sec_Saude' lbac_cpm_cargo 'Medico' -- v02 -- component lbac_cpm_nivel 'Publico' ; grant security label lbac_pol.governador to bozo for all access ; grant security label lbac_pol.secretario_saude to didi for all access ; grant security label lbac_pol.rh_saude to bussunda for all access ; grant security label lbac_pol.medico to derci for all access ; grant security label lbac_pol.secretario_educacao to pele for all access ; grant security label lbac_pol.rh_educacao to gugu for all access ; grant security label lbac_pol.professor to tiririca for all access ; --grant security label lbac_pol.governador to cmartins for read access grant security label lbac_pol.secretario_saude to cmartins for read access ; grant security label lbac_pol.rh_saude to cmartins for write access ; ------------------------------------------------------------------------------ ------------------------------------------------------------------------------ ----------- ESTRUTURA -- ESTRUTURA -- ESTRUTURA -- ESTRUTURA ------------- ------------------------------------------------------------------------------ ------------------------------------------------------------------------------ --------------------------------------- create distinct type dt_codigo as int{bigint} ; grant usage on type dt_codigo to r_leitura,r_gravacao ; drop cast ( dt_codigo as int{bigint}) ; drop cast ( int{bigint} as dt_codigo) ; create implicit cast ( dt_codigo as int{bigint} ) ; create implicit cast ( int{bigint} as dt_codigo ) ; --------------------------------------- create sequence sq_codigo increment by 3 start 1 maxvalue 1000 nocycle ; revoke all on sq_codigo from public ; grant select on sq_codigo to r_leitura ; grant select,alter on sq_codigo to r_gravacao ; --------------------------------------- create table t_user ( user_id dt_codigo ,username char(10) not null check ( char_length(username) >= 4) ,senha char(100) not null check (char_length(senha) > 0) -- utilizar função de encriptação aqui ) lock mode row ; revoke all on t_user from public ; grant select,update,insert,delete on t_user to r_gravacao ; grant select on t_user to r_leitura ; create unique index ixu1_user on t_user ( user_id ) fillfactor 100 ; alter table t_user add constraint primary key ( user_id ) constraint ixp1_user ; --------------------------------------- create table t_user_compl( user_id dt_codigo not null ,nome_completo char(255) not null ,endereco char(255) not null ,orgao set( dt_codigo not null ) not null ,cargo set( dt_codigo not null ) not null ) lock mode row ; revoke all on t_user_compl from public ; grant select,update,insert,delete on t_user_compl to r_gravacao ; grant select on t_user_compl to r_leitura ; create unique index ixu1_user_compl on t_user_compl (user_id) fillfactor 100 ; alter table t_user_compl add constraint primary key ( user_id ) constraint ixp1_user_compl ; alter table t_user_compl add constraint foreign key ( user_id ) references t_user constraint ixf1_user_compl ; --------------------------------------- create table t_cargo ( cargo_id dt_codigo not null ,nome char(100) not null ) lock mode row ; revoke all on t_cargo from public ; grant select,update,insert,delete on t_cargo to r_gravacao ; grant select on t_cargo to r_leitura ; create unique index ixu1_cargo on t_cargo (cargo_id) fillfactor 100 ; alter table t_cargo add constraint primary key ( cargo_id ) constraint ixp1_cargo ; --------------------------------------- create table t_orgao ( orgao_id dt_codigo not null ,nome char(100) not null ) lock mode row ; revoke all on t_orgao from public ; grant select,update,insert,delete on t_orgao to r_gravacao ; grant select on t_orgao to r_leitura ; create unique index ixu1_orgao on t_orgao ( orgao_id ) fillfactor 100 ; alter table t_orgao add constraint primary key ( orgao_id ) constraint ixp1_orgao ; --------------------------------------- create table t_salario ( user_id dt_codigo not null ,salario decimal(20,2) not null check ( salario >= 0 ) ) lock mode row ; revoke all on t_salario from public ; grant select,update,insert,delete on t_salario to r_gravacao ; grant select on t_salario to r_leitura ; create unique index ixu1_salario on t_salario ( user_id ) fillfactor 100 ; alter table t_salario add constraint primary key ( user_id ) constraint ixp1_salario ; alter table t_salario add constraint foreign key ( user_id ) references t_user constraint ixf1_salario ; --------------------------------------- create table t_sal_mov ( user_id dt_codigo not null ,sequencia dt_codigo not null ,valor_mov decimal(20,2) not null check (valor_mov >= 0 ) ,sinal smallint not null check ( sinal in (1,-1)) ,motivo char(100) not null check (char_length(motivo) > 10) ,data_mov datetime year to second default current year to second not null ) lock mode row ; revoke all on t_sal_mov from public ; grant select,update,insert,delete on t_sal_mov to r_gravacao ; grant select on t_sal_mov to r_leitura ; create unique index ixu1_sal_mov on t_sal_mov( user_id,sequencia) fillfactor 100 ; alter table t_sal_mov add constraint primary key ( user_id,sequencia ) constraint ixp1_sal_mov ; alter table t_sal_mov add constraint foreign key ( user_id ) references t_user constraint ixf1_sal_mov ; ------------------------------------------------------------------------------ ------------------------------------------------------------------------------ --------------- CARGA -- CARGA -- CARGA -- CARGA --------------------- ------------------------------------------------------------------------------ ------------------------------------------------------------------------------ --------------------------------------- load from "user.unl" insert into t_user ; load from "user_compl.unl" insert into t_user_compl ; load from "cargo.unl" insert into t_cargo ; load from "orgao.unl" insert into t_orgao ; load from "salario.unl" insert into t_salario ; load from "sal_mov.unl" insert into t_sal_mov ; ------------------------------------------------------------------------------ ------------------------------------------------------------------------------ ----------- SEGURANÇA -- SEGURANÇA -- SEGURANÇA -- SEGURANÇA ------------- ------------------------------------------------------------------------------ ------------------------------------------------------------------------------ alter table t_user_compl add( lbac_lab idssecuritylabel default 'rh_saude' ) ,add security policy lbac_pol ; alter table t_cargo add ( lbac_lab idssecuritylabel default 'rh_saude' ) ,add security policy lbac_pol ; alter table t_orgao add ( lbac_lab idssecuritylabel default 'rh_saude' ) , add security policy lbac_pol ; --alter table t_salario add ( lbac_lab idssecuritylabel default 'rh_saude' ) alter table t_salario modify ( salario decimal(20,2) COLUMN SECURED WITH secretario_saude ) , add security policy lbac_pol ; alter table t_sal_mov add ( lbac_lab idssecuritylabel default 'rh_saude' ) , add security policy lbac_pol ;