Identificación de tablas y columnas en una base de datos Oracle mediante sentencias SQL

Una vez que una tabla Oracle es creada utilizando el comando CREATE TABLE, la estructura de dicha tabla se puede visualizar utilizando las siguientes vistas (views) del sistema de la base de datos Oracle:

DBA_TABLES: Muestra la información de la tabla a nivel de cabecera.

DBA_TAB_COLUMNS: Muestra la información de la tabla a nivel de columna.

DBA_TAB_PRIVS: Muestra los privilegios de acceso a las tablas de los usuarios de la base de datos.

DBA_COL_PRIVS: Muestra los privilegios de acceso a nivel de columna. Es bastante raro tener la necesidad de definir permisos a nivel de columna, de hecho, yo nunca me he encontrado con una bases de datos Oracle que utilice esta funcionalidad.

A nivel de cabecera, es decir en la vista DBA_TABLES, se muestran datos como el esquema al que pertenece la tabla (columna OWNER), el nombre de la tabla (TABLE_NAME), el nombre del tablespace donde se almacenan físicamente los datos contenidos en la tabla (TABLESPACE_NAME), el número de registros que contiene la tabla (NUM_ROWS), el número de bloques que ocupan físicamente los datos (BLOCKS), así como otros datos estadísticos que son utilizados por el optimizador basado en costes para generar los planes de ejecución de las sentencias SQL o PL/SQL.

La vista DBA_TABLES se puede unir con la vista DBA_TAB_COLUMNS mediante los campos OWNER y TABLE_NAME. Esta última vista del sistema Oracle nos dará información acerca los nombres de las columnas (COLUMN_NAME), del tipo de dato SQL almacenado en dicha columna (DATA_TYPE), de las dimensiones o tamaño de la columna (DATA_LENGTH) y nos indicará si la columna permite o no tomar valores nulos o en blanco (NULLABLE). También esta vista del sistema Oracle muestra algunos valores estadísticos que son utilizados por el optimizador.

La vista DBA_TAB_PRIVS se une de igual manera que la DBA_TAB_COLUMNS con la vista DBA_TABLES. Las columnas fundamentales de la vista DBA_TAB_PRIVS dan información sobre cual es el usuario al que se le concede un permiso determinado (GRANTEE), cual es el usuario que concedió el permiso (GRANTOR), cual es el permiso concedido (PRIVILEGE) y si el usuario al que se le concede el permiso puede a su vez conceder dicho permiso a otros usuarios (GRANTABLE).

Los permiso de acceso que se pueden asignar a una tabla son los siguientes: ALL, ALTER, DELETE, INDEX, INSERT, REFERENCES, SELECT y UPDATE. Además, cuando se dan permisos de acceso al usuario genérico PUBLIC, automáticamente se están asignando dichos permisos a todos los usuarios de la base de datos Oracle.

A continuación encontraréis un par de ejemplos de sentencias SQL con los que podréis:

1) Encontrar el nombre de una tabla de la que sólo recordáis parte del nombre (la sentencia SQL del ejemplo devolverá todas las tablas y el esquema al que pertenecen que contienen en su nombre la secuencia de caracteres EMPL):

SELECT DISTINCT owner, table_name
  FROM dba_tables
 WHERE table_name LIKE '%EMPL%'

2) Encontrar las tablas que tienen una columna con un nombre específico (la sentencia SQL del ejemplo devolverá todas las tablas que tienen una columna cuyo nombre es CLIENTE):

SELECT DISTINCT owner, table_name, column_name
  FROM dba_tab_columns
 WHERE column_name = 'CLIENTE'

3) Listar los permisos que un determinado usuario tiene sobre un esquema de la base de datos determinado (la sentencia SQL del ejemplo mostraría los permisos del usuario JLOPEZ sobre el esquema FACTURAS):

SELECT owner, table_name, grantee,
       privilege, grantable
  FROM dba_tab_privs
 WHERE owner = 'FACTURAS'
   AND grantee = 'JLOPEZ'

Nota: Es importante utilizar mayúsculas a la hora de tratar de identificar un esquema, una tabla, una columna o un usuario, ya que sus nombres son almacenados con este formato.

Aclaración: Es posible que un determinado usuario tenga acceso a una tabla y no aparezca ningún registro para dicho usuario en la tabla DBA_TAB_PRIVS. Estas son algunas de las posibles razones por las que esto puede ocurrir:

1) La tabla en cuestión puede estar habilitada para ser accedida públicamente, es decir, tiene permisos de acceso el usuario genérico PUBLIC.

2) Un determinado rol (ROLE) tiene permiso de acceso sobre la tabla y el usuario en cuestión pertenece a dicho rol (ver tablas DBA_ROLES y DBA_ROLE_PRIVS).

3) El usuario tiene acceso a nivel de sistema para acceder a cualquier tabla, es decir, se le ha asignado el privilegio SELECT ANY TABLE (ver tabla DBA_SYS_PRIVS). También existen privilegios equivalentes para las acciones de CREATE, ALTER, BACKUP, DELETE, DROP, INSERT, LOCK y UPDATE.