Oracle MCP Server

Instructions

Implementation Reference

• src/oracle_mcp_server/server.py:800-809 (handler)

  Handler logic within handle_call_tool that processes the list_tables tool call: extracts optional 'owner' parameter, calls DatabaseInspector.get_tables(), and returns JSON-formatted list of tables as TextContent.

  elif name == "list_tables":
      owner = arguments.get("owner")
      tables = await self.inspector.get_tables(owner)
  
      return [
          TextContent(
              type="text",
              text=json.dumps({"tables": tables}, indent=2, default=str),
          )
      ]

• src/oracle_mcp_server/server.py:667-680 (registration)

  Registration of the 'list_tables' tool in handle_list_tools(), including name, description, and input schema for optional 'owner' filter.

  Tool(
      name="list_tables",
      description="List all tables in the database with metadata",
      inputSchema={
          "type": "object",
          "properties": {
              "owner": {
                  "type": "string",
                  "description": "Filter by schema owner (optional)",
                  "default": None,
              }
          },
      },
  ),

• src/oracle_mcp_server/server.py:133-216 (helper)

  Core implementation in DatabaseInspector.get_tables(): executes secure SQL queries against Oracle's ALL_TABLES/USER_TABLES with privilege checks, applies table whitelists, retrieves metadata (owner, name, row count, comments, etc.), and returns formatted list of dicts.

  async def get_tables(self, owner: Optional[str] = None) -> List[Dict[str, Any]]:
      """Get list of tables with metadata"""
      conn = await self.connection_manager.get_connection()
      try:
          cursor = conn.cursor()
  
          # Security: Only show tables the connected user actually owns or has access to
          # This prevents any access to system schemas or unauthorized tables
  
          if owner:
              # When owner is specified, verify the connected user has access
              query = """
                  SELECT 
                      t.owner,
                      t.table_name,
                      t.num_rows,
                      t.last_analyzed,
                      tc.comments as table_comment,
                      t.tablespace_name
                  FROM all_tables t
                  LEFT JOIN all_tab_comments tc ON t.owner = tc.owner AND t.table_name = tc.table_name
                  WHERE t.owner = :owner
                    AND (t.owner = USER OR EXISTS (
                        SELECT 1 FROM all_tab_privs p 
                        WHERE p.table_name = t.table_name 
                          AND p.table_schema = t.owner 
                          AND p.grantee IN (USER, 'PUBLIC')
                    ))
              """
              params = [owner]
          else:
              # Default: Only show tables owned by the current connected user
              # For testuser, this will only show EMPLOYEES, DEPARTMENTS, etc.
              query = """
                  SELECT 
                      USER as owner,
                      t.table_name,
                      t.num_rows,
                      t.last_analyzed,
                      tc.comments as table_comment,
                      t.tablespace_name
                  FROM user_tables t
                  LEFT JOIN user_tab_comments tc ON t.table_name = tc.table_name
              """
              params = []
  
          # Apply whitelist filter if configured
          if TABLE_WHITE_LIST and TABLE_WHITE_LIST != [""]:
              placeholders = ",".join(
                  [f":table_{i}" for i in range(len(TABLE_WHITE_LIST))]
              )
              if owner:
                  # For all_tables query with owner specified
                  query += f" AND t.table_name IN ({placeholders})"
              else:
                  # For user_tables query (no owner)
                  query += f" AND t.table_name IN ({placeholders})"
              params.extend(TABLE_WHITE_LIST)
  
          # Order by clause depends on query type
          if owner:
              query += " ORDER BY t.owner, t.table_name"
          else:
              query += " ORDER BY t.table_name"
  
          cursor.execute(query, params)
  
          tables = []
          for row in cursor:
              tables.append(
                  {
                      "owner": row[0],
                      "table_name": row[1],
                      "num_rows": row[2],
                      "last_analyzed": row[3].isoformat() if row[3] else None,
                      "table_comment": row[4],
                      "tablespace_name": row[5],
                  }
              )
  
          return tables
  
      finally:
          conn.close()