<!-- /* Font Definitions */ @font-face {font-family:"Cambria Math"; panose-1:2 4 5 3 5 4 6 3 2 4; mso-font-charset:0; mso-generic-font-family:roman; mso-font-pitch:variable; mso-font-signature:-1610611985 1107304683 0 0 415 0;} @font-face {font-family:Calibri; panose-1:2 15 5 2 2 2 4 3 2 4; mso-font-charset:0; mso-generic-font-family:swiss; mso-font-pitch:variable; mso-font-signature:-520092929 1073786111 9 0 415 0;} /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-unhide:no; mso-style-qformat:yes; mso-style-parent:""; margin-top:0cm; margin-right:0cm; margin-bottom:10.0pt; margin-left:0cm; line-height:115%; mso-pagination:widow-orphan; font-size:11.0pt; font-family:"Calibri","sans-serif"; mso-fareast-font-family:Calibri; mso-bidi-font-family:"Times New Roman"; mso-fareast-language:EN-US;} .MsoChpDefault {mso-style-type:export-only; mso-default-props:yes; font-size:10.0pt; mso-ansi-font-size:10.0pt; mso-bidi-font-size:10.0pt; mso-ascii-font-family:Calibri; mso-fareast-font-family:Calibri; mso-hansi-font-family:Calibri;} @page WordSection1 {size:612.0pt 792.0pt; margin:70.85pt 3.0cm 70.85pt 3.0cm; mso-header-margin:36.0pt; mso-footer-margin:36.0pt; mso-paper-source:0;} div.WordSection1 {page:WordSection1;} /* List Definitions */ @list l0 {mso-list-id:1159540223; mso-list-type:hybrid; mso-list-template-ids:-1252340904 2144099020 68550681 68550683 68550671 68550681 68550683 68550671 68550681 68550683;} @list l0:level1 {mso-level-text:%1-; mso-level-tab-stop:none; mso-level-number-position:left; text-indent:-18.0pt;} ol {margin-bottom:0cm;} ul {margin-bottom:0cm;} -->
TEORIA – ROLES 20/04/2010
DEFINIÇÃO : Concede / retira previlegios grupo
Sintaxe : CREATE ROLE NOME_ROLE NOT IDENTIFIED
IDENTIFIED BY PASSWORD
EXTERNALLY
ONDE:
NOT IDENTIFIED = USUARIOS PRIVILEGIADOS NÃO NECESSITAM SER VERIFICADOS PELO ORACLE P/ HABILITA-LOS
IDENTIFIED = INDICA QUE DEVERÁ SER VERIFICADO PELO ORACLE.
BY PASSWORD = INDICA QUE O USUARIO NECESSITA ESPECIFICAR UMA SENHA P/ HABILITAR A ROLE.
EXTERNALLY = VERIFICA O ACESSO PELO SISTEMA OPERACIONAL.
EX:
1- CREATE ROLE CONTABILIDADE;
2- CREATE ROLE ROLE_GERENTE IDENTIFIED BY S12345;
3- CREATE ROLE ROLE_ANALISTA IDENTIFIED BY MANAGER;
4- GRANT CREATE TABLE, CREATE USER TO ROLE_ANALISTA;
5- GRANT ROLE_ANALISTA TO JOAO;
ALTERANDO OS PARÂMETROS DE UMA ROLE
SINTAXE: ALTER ROLE NOME_ROLE NOT IDENTIFIED
IDNETIFIED BY PASSWORD
EXTERNALLY
EX:
ALTER ROLE CONTABILIDADE IDENTIFIED BY ABCD
ALTER ROLE ROLE_GERENTE IDENTIFIED EXTERNALLY;
ELIMINAR UMA ROLE
DROP ROLE CONTABILIDADE;
DROP ROLE ROLE_GERENTE;
DROP ROLE ROLE_ANALISTA;
INFOMAÇÕES DAS ROLES NO DIC. DADOS
DBA_ROLES = LISTA AS ROLES DO BD.
EX: SELECT * FROM DBA_ROLES;
DBA_ROLE_PRIVS = LISTA PRIVILÉGIOS CONCEDIDOS A USUÁRIOS , ATRAVÉS DAS ROLES;
ROLE_TAB_PRIVS = LISTA PRIVILÉGIOS DE TABELAS CONCEDIDOS A ROLES ( DENTRO USUARIO)
HABILITANDO / DESABILITANDO ROLES
DEF: PODEMOS HABILITAR /DESBILITAR ROLES P/ DISPONIBILIZAR OU RESTRINGIR CERTOS PRIVILÉGIOS P/ OS USUÁRIOS
SINTAXE:
SET ROLE NOME_ROLE IDENTIFIED BY PASSWORD
ALL EXCEPT NOME_ROLE
NONE
EX:
SET ROLE CONTABILIDADE;
SET ROLE ROLE_GERENTE IDENTIFIED BY ABCD;
SET ROLE NONE;
SET ROLE ALL EXCEPT ROLE_FINANCEIRO;
ESTABELECENDO ROLES DEFAULTS
SINTAXE:
ALTER USER NOME_USUARIO
DEFAULT ROLE NOME_ROLE
IDENTIFIED BY PASSWORD
ALL EXCEPT NOME_ROLE
NONE
EX1:
ALTER USER USUTESTE
DEFAULT ROLE ROLE_GERENTE IDENTIFIED BY ABCD
EX2:
ALTER USER USUTESTE
DEFAULT ROLE ALL EXCEPT ROLE_GERENTE;
EX3:
ALTER USER USUTESTE
DEFAULT ROLE NONE;
EXERCICIOS
EX30:
DROP USER USUTESTE CASCADE;
CREATE USER USUTESTE
IDENTIFIED BY S190385
DEFAULT TABLESPACE TABLESPC01
TEMPORARY TABLESPACE TABLESPC02
QUOTA 30M ON TABLESPC01
PASSWORD EXPIRE
ACCOUNT UNLOCK;
GRANT CONNECT TO USUTESTE;
DROP USER USUTESTE01 CASCADE;
CREATE USER USUTESTE01
IDENTIFIED BY S220388
DEFAULT TABLESPACE TABLESPC01
TEMPORARY TABLESPACE TABLESPC02
QUOTA 30M ON TABLESPC01
PASSWORD EXPIRE
ACCOUNT UNLOCK;
GRANT CONNECT TO USUTESTE01;
EX31:
A= CREATE ROLE ROLE_COTACAO
IDENTIFIED BY R110504;
B= SET ROLE ROLE_COTACAO
IDENTIFIED BY R110504;
EX32:
SELECT * FROM USER_ROLE_PRIVS;
EX33:
GRANT CREATE TABLE TO ROLE_COTACAO;
EX34:
GRANT ROLE_COTACAO TO USUTESTE , USUTESTE01;
EX35:
EM USUTESTE
CREATE TABLE COTACAO ( VALOR NUMBER(11,2),CODIGO NUMBER(10), PRIMARY KEY (CODIGO),DATA DATE) TABLESPACE TABLESPC01;
Create table vendedor (nome varchar (10), codigo number(10), primary key (codigo), endereco varchar (30), estado varchar(02), vendas number(11,2), comissoes number(11,2)) Tablespace TABLESPC01;
