Este script crea un usuario y este puede crear otros usuario para que puede usar las tablas según el rol que se les haya dado.
-- Autor: Mynor Choc
-- Carne: 1890-09-3697
-- DBMS: Oracle 11g
-- Tema: Roles.
--creamos un table space.
CREATE BIGFILE TABLESPACE table_tarea DATAFILE 'table_tarea.dat' SIZE 100M AUTOEXTEND ON;
--creamos un usuario, desbloquemos el usuario y damos permiso de coneccion
CREATE USER usuario_tarea PROFILE DEFAULT IDENTIFIED BY usuario_tarea DEFAULT TABLESPACE table_tarea ACCOUNT UNLOCK;
GRANT CONNECT TO usuario_tarea;
-- permiso para crear tablas, rol, usuario y conneccion
GRANT CREATE TABLE TO usuario_tarea;
GRANT CREATE ROLE TO usuario_tarea;
GRANT CREATE USER TO usuario_tarea;
GRANT UNLIMITED TABLESPACE TO usuario_tarea;
GRANT CREATE TO usuario_tarea with admin option;
-- habilitamos que el usuario que tenga un permiso ilimitado a la tablesspace.
ALTER USER usuario_tarea quota unlimited on table_tarea;
--conectamos a usuario_tarea
CONNECT usuario_tarea/usuario_tarea
-- creamos las tablas, llaves primarias, relaciones y los constraints
CREATE TABLE clientes
(
id INTEGER NOT NULL ,
nombre VARCHAR2 (100) NOT NULL ,
nit VARCHAR2 (50) NOT NULL
) ;
ALTER TABLE clientes ADD CONSTRAINT clientes_PK PRIMARY KEY ( id ) ;
CREATE TABLE compras
(
id INTEGER NOT NULL ,
nombre_producto VARCHAR2(100) NOT NULL ,
inventario_id INTEGER NOT NULL
) ;
ALTER TABLE compras ADD CONSTRAINT compras_PK PRIMARY KEY ( id ) ;
CREATE TABLE inventario
(
id INTEGER NOT NULL ,
nombre_inventario VARCHAR2 (100) NOT NULL
) ;
ALTER TABLE inventario ADD CONSTRAINT inventario_PK PRIMARY KEY ( id ) ;
CREATE TABLE productos
(
id INTEGER NOT NULL ,
nombre VARCHAR2 (100) NOT NULL ,
descripcion VARCHAR2 (100) ,
proveedores_id INTEGER NOT NULL ,
inventario_id INTEGER NOT NULL ,
ventas_id INTEGER NOT NULL
) ;
ALTER TABLE productos ADD CONSTRAINT productos_PK PRIMARY KEY ( id ) ;
CREATE TABLE proveedores
(
id INTEGER NOT NULL ,
nombre VARCHAR2 (100) NOT NULL
) ;
ALTER TABLE proveedores ADD CONSTRAINT proveedores_PK PRIMARY KEY ( id ) ;
CREATE TABLE ventas
(
id INTEGER NOT NULL ,
cantidad VARCHAR2 (100) NOT NULL ,
clientes_id INTEGER NOT NULL
) ;
ALTER TABLE ventas ADD CONSTRAINT ventas_PK PRIMARY KEY ( id ) ;
ALTER TABLE compras ADD CONSTRAINT compras_inventario_FK FOREIGN KEY ( inventario_id ) REFERENCES inventario ( id ) ;
ALTER TABLE productos ADD CONSTRAINT productos_inventario_FK FOREIGN KEY ( inventario_id ) REFERENCES inventario ( id ) ;
ALTER TABLE productos ADD CONSTRAINT productos_proveedores_FK FOREIGN KEY ( proveedores_id ) REFERENCES proveedores ( id ) ;
ALTER TABLE productos ADD CONSTRAINT productos_ventas_FK FOREIGN KEY ( ventas_id ) REFERENCES ventas ( id ) ;
ALTER TABLE ventas ADD CONSTRAINT ventas_clientes_FK FOREIGN KEY ( clientes_id ) REFERENCES clientes ( id ) ;
INSERT INTO USUARIO_TAREA.CLIENTES (ID, NOMBRE, NIT) VALUES ('1', 'Juan gomez', '455555-nk');
INSERT INTO USUARIO_TAREA.VENTAS (ID, CANTIDAD, CLIENTES_ID) VALUES ('1', '4', '1');
INSERT INTO USUARIO_TAREA.PROVEEDORES (ID, NOMBRE) VALUES ('1', 'coka-cola');
INSERT INTO USUARIO_TAREA.INVENTARIO (ID, NOMBRE_INVENTARIO) VALUES ('1', 'Finca el zapote');
INSERT INTO USUARIO_TAREA.PRODUCTOS (ID, NOMBRE, DESCRIPCION, PROVEEDORES_ID, INVENTARIO_ID, VENTAS_ID) VALUES ('1', 'Bebida coka', '12 onza', '1', '1', '1');
INSERT INTO USUARIO_TAREA.COMPRAS (ID, NOMBRE_PRODUCTO, INVENTARIO_ID) VALUES ('1', 'coka-cola', '1');
-- creamos los roles
CREATE ROLE cajero;
CREATE ROLE supervisor;
CREATE ROLE enc_compras;
CREATE ROLE jefe_compras;
-- permiso para el cajero
GRANT SELECT ON productos TO cajero;
GRANT SELECT,INSERT ON ventas TO cajero;
GRANT UPDATE ON inventario TO cajero;
GRANT UPDATE,SELECT,DELETE ON clientes TO cajero;
-- permiso para el supervisor
GRANT SELECT ON productos TO supervisor;
GRANT DELETE,UPDATE,SELECT,INSERT ON ventas TO supervisor;
GRANT UPDATE,SELECT,DELETE ON clientes TO supervisor;
--Permiso para el enc_compras
GRANT SELECT ON proveedores TO enc_compras;
GRANT INSERT,DELETE,UPDATE,SELECT ON productos TO enc_compras;
GRANT SELECT,INSERT ON compras TO enc_compras;
--permiso para el jefe_compras
GRANT enc_compras TO jefe_compras;
GRANT DELETE,UPDATE ON compras TO jefe_compras;
GRANT INSERT,SELECT,DELETE,UPDATE ON proveedores TO jefe_compras;
--creamos los usuarios 4 usuarios.
--usuario_cajero;
--usuario_supervisor;
--usuario_enc_compras;
--usuario_jefe_compras
CREATE USER usuario_cajero PROFILE DEFAULT IDENTIFIED BY usuario_cajero DEFAULT TABLESPACE table_tarea ACCOUNT UNLOCK;
GRANT CONNECT TO usuario_cajero;
CREATE USER usuario_supervisor PROFILE DEFAULT IDENTIFIED BY usuario_supervisor DEFAULT TABLESPACE table_tarea ACCOUNT UNLOCK;
GRANT CONNECT TO usuario_supervisor;
CREATE USER usuario_enc_compras PROFILE DEFAULT IDENTIFIED BY usuario_enc_compras DEFAULT TABLESPACE table_tarea ACCOUNT UNLOCK;
GRANT CONNECT TO usuario_enc_compras;
CREATE USER usuario_jefe_compras PROFILE DEFAULT IDENTIFIED BY usuario_jefe_compras DEFAULT TABLESPACE table_tarea ACCOUNT UNLOCK;
GRANT CONNECT TO usuario_jefe_compras;
-- damos los permisos a cada usuario utilizaremos cada rol
-- ROLE cajero;
-- ROLE supervisor;
-- ROLE enc_compras;
-- ROLE jefe_compras;
GRANT cajero TO usuario_cajero;
GRANT supervisor TO usuario_supervisor;
GRANT enc_compras TO usuario_enc_compras;
GRANT jefe_compras TO usuario_jefe_compras;
-- esto puede ver que roles tiene un que nos conectemos
select * from user_role_privs;
-- nos conectamos a usuario.
-- Podemos hacer las pruebas para cada usuario y vermos que solo tenemos acceso
-- a las tablas por medio del rol.
CONNECT usuario_cajero/usuario_cajero;
select * from user_role_privs;
select * from usuario_tarea.compras; --Esta tabla no existe ya que no tenemos acceso a ella por el rol.
-- en esta caso probamos la tabla de producto, y esto solo tiene acceso sobre select ya que asi esta en el rol
select * from usuario_tarea.productos; -- el rol cajero puede selecionar esta tabla.
DELETE FROM usuario_tarea.productos WHERE id = 1; -- no tenemos privilegios para eliminar.
UPDATE usuario_tarea.productos SET nombre = 'hola' WHERE id = 1; -- no tenemos privilegios para actualizar
INSERT INTO usuario_tarea.PRODUCTOS(ID, NOMBRE , DESCRIPCION , PROVEEDORES_ID , VENTAS_ID , INVENTARIO_ID) VALUES (1,'hola','otros',1,1,1); -- no tiene privilegios para insertar
--tabla ventas
INSERT INTO USUARIO_TAREA.VENTAS (ID, CANTIDAD, CLIENTES_ID) VALUES ('2', '5', '1'); -- esta tabla si puede insertar ya que es el unico que esta permitido en rol del cajero.
exit;
CONNECT usuario_supervisor/usuario_supervisor;
select * from user_role_privs;
exit;
CONNECT usuario_enc_compras/usuario_enc_compras;
select * from user_role_privs;
exit;
CONNECT usuario_jefe_compras/usuario_jefe_compras
select * from user_role_privs;
exit;