/*
Sistemas de Base de Dados
Bruno LeitãoNº2181846
*/

/*
Ficha 1

1)
    Utilizador - Esquema + definições de segurança;
    Privilegio de Sistema - opções
    Privilegio de Objeto - Ações sobre objetos
    Role - Grupo de privilegios de sistema ou objetos de modo a serem atribuidos a utilizadores

2)
    a) CREATE USER <nome> IDENTIFIED BY <password>
    b) GRANT CREATE SESSION To <nomeZ>
    c) CREATE ROLE <nome.role>
        GRANT SELECT ON <tabela> to <none.role>
    d) GRANT <nome.role> to <nome.user>
    e) REVOKE <nome.privilegio> from <nome.user>
*/

-----------------------------------
--           Questão 1 a)       --
-----------------------------------

conn sys/sys AS sysdba

START Z:\Pratica\scripts\Ficha01_users.SQL

DESC dba_users;

SELECT username, account_status
FROM dba_users
WHERE TO_CHAR(created, 'yyyymmdd') = TO_CHAR(SYSDATE, 'yyyymmdd');

-----------------------------------
--           Questão 1 b)       --
-----------------------------------

conn sys/sys AS sysdba

GRANT CREATE SESSION TO rep_ficha1;
GRANT CREATE TABLE TO rep_ficha1;

DESC dba_sys_privs;

SELECT grantee, privilege
FROM dba_sys_privs
WHERE UPPER(grantee) = 'REP_FICHA1';

conn rep_ficha1/rep

SELECT privilege
FROM user_sys_privs;

START Z:\Pratica\scripts\Ficha01_tabelas.SQL;

DESC user_tables

SELECT TABLE_NAME FROM user_tables;


-----------------------------------
--           Questão 1 c)       --
-----------------------------------

conn rep_ficha1/rep

START Z:\Pratica\scripts\Ficha01_dados.SQL;

DESC aluno;

SELECT * FROM aluno;

DESC inscricao;

SELECT * FROM inscricao;

DESC exame;

SELECT * FROM exame;

-----------------------------------
--           Questão 3 a)       --
-----------------------------------
conn rep_ficha1/rep

ALTER TABLE aluno add (username VARCHAR2(30));

DESC ALUNO;

DESC USER_TAB_COLUMNS;

SELECT COLUMN_NAME, DATA_TYPE, DATA_LENGTH
FROM USER_TAB_COLUMNS
WHERE UPPER(TABLE_NAME) = 'ALUNO';

-----------------------------------
--           Questão 3 b)       --
-----------------------------------

conn sys/sys AS sysdba

SELECT a.nome, a.bi, d.username
FROM DBA_USERS d JOIN REP_FICHA1.ALUNO a
ON TO_CHAR(a.bi) = SUBSTR(d.username, -8);

UPDATE rep_ficha1.aluno a
SET a.username = UPPER(SUBSTR(a.nome, 1, INSTR(a.nome, ' ') -1 ) ) || a.bi
WHERE a.bi IN (SELECT a.bi
                FROM DBA_USERS d JOIN REP_FICHA1.ALUNO a
                ON TO_CHAR(a.bi) = SUBSTR(d.username, -8));

COMMIT;

conn rep_ficha1/rep

SELECT* FROM aluno;

-----------------------------------
--           Questão 3 c)       --
-----------------------------------

conn sys/sys AS sysdba

CREATE USER CARLOS10700007 IDENTIFIED BY CARLOS10700007;

UPDATE rep_ficha1.aluno a
SET a.username = UPPER(SUBSTR(a.nome, 1, INSTR(a.nome, ' ') -1 ) ) || a.bi
WHERE a.bi IN (SELECT a.bi
                FROM DBA_USERS d JOIN REP_FICHA1.ALUNO a
                ON TO_CHAR(a.bi) = SUBSTR(d.username, -8));

conn rep_ficha1/rep

SELECT* FROM aluno;

-----------------------------------
--           Questão 4      --
-----------------------------------
conn sys/sys AS sysdba

--privilegio de sistema
SELECT privilege
FROM dba_sys_privs
WHERE grantee IN ('CARLOS10700007', 'FILIPE10900009', 'SUSANA10800008');

