Administración de privilegios, roles, perfiles, tablespaces, secuencias, índices y clusters en SQL

Administración de Privilegios, Roles y Perfiles en SQL

Privilegios

Para consultar los privilegios, se utilizan las siguientes sentencias:

• Privilegios del usuario activo: SQL> SELECT * FROM SESSION_PRIVS;
• Privilegios del sistema asignados al usuario: SQL> SELECT * FROM USER_SYS_PRIVS;

A continuación, se muestra una lista de vistas útiles para la gestión de privilegios:

• DBA_SYS_PRIVS: Privilegios del sistema asignados a usuarios o roles.
• USER_TAB_PRIVS: Concesiones sobre objetos que son propiedad del usuario, concedidos o recibidos por este.
• USER_TAB_PRIVS_MADE: Concesiones sobre objetos que son propiedad del usuario, asignadas.
• USER_TAB_PRIVS_RECD: Concesiones sobre objetos que recibe el usuario.
• USER_TAB_GRANTS: Concesiones en objetos para los que el usuario es el propietario, el que concedió el privilegio o al que se concedió el privilegio.
• USER_TAB_GRANTS_MADE: Todas las concesiones hechas en objetos que son propiedad del usuario.
• USER_TAB_GRANTS_RECD: Concesiones en objetos en las que el usuario es aquel al que se le ha concedido el privilegio.
• USER_TAB_GRANTS, ALL_TAB_...: Igual que las anteriores, pero aparecen todos los usuarios.
• USER_COL_GRANTS: Concesiones en columnas para los que el usuario es el propietario, el que concedió el privilegio o al que se concedió el privilegio.
• USER_COL_GRANTS_MADE: Todas las concesiones hechas a las columnas que son propiedad del usuario.
• USER_COL_GRANTS_RECD: Concesiones en objetos en las que el usuario es aquel al que se le ha concedido el privilegio.
• USER_COL_GRANTS_RECD, ALL_...: Igual que las anteriores, pero aparecen todos los usuarios.
• USER_COL_PRIVS: Concesiones sobre columnas que son propiedad del usuario, concedidos o recibidos por este.
• USER_COL_PRIVS_MADE: Concesiones sobre columnas que son propiedad del usuario, asignadas.
• USER_COL_PRIVS_RECD: Concesiones sobre columnas que recibe el usuario.

Roles

Ejemplo de concesión de privilegios:

• Otorgar privilegio de creación de roles a JAIME: SQL> GRANT CREATE ROLE TO JAIME;

Un rol es un conjunto de privilegios al que se le asigna un nombre.

• Quitar privilegios al rol ACCESO: SQL> REVOKE CREATE TABLE FROM ACCESO;
• Borrar un rol (de nombre ACCESO): SQL> DROP ROLE ACCESO;
• Rol por defecto: ALTER USER Nombreusuario DEFAULT {[ROLE nombre_rol]| [NONE]}; (None para que no tenga rol por defecto).

Perfiles

Ejemplo de creación de un perfil:

• Crear el PERFIL1: Limita a 1 el número de sesiones concurrentes por usuario y a dos minutos el tiempo de conexión por sesión. SQL> CREATE PROFILE PERFIL1 LIMIT SESSIONS_PER_USER 1 CONNECT_TIME 2;

Opciones de configuración de perfiles:

• SESSIONS_PER_USER: Número de sesiones múltiples concurrentes permitidas por nombre de usuario.
• CONNECT_TIME: Limita el tiempo de la conexión permitido por sesión antes de que el usuario sea desconectado.
• IDLE_TIME: Limita el tiempo de inactividad permitido antes de que el usuario sea desconectado.
• CPU_PER_SESSION: Limita el tiempo máximo de CPU por sesión (se expresa en centésimas de segundo).
• CPU_PER_CALL: Limita el tiempo máximo de CPU por llamada de análisis, ejecución o búsqueda (se expresa en centésimas de segundo).
• LOGICAL_READS_PER_SESSION: Limita el número de bloques de datos leídos en una sesión.
• LOGICAL_READS_PER_CALL: Limita el número de bloques de datos leídos por llamada de análisis, ejecución o búsqueda.
• PRIVATE_SGA: Limita el número de bytes enteros de espacio privado de la SGA.
• COMPOSITE_LIMIT: Limita el coste total de recursos para una sesión en unidades de servicio basada en la suma de los siguientes recursos (CPU_PER_SESSION, CONNECT_TIME, LOGICAL_READS_PER_SESSION y PRIVATE_SGA).

Para activar el uso de los perfiles en el sistema, el administrador debe ejecutar la siguiente orden:

• SQL> ALTER SYSTEM SET RESOURCE_LIMIT=TRUE;

Administración de Tablespaces, Secuencias, Índices y Clusters en SQL

Tablespaces

• Crear un tablespace TRAB: SQL> create tablespace trab datafile 'trab1.ora' size 10m,'trab2.ora' size 5m default storage (initial 10k pctincrease 25);
• Crear un tablespace PEQUE: SQL> CREATE TABLESPACE PEQUE DATAFILE 'PEQUE.ORA' SIZE 100K AUTOEXTEND ON NEXT 120 MAXSIZE 1M;
• Agregar un nuevo archivo al tablespace TRAB: SQL> ALTER TABLESPACE TRAB ADD DATAFILE 'TRAB3.ORA' SIZE 6M;
• Borrar un tablespace: DROP TABLESPACE nombretablespace INCLUDING CONTENTS;

Secuencias

• Crear una secuencia CODIGOS: SQL> CREATE SEQUENCE CODIGOS START WITH 1 INCREMENT BY 1 MAXVALUE 99;

Índices

• Crear un índice en la columna EMP_NO para la tabla EMPLE: SQL> CREATE INDEX INDICEEMPLE ON EMPLE (EMP_NO);

Clusters

• Crear un cluster EMPLEYDEPART: SQL> CREATE CLUSTER EMPLEYDEPART (NUMERO_DEPARTAMENTO NUMBER (2));
• Agregar las tablas EMPLE y DEPART al cluster: CREATE TABLE DEPART ( DEPT_NO NUMBER(2) NOT NULL, DNOMBRE VARCHAR2(14), LOC VARCHAR2(14) ) CLUSTER EMPLEYDEPART (DEPT_NO);