Criacao de Users no ambiente ORACLE ( PRDORA / PRDCORP / PRDCRIT );
Criacao de Users no ambiente ORACLE ( DSVORA / HMGORA / TSTORA/ TREORA );

--SEMPRE VERIFICAR QUAL O DATAFILE PARA CRIAR A TABLESPACE

select * from v$datafile


-- CRIAR A TABLESPACE PARA ARMAZENAR OS OBJETOS/DADOS DO OWNER DO PROJETO

CREATE  TABLESPACE DB DATAFILE '/db_01.dbf' SIZE 100M AUTOEXTEND ON NEXT 100 M MAXSIZE 10048M;

EX:
CREATE TABLESPACE DBFIES_POSGRADUACAO DATAFILE '/oradata/tstora/dbfies_posgraduacao_01.dbf' SIZE 100M AUTOEXTEND ON NEXT 100 M MAXSIZE 10048M ;


-- USUARIO OWNER DOS OBJETOS DB


CREATE USER "DB" IDENTIFIED BY "DC01XJ01EG" ACCOUNT UNLOCK DEFAULT TABLESPACE "TABLESPACEPROJETO" TEMPORARY TABLESPACE "TEMP" PROFILE "DEFAULT";

EX:
CREATE USER "DB" IDENTIFIED BY "DC01XJ01EG" ACCOUNT UNLOCK DEFAULT TABLESPACE "DBFIES_POSGRADUACAO" TEMPORARY TABLESPACE "TEMP" PROFILE "DEFAULT";


-- USUARIO DA APLICACAO 
CREATE  USER "SYSDB" IDENTIFIED BY "DC01XJ01EG" ACCOUNT UNLOCK DEFAULT TABLESPACE "TABLESPACEPROJETO" TEMPORARY TABLESPACE "TEMP" PROFILE "DEFAULT";

EX:
CREATE  USER "SYSDBFIES_POSGRADUACAO" IDENTIFIED BY "DC01XJ01EG" ACCOUNT UNLOCK DEFAULT TABLESPACE "DBFIES_POSGRADUACAO" TEMPORARY TABLESPACE "TEMP" PROFILE "DEFAULT";




-- GRANTS PARA ACESSO
GRANT CREATE SESSION, CONNECT, RESOURCE TO "";


GRANT CREATE SESSION, CONNECT, RESOURCE TO "SYSDBFIESPOSGRADUACAO";
GRANT CREATE SESSION, CONNECT, RESOURCE TO "DBFIES_POSGRADUACAO";

-- EXECUTAR COMO SYSDBA:

GRANT EXECUTE on DBMS_CRYPTO to SYSDBSISU;
GRANT EXECUTE on DBMS_CRYPTO to ISNCRICAO;
GRANT EXECUTE on DBMS_CRYPTO to DBPROUNI_INSCRICAO;
GRANT EXECUTE on DBMS_CRYPTO to SYSDBPROUNIINSCRICAO;


-- CREATE ROLES RO__READ OU RO__WRITE

CREATE ROLE RO_NOMESISTEMA_READ;
CREATE ROLE RO_NOMESISTEMA_WRITE;


-- Script Gerar Grants:

-- Grant nas tabelas
SELECT 'GRANT SELECT, INSERT, UPDATE, DELETE ON '||OWNER||'.'||TABLE_NAME||' TO RDBSISUS;' FROM DBA_TABLES WHERE OWNER='DBSISU';
SELECT 'GRANT SELECT ON '||OWNER||'.'||TABLE_NAME||' TO FELIPELYRA;' FROM DBA_TABLES WHERE OWNER='DBSIGPET';
-- Grant nas views
SELECT 'GRANT SELECT ON '||OWNER||'.'||VIEW_NAME||' TO RDBSISUG;' FROM dba_views WHERE OWNER='DBSISU';
-- Grant nas sequences
SELECT 'GRANT SELECT ON '||SEQUENCE_OWNER||'.'||SEQUENCE_NAME||' TO RDBSISUG;' FROM dba_sequences WHERE SEQUENCE_OWNER='DBSISU';
SELECT 'GRANT SELECT ON '||SEQUENCE_OWNER||'.'||SEQUENCE_NAME||' TO RDBSISUS;' FROM dba_sequences WHERE SEQUENCE_OWNER='DBSISU';
-- Grant execute procedure\fuction
SELECT 'GRANT EXECUTE ON '||OWNER||'.'||OBJECT_NAME||' TO RDBSISUS;' FROM dba_objects WHERE OWNER='DBSISU' AND OBJECT_TYPE IN ('PROCEDURE','FUNCTION');


-- Grant/Revoke system privileges:
 
grant create any type to "DBPORTALESTUDANTE";
grant unlimited tablespace to "DBPORTALESTUDANTE";
grant unlimited tablespace to ENEM;
grant execute on DBMS_CRYPTO to DBPROUNI_INSCRICAO;
grant create any type to ENEM;

-- Somente ambiente de PRODUCAO

-- Trigger dos sistemas para nao onerar o ambiente de producao.

create or replace 
trigger sys.TG_USUARIOS_DG
after logon
          on database when (user not in ('SYS','SYSTEM','DBSNMP','SYSDBPORTAL','SYSDBSISU','SYSDBDBSIPI','SYSDBPORTAL','SYSDBPROUNIINSCRICAO','SYSDBPROUNIGESTAO','SYSDBSISUTEC','SYSDBSISUTEC_CONSULTA','SYSDBISF','SYSDBISFGESTAOBI','SYSDBDUMP','SYSDBEGAT','SYSDBISFGESTAO','SYSDBSONAR','SYSEXECUTAETL','USR_INEP','SYSDBSISUMONITOR'))
          declare
          v_dbrole varchar2(16);
          v_rptg_user number;
          begin
              select database_role into v_dbrole from v$database;
              select count(*) into v_rptg_user from dba_role_privs
                     where grantee = user and granted_role = 'RO_USUARIO_DG';
            if ( v_dbrole = 'PRIMARY' and v_rptg_user > 0)
         then
               raise_application_error(-20022, 'Usuario nao autorizado a conectar ao Banco de Dados Primario.');
            end if;
         end TG_USUARIOS_DG;


-- Role para bloquer acesso ao ambiente de PRODUCAO;


GRANT RO_USUARIO_DG TO ;

Ex:

GRANT RO_USUARIO_DG TO fernandocardona;


-- string de conexao

TSTORA =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = dsv-oracle.mec.gov.br)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = tstora)
    )
  )