Comandos SQL en Oracle para gestionar usuarios, roles, tablespaces y tablas

Gestión de Usuarios, Roles y Permisos en SQL (Oracle)

Administración de Usuarios y Roles

Crear un Usuario

Sintaxis básica para la creación de un usuario y su autenticación mediante contraseña.

CREATE USER NombreUsuario IDENTIFIED BY Contraseña;

Crear un Rol

Definición de un nuevo rol para agrupar permisos.

CREATE ROLE NombreRol;

Asignar Tablespace por Defecto a un Usuario

Crear un usuario y asignarle un tablespace por defecto.

CREATE USER NombreUsuario IDENTIFIED BY Contraseña DEFAULT TABLESPACE NombreTablespace;

Dar Permisos a un Rol

Asignar privilegios de sistema y objeto a un rol específico.

GRANT CREATE SESSION, CREATE USER, SELECT ANY TABLE, CREATE TABLESPACE TO NombreRol;

Asignar un Rol a un Usuario

Otorgar un rol previamente definido a un usuario.

GRANT NombreRol TO NombreUsuario;

Ver Roles Asignados a un Usuario

Consulta para visualizar los roles otorgados a un usuario.

SELECT username, granted_role FROM user_role_privs;

Dar Todos los Permisos a un Usuario

Otorgar todos los privilegios del sistema (generalmente solo para administradores).

GRANT ALL PRIVILEGES TO NombreUsuario;

Revocar Permisos de un Usuario

Retirar privilegios específicos (ej. RESOURCE y CONNECT).

REVOKE RESOURCE, CONNECT FROM NombreUsuario;

Revocar Roles de un Usuario

Retirar uno o varios roles asignados a un usuario.

REVOKE NombreRol, RESOURCE, CONNECT FROM NombreUsuario;

Gestión de Tablespaces y Metadatos

Creación y Consulta de Tablespaces

Crear un Tablespace

Definición de un nuevo tablespace y su archivo de datos.

CREATE TABLESPACE NombreTablespace DATAFILE 'C:/NombreTablespace.dbf' SIZE 10M;

Ver Tablas en un Tablespace Específico

Consulta a la vista DBA_TABLES para listar las tablas contenidas en un tablespace.

SELECT owner, table_name, tablespace_name FROM dba_tables WHERE tablespace_name='NombreTablespace';

Operaciones DDL: Creación y Modificación de Tablas

Creación de Tablas y Datos

Crear Tablas e Insertar Datos

La sintaxis para la creación de tablas (CREATE TABLE) y la inserción de datos (INSERT INTO) sigue el estándar SQL, similar a MySQL.

-- Crear tablas: Sintaxis estándar SQL
-- Insertar datos: Sintaxis estándar SQL

Crear Tabla con Clave Foránea (Foreign Key)

Definición de una restricción de clave foránea durante la creación de la tabla.

CREATE TABLE NombreTabla (
    NombreCampo1 TIPO,
    NombreCampo2 TIPO,
    CONSTRAINT NombreConstraint FOREIGN KEY (NombreCampoFK) 
    REFERENCES NombreTablaReferenciada (NombreCampoReferenciado)
);

Hacer una Copia de una Tabla Existente

Crear una nueva tabla copiando la estructura y los datos de la tabla empresas.

CREATE TABLE Copia_empresas AS SELECT * FROM empresas;

Visualización de Estructuras

Visualizar el Nombre de las Tablas Creadas

Consulta a la vista USER_TABLES.

SELECT table_name FROM user_tables;

Mostrar Estructura de una Tabla

Comando para describir la estructura de una tabla.

DESC NombreTabla;

Mostrar Estructura de la Vista USER_CONSTRAINTS

Comando para describir la estructura de la vista de restricciones de usuario.

DESC user_constraints;

Modificación de Tablas (ALTER TABLE)

Borrar una Columna

ALTER TABLE NombreTabla DROP COLUMN NombreColumna;

Añadir una Columna a una Tabla Existente

ALTER TABLE NombreTabla ADD NombreCampo TIPO;

Añadir Varias Columnas a la Vez

ALTER TABLE NombreTabla ADD (
    Columna1 TIPO,
    Columna2 TIPO,
    Columna3 TIPO
);

Añadir Columna y Establecer Valor por Defecto

ALTER TABLE NombreTabla ADD NombreColumna TIPO DEFAULT 'ValorPorDefecto';

Cambiar Nombre de una Columna

ALTER TABLE NombreTabla RENAME COLUMN NombreColumna TO NuevoNombreColumna;

Cambiar Tipo de Datos de un Campo

Modificar el tipo de datos de una columna existente (ej. a FLOAT para valores decimales).

ALTER TABLE NombreTabla MODIFY NombreCampo FLOAT;

Gestión de Restricciones (Constraints)

Añadir Restricción de Comparación entre Columnas

Añadir dos columnas y una restricción CHECK que compara sus valores. (Nota: Se corrige la sintaxis de la restricción original para reflejar la intención de comparación entre campos).

ALTER TABLE NombreTabla ADD (
    NombreCampo1 TIPO,
    NombreCampo2 TIPO,
    CONSTRAINT NombreDeLaConstraint CHECK (NombreCampo1 <= NombreCampo2)
);

Añadir Restricción de Valores Permitidos (IN)

Modificar una columna para asegurar que solo contenga ciertos valores predefinidos.

ALTER TABLE NombreTabla MODIFY NombreColumna TIPO CHECK (NombreColumna IN ('valor1', 'valor2', 'valor3', 'valor4'));

Ejemplo de Restricción IN para la Tabla EMPLEADOS

Añadir una restricción a la columna categoria de la tabla empleados.

ALTER TABLE empleados ADD CONSTRAINT categoria_valida CHECK (categoria IN ('jefe', 'subjefe', 'ingeniero', 'tecnico', 'comercial', 'administrativo'));

Añadir Columnas de Fecha y Restricción de Validación

Añadir columnas fecha_devolucion y fecha_real_devolucion a la tabla prestamos y asegurar que la fecha de devolución sea menor o igual a la fecha real.

ALTER TABLE prestamos ADD (fecha_devolucion DATE, fecha_real_devolucion DATE);
ALTER TABLE prestamos ADD CONSTRAINT fecha_devolucion_valida CHECK (fecha_devolucion <= fecha_real_devolucion);

Restricción FOREIGN KEY con ON DELETE CASCADE (Socio)

Si se elimina un socio, se eliminan automáticamente todos sus préstamos asociados.

ALTER TABLE prestamos ADD CONSTRAINT fk_borrar_socio FOREIGN KEY (socio) REFERENCES socios ON DELETE CASCADE;

Restricción FOREIGN KEY con ON DELETE CASCADE (Ejemplar)

Si se elimina un ejemplar, se eliminan automáticamente todos sus préstamos asociados. (Nota: Se corrige la sintaxis de la clave compuesta).

ALTER TABLE prestamos ADD CONSTRAINT fk_borrar_ejemplar FOREIGN KEY (cod_libro, num_ejemplar) REFERENCES ejemplares ON DELETE CASCADE;

Restricción CHECK para Valor Mínimo

Asegurar que el precio de compra sea siempre mayor que cero.

ALTER TABLE libros ADD CONSTRAINT precio_compra_positivo CHECK (precio_compra > 0);