--privilegio de objeto
SELECT table_name, privilege
FROM dba_tab_privs
WHERE grantee IN ('CARLOS10700007', 'FILIPE10900009', 'SUSANA10800008');

--pesquisa de roles
SELECT granted_role
FROM dba_role_privs
WHERE grantee IN ('CARLOS10700007', 'FILIPE10900009', 'SUSANA10800008');

-----------------------------------
--           Questão 5      --
-----------------------------------
conn sys/sys AS sysdba

SELECT role
FROM dba_roles
WHERE role IN ('ROLE_ALUNO', 'CONNECT');

CREATE role ROLE_ALUNO;

grant role_aluno TO CARLOS10700007;
grant role_aluno TO FILIPE10900009;
grant role_aluno TO SUSANA10800008;

grant CONNECT TO role_aluno;

DESC dba_role_privs;

SELECT GRANTED_ROLE, GRANTEE
FROM dba_role_privs
WHERE GRANTED_ROLE IN ('ROLE_ALUNO', 'CONNECT');

grant CREATE VIEW TO role_aluno;

SELECT role, privilege
FROM role_sys_privs
WHERE role IN ('ROLE_ALUNO', 'CONNECT');

-----------------------------------
--           Questão 6      --
-----------------------------------
conn sys/sys AS sysdba

CREATE USER jalmeida IDENTIFIED BY jalmeida;

SELECT username, account_status
FROM dba_users
WHERE TO_CHAR(created, 'yyyymmdd') = TO_CHAR(SYSDATE, 'yyyymmdd');

SELECT role
FROM dba_roles
WHERE role IN ('ROLE_FUNC', 'CONNECT');

CREATE ROLE role_func;

grant CONNECT TO ROLE_FUNC;

SELECT granted_role, grantee
FROM dba_role_privs
WHERE grantee = 'ROLE_FUNC';

GRANT role_func TO jalmeida;

conn rep_ficha1/rep

grant INSERT, SELECT ON aluno TO ROLE_FUNC;
grant UPDATE(nome, morada) ON aluno TO ROLE_FUNC;

SELECT TABLE_NAME, PRIVILEGE
FROM USER_TAB_PRIVS_MADE
WHERE GRANTEE = 'ROLE_FUNC';

conn jalmeida/jalmeida

DESC rep_ficha1.aluno;

--Testar
--Jalmeida deve fazer:
--consultar tabelas de aluno
-- inserir aluno
INSERT INTO rep_ficha1.aluno (BI, NOME, MORADA) VALUES (10900010, 'Bruno Leitão', 'Rua Prior Doutor Cura Rachão');
-- atualizar o nome e morada de um aluno --> esperamos que corra bem
UPDATE rep_ficha1.aluno a
SET a.nome = 'Bruno Miguel Ferreira Leitão', a.morada = 'Rua das flores'
WHERE a.BI = 10900010;
-- atualizar o bi de um aluno --> esperamos que dê erro
UPDATE rep_ficha1.aluno a
SET a.BI = 10900011
WHERE a.BI = 10900010;

SELECT * FROM rep_ficha1.aluno;

-----------------------------------
--           Questão 7      --
-----------------------------------

conn sys/sys AS sysdba

GRANT CREATE VIEW TO rep_ficha1;

-- rep_ficha1

conn rep_ficha1/rep

-- criar vista v_exames

CREATE VIEW v_exames AS
SELECT LOCAL, DATA, CATEGORIA
FROM exame
WHERE DATA > SYSDATE AND DATA  < SYSDATE + 30;

-- dar acesso á vista ao role_aluno

GRANT SELECT ON rep_ficha1.v_exames TO role_aluno;
-- verificar vista foi criada (no dicionario de dados)


-- testar -- entrar como aluno

conn FILIPE10900009/FILIPE10900009

-- pesquisar a vista --> conseguir aceder a vista

SELECT * FROM rep_ficha1.v_exames;

-----------------------------------
--           Questão 8      --
-----------------------------------

-- rep_ficha1
-- criar vista v_dados_aluno
-- dar acesso à vista ao role_aluno
-- virificar vista foi criada (no dicionario de dados) (user_tab_privs_made)
-- testar
-- entrar como aluno
-- pesquisar a vista --> conseguir aceder a vista