Notes

Perguntas de Aferição:
1-a)Utilizador -> Esquema + definições de segurança
a.i) Esquema (schema) – conjunto de objetos de um utilizador (tabelas, restrições, índices,…)
b)Previlégio de sistema -> Privilégios de administração(Escrever, mudar pws, etc)
Permitem ao utilizador realizar operações na BD
c)Previlégio de objeto -> Privilégios de utilizadores(ver tabela, escrever em tabela..)
Permitem ao utilizador aceder e manipular determinados objetos existentes na BD
d)Role -> Conjunto de privilégios
e)Perfil -> Um grupo de restrições para cada utilizador, restrições de limites de acesso a recursos
e na política de segurança a passwords

2-a)Criar um utilizador -> Create user xpto
b)Conceder um privilegio de sistema a um utilizador -> Grant .... to xpto
c)Conceder um privilegio de um objeto a um role ->
d)Associar um role a um utilizador -> Grant role teste to xpto
e)Retirar a um role um privilegio de distema que lhe foi concedido -> Revoke role teste to xpto
f)Atribuir um perfil a um utilizador -> Grant ...


------------------------------------------------------------------------
LOGIN:

aulas / aulas
testes / testes
sys / sys




COMO SYS:
1-Criar users: create user func_Jalmeida identified by 123
Criar users: create user fsilva identified by 123
Criar users: create user scosta identified by 123
Criar users: create user csousa identified by 123

2-Criar roles: create role role_funcionario;
Criar roles: create role role_aluno;
Grant role_funcionario to func_jalmeida;
Grant role_aluno to csousa;
Grant role_aluno to scosta;
Grant role_aluno to fsilva;


3-Associar roles a roles: grant connect to role_aluno;
Associar roles a roles: grant connect to role_funcionario;

4-Verificar operações anteriores:
Desc DBA_USERS;
Desc DBA_ROLES;

Select Role
From DBA_ROLES
WHERE UPPER(role) = 'ROLE_ALUNO';

Select ROLE
From DBA_ROLES
WHERE UPPER(ROLE) = 'ROLE_FUNCIONARIO';

Select username
From DBA_USERS
Where UPPER(username)= 'FUNC_JALMEIDA';

Select username
From DBA_USERS
Where UPPER(username)= 'CSOUSA';

Select username
From DBA_USERS
Where UPPER(username)= 'SCOSTA';

Select username
From DBA_USERS
Where UPPER(username)= 'FSILVA';

//Diz o que users os roles funcionario e aluno receberam
Select grantee, granted_role
From dba_role_privs
WHERE grantee like 'ROLE_%';

//Diz a que users foram dados os roles funcionario e alunos
Select grantee, granted_role
From role_role_privs
WHERE granted_role like 'ROLE_%';

5- Privilégios de cada utilizador:
Select grantee, privilege
From dba_sys_privs
WHERE grantee = 'CONNECT';


6-
CREATE OR REPLACE FUNCTION func_verifica_pw_alunos (
username VARCHAR2,
password VARCHAR2,
old_password VARCHAR2) RETURN BOOLEAN AS
BEGIN
IF LENGTH(password) < 6 THEN RETURN FALSE;
ELSIF(password = 'password' OR password ='123456' OR password = old_password)
THEN RETURN FALSE;
ELSE RETURN TRUE;
END IF;
END my_verification_function;

7-
create profile perfil_aluno LIMIT
failed_login_attempts 3
password_reuse_max 5
password_verify_function func_verifica_pw_alunos
PASSWORD_LOCK_TIME 15/(24*60) // ou 15/24/60 mesma coisa
password_life_time 60
password_reuse_time 30
password_grace_time 10;

8-
ALTER user csousa
PROFILE perfil_aluno;

ALTER user scosta
PROFILE perfil_aluno;

LTER user fsilva
PROFILE perfil_aluno;

9-
conn fsilva/123
ALTER user fsilva
identified by password
replace 123;

conn fsilva/123
ALTER user fsilva
identified by teste
replace password;

10-
//Criar perfil
create profile perfil_funcionario limit
sessions_per_user 2
cpu_per_session unlimited
cpu_per_call 3000
connect_time 45
LOGICAL_READS_PER_CALL 1000
PRIVATE_SGA 15k;

//Depois de criar o perfil, para que os seus limites tenham efeito:
ALTER SYSTEM SET RESOURCE_LIMIT=true SCOPE=both;

11-
//Atribuir perfil
ALTER user func_jalmeida
PROFILE perfil_funcionario;

//Testar:
Abrir 3 sessões no sql plus
entrar numa e nao sair
entrar noutra e nao sair
entrar na 3ª (nao vai dar)


12-
alter table aluno
add username varchar2(20);
alter table inscricao
add(meiopagamento varchar2(10) constraint cv_inscricao_meiopag CHECK (UPPER(meioPagamento) IN ('DINHEIRO', 'CHEQUE', 'CARTAO')),NUMCARTAOCREDITO varchar2(20));

13-
Update Aluno
set Username = 'csousa'
WHERE bi=10700007;

Update Aluno
set Username = 'scosta'
WHERE bi=10800008;

Update Aluno
set Username = 'fsilva'
WHERE bi=10900009;

Update inscricao set meiopagamento = 'dinheiro' where bi== 7089;

UPDATE inscricao
SET meioPagamento = 'Cartão', numCartaoCredito = 21004509101231567921
WHERE biAluno = 10800008;

Update inscricao set numcartaocredito = 123 WHERE bialuno=10700007;

Update inscricao set numcartaocredito = 1234 WHERE bialuno=10800008;

Update inscricao set numcartaocredito = 12345 WHERE bialuno=10900009;

14-
i)GRANT INSERT, SELECT,UPDATE(nome,morada,datanasc)
ON aluno
TO role_funcionario;
ii)GRANT INSERT, SELECT, UPDATE ON inscricao TO role_funcionario;
iii)GRANT INSERT, SELECT, UPDATE ON exame TO role_funcionario;

???????????????????????????


//Verificação que fizemos estes comandos
SELECT * FROM role_tab_privs WHERE UPPER(role) = 'ROLE_FUNCIONARIO';

15-
CONN func_jalmeida/funcionario
SELECT * FROM aulas.aluno;
....

16-
CREATE VIEW ver_dados_aluno
AS
SELECT *
FROM aluno
WHERE UPPER(username) = USER;

17-
GRANT SELECT ON ver_dados_aluno
TO role_aluno;

18-

??????????????????


19-
CREATE VIEW ver_dados_exame
AS
SELECT i.datainsc, i.datapagamento, e.data, e.categoria, e.local, i.resultadoexame
FROM exame e JOIN inscricao i ON e.id = i.idexame;

20-
GRANT SELECT ON ver_dados_exame
TO role_aluno;

21-
REVOKE ALL
ON aluno
FROM role_funcionario, role_aluno;

REVOKE ALL
ON exame
FROM role_funcionario, role_aluno;

REVOKE ALL
ON inscricao
FROM role_funcionario, role_aluno;

REVOKE ALL
ON ver_dados_exame
FROM role_aluno;

REVOKE ALL
ON ver_dados_aluno
FROM role_aluno;

//consultar privilegios
SELECT * FROM role_tab_privs WHERE UPPER(role) = 'ROLE_ALUNO';
SELECT * FROM role_tab_privs WHERE UPPER(role) = 'ROLE_FUNCIONARIO';

//Como nao existem privilegios, utilizando estes comandos, a consola devolve:
"Não existem linhas"

22-
Mostra a mensagem de erro: ""

23-
start C:TEMPficha1Ficha01_exec.sql

24-
DECLARE
v_mensagem VARCHAR2 (200);
v_return VARCHAR2 (200);
BEGIN
v_mensagem := 'Sistemas de Bases de Dados';
DBMS_OUTPUT.PUT_LINE('Mensagem a cifrar: ' || v_mensagem);
v_return := FUNC_CIFRAR(v_mensagem);
DBMS_OUTPUT.PUT_LINE('Mensagem cifrada: ' || v_return);
v_mensagem := FUNC_DECIFRAR(v_return);
DBMS_OUTPUT.PUT_LINE('Mensagem decifrada: ' || v_mensagem);
END;

??????????????????????
















Funções:
-------------------------------------------------------------------
create or replace FUNCTION FUNC_CIFRAR(input IN VARCHAR2)
RETURN VARCHAR2 AS
input_string VARCHAR2(1024) := input;
----------Restrições -----------------------------------
-- A chave tem de ter obriagatoriamente 64 bits (8 bytes)
-- A string a encriptar tem de ser múltiplo de 8 bytes
-- A string a encriptar não pode estar vazia

-- Chave utilizada para encriptar a string
key_string VARCHAR2(8) := 'SBD00708';
encrypted_string VARCHAR2(2048);

BEGIN
-- Torna o tamanho da input_string múltiplo de 8
input_string := LPAD(input, Length(input) + 8 - mod(length(input), 8));

dbms_obfuscation_toolkit.DESEncrypt(input_string => input_string, key_string => key_string,
encrypted_string=> encrypted_string );
RETURN encrypted_string;
EXCEPTION
WHEN OTHERS THEN
return null;
END FUNC_CIFRAR;



---------------------------------------------------------------
create or replace FUNCTION FUNC_DECIFRAR(input IN VARCHAR2)
RETURN VARCHAR2 AS
input_string VARCHAR2(1024) := input;
----------Restrições -----------------------------------
-- A chave tem de ter obriagatoriamente 64 bits (8 bytes)
-- O tamanho da string a desencriptar tem de ser múltiplo de 8 bytes
-- A string a desencriptar não pode estar vazia

-- Chave utilizada para encriptar a string
key_string VARCHAR2(8) := 'SBD00708';
encrypted_string VARCHAR2(2048) := input;
decrypted_string VARCHAR2(2048);
begin

dbms_obfuscation_toolkit.DESDecrypt(input_string => encrypted_string,
key_string => key_string, decrypted_string => decrypted_string );

RETURN LTRIM(decrypted_string);

EXCEPTION
WHEN OTHERS THEN
return null;
END FUNC_DECIFRAR